{"id":13361,"date":"2020-02-04T20:07:51","date_gmt":"2020-02-04T19:07:51","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/"},"modified":"2020-02-04T20:07:51","modified_gmt":"2020-02-04T19:07:51","slug":"rollback-to-savepoint","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/","title":{"rendered":"ROLLBACK TO SAVEPOINT;"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nI love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example:<\/p>\n

create table DEMO (n int);\nbegin transaction;\ninsert into DEMO values (0);\nselect n \"after insert\" from DEMO;\nupdate DEMO set n=1\/n;\nselect n \"after error\" from DEMO;\ncommit;\nselect n \"after commit\" from DEMO;<\/code><\/pre>\n
The “begin transaction” is not valid syntax in all databases because transactions may be started implicitly, but the other statements are valid syntax in all the common SQL databases. They all raise an error in the update execution because there’s one row with N=0 and then we cannot calculate 1\/N as it is a math error. But, what about the result of the last select?<\/p>\n
If I run this with Oracle<\/a>, DB2<\/a>, MS SQL Server<\/a>, My SQL<\/a> (links go to example in db<>fiddle), the row added by the insert is always visible by my session: after the insert, of course, after the update error, and after the commit (then visible by everybody).
\n
\n\"\"<\/a><\/p>\n
The same statements run with PostgreSQL<\/a> have a different result. You cannot do anything after the error. Only rollback the transaction. Even if you “commit” it will rollback.\u00a0<\/p>\n
\"\"<\/a><\/p>\n
Yes, no rows are remaining there! Same code but different result.<\/p>\n
You can have the same behavior as the other databases by defining a savepoint before the statement, and rollback to savepoint after the error. Here is the db<>fiddle<\/a>. With PostgreSQL you have to define an explicit savepoint<\/strong> if you want to continue in your transaction after the error. Other databases take an implicit savepoint<\/strong>. By the way, I said “statement” but here is Tanel Poder showing that in Oracle the transaction is actually not related to the statement but the user call: Oracle State Objects and Reading System State Dumps Hacking Session Video – Tanel Poder’s blog<\/a><\/p>\n
In Oracle, you can run multiple statements in a user call with a PL\/SQL block. With PostgreSQL, you can group multiple statements in one command but you can also run a PL\/pgSQL block. And with both, you can catch errors in the exception block. And then, it is PostgreSQL that takes now an implicit savepoint<\/strong> as I explained in a previous post:\u00a0PostgreSQL subtransactions, savepoints, and exception blocks<\/a><\/p>\n
This previous post was on Medium ( you can read https:\/\/www.linkedin.com\/pulse\/technology-advocacy-why-i-am-still-nomad-blogger-franck-pachot\/<\/a> where I explain my blog “nomadism”), but as you can see I’m back on the dbi-services blog<\/strong> for my 500th post there.\u00a0<\/p>\n
My last post here was called “COMMIT<\/strong>” https:\/\/www.dbi-services.com\/blog\/commit\/<\/a> where I explained that I was quitting consulting for CERN to start something new. But even if I decided to change, I was really happy at dbi-services (as I mentioned on a LinkedIn post<\/a> about great places to work<\/strong>). And when people like to work together it creates an implicit SAVEPOINT<\/strong> where you can come back if you encounter some unexpected results. Yes\u2026 this far-fetched analogy just to mention that I’m happy to come back to dbi services and this is where I’ll blog again.<\/p>\n
As with many analogies, it reaches the limits of the comparison very quickly. You do not ROLLBACK a COMMIT and it is not a real rollback because this year at CERN was a good experience. I’ve met great people there, learned interesting things about matter and anti-matter, and went out of my comfort zone like co-organizing a PostgreSQL meetup and inviting external people ( https:\/\/www.linkedin.com\/pulse\/working-consultants-only-externalization-franck-pachot\/<\/a>) for visits and conferences.\u00a0<\/p>\n
This “rollback” is actually a step further, but back in the context I like: solve customer problems in a company that cares about its employees and customers. And I’m not exactly coming back at the same “savepoint”. I was mostly focused on Oracle and I’m now covering more technologies in the database ecosystem. Of course, consulting on Oracle Database will still be a major activity. But today, many other databases are raising: NoSQL, NewSQL\u2026 Open Source is more and more relevant. And in this jungle, the replication and federation technologies are raising. I’ll continue to share on these areas and you can follow this blog<\/a>, the RSS feed<\/a>, and\/or my twitter account.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example: create table DEMO (n int); begin transaction; insert into DEMO values (0); select n “after insert” from DEMO; update […]<\/p>\n","protected":false},"author":28,"featured_media":13362,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[96,77],"type_dbi":[],"class_list":["post-13361","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-oracle","tag-postgresql"],"acf":[],"yoast_head":"\nROLLBACK TO SAVEPOINT; - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ROLLBACK TO SAVEPOINT;\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example: create table DEMO (n int); begin transaction; insert into DEMO values (0); select n "after insert" from DEMO; update […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-02-04T19:07:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1000\" \/>\n\t<meta property=\"og:image:height\" content=\"560\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"ROLLBACK TO SAVEPOINT;\",\"datePublished\":\"2020-02-04T19:07:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\"},\"wordCount\":693,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg\",\"keywords\":[\"Oracle\",\"PostgreSQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\",\"name\":\"ROLLBACK TO SAVEPOINT; - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg\",\"datePublished\":\"2020-02-04T19:07:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg\",\"width\":1000,\"height\":560},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ROLLBACK TO SAVEPOINT;\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"ROLLBACK TO SAVEPOINT; - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/","og_locale":"en_US","og_type":"article","og_title":"ROLLBACK TO SAVEPOINT;","og_description":"By Franck Pachot . I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example: create table DEMO (n int); begin transaction; insert into DEMO values (0); select n \"after insert\" from DEMO; update […]","og_url":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/","og_site_name":"dbi Blog","article_published_time":"2020-02-04T19:07:51+00:00","og_image":[{"width":1000,"height":560,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg","type":"image\/jpeg"}],"author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"headline":"ROLLBACK TO SAVEPOINT;","datePublished":"2020-02-04T19:07:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/"},"wordCount":693,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg","keywords":["Oracle","PostgreSQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/","url":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/","name":"ROLLBACK TO SAVEPOINT; - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg","datePublished":"2020-02-04T19:07:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1_ObIqMknR1jztQA2DQIcTGw.jpg","width":1000,"height":560},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/rollback-to-savepoint\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"ROLLBACK TO SAVEPOINT;"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13361","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=13361"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13361\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/13362"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13361"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13361"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13361"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13361"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}