{"id":7239,"date":"2016-03-09T14:24:21","date_gmt":"2016-03-09T13:24:21","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/"},"modified":"2016-03-09T14:24:21","modified_gmt":"2016-03-09T13:24:21","slug":"transactional-ddl","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/","title":{"rendered":"Transactional DDL"},"content":{"rendered":"<p>When you are working on Oracle you probably learned that each DDL does an implicit commit in the background. There is no way to have DDLs wrapped in a real transaction (with the exception of the &#8220;create schema&#8221; command which I blogged about <a href=\"http:\/\/danielwestermann.com\/2013\/06\/05\/create-schema-in-oracle\/\" target=\"_blank\" rel=\"noopener\">a long time ago<\/a>). If you take a look a the SQL:2008 standard (<a href=\"http:\/\/www.iso.org\/iso\/home\/store\/catalogue_ics\/catalogue_detail_ics.htm?csnumber=53681\" target=\"_blank\" rel=\"noopener\">ISO\/IEC 9075-1:2008<\/a>) you can find this: &#8220;An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. That is to say: either the execution result is completely successful, or it has no effect on any SQL schemas or SQL-data.&#8221; Well, a schema is what DDL is about, isn&#8217;t it?  Lets see how PostgreSQL handles this. <\/p>\n<p>When you create objects like you would do it in Oracle you will probably do it like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; create table tab1 ( a int, b date );\nCREATE TABLE\nTime: 60.109 ms\n(postgres@[local]:5000) [postgres] &gt; create view view1 as select a from tab1;\nCREATE VIEW\nTime: 64.288 ms\n(postgres@[local]:5000) [postgres] &gt; create sequence seq1;\nCREATE SEQUENCE\nTime: 36.861 ms\n<\/pre>\n<p>Creating object this way behaves pretty much the same as Oracle does. There are autocommits in the background and all objects got created:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; d tab1\n     Table \"public.tab1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n b      | date    | \n\n(postgres@[local]:5000) [postgres] &gt; d view1\n     View \"public.view1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n\n(postgres@[local]:5000) [postgres] &gt; d seq1\n            Sequence \"public.seq1\"\n    Column     |  Type   |        Value        \n---------------+---------+---------------------\n sequence_name | name    | seq1\n last_value    | bigint  | 1\n start_value   | bigint  | 1\n increment_by  | bigint  | 1\n max_value     | bigint  | 9223372036854775807\n min_value     | bigint  | 1\n cache_value   | bigint  | 1\n log_cnt       | bigint  | 0\n is_cycled     | boolean | f\n is_called     | boolean | f\n<\/pre>\n<p>But is this what you really want? What do you do when one ore more of the statements fail? Drop all objects that got created and start from the beginning? Imagine you want to create hundred of objects and you want to either all of them to succeed or roll back everything if one ore more fail. This is where transactions come into the game: A transaction is atomic which means: Either everything inside the transaction succeeds or everything is rolled back. Transactions are not only for DML as you might think. PostgreSQL lets you do this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; begin;\nBEGIN\nTime: 0.075 ms\n(postgres@[local]:5000) [postgres] &gt; create table tab1 ( a int, b date );\nCREATE TABLE\nTime: 0.543 ms\n(postgres@[local]:5000) [postgres] &gt; create view view1 as select a from tab1;\nCREATE VIEW\nTime: 0.520 ms\n(postgres@[local]:5000) [postgres] &gt; create sequence seq1;\nCREATE SEQUENCE\nTime: 0.576 ms\n(postgres@[local]:5000) [postgres] &gt; end;\nCOMMIT\nTime: 0.854 ms\n<\/pre>\n<p>All the DDLs are executed in one transaction. As soon as then transaction is closed by issuing &#8220;end;&#8221; a commit is performed. All objects are there:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; d tab1\n     Table \"public.tab1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n b      | date    | \n\n(postgres@[local]:5000) [postgres] &gt; d view1\n     View \"public.view1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n\n(postgres@[local]:5000) [postgres] &gt; d seq1\n            Sequence \"public.seq1\"\n    Column     |  Type   |        Value        \n---------------+---------+---------------------\n sequence_name | name    | seq1\n last_value    | bigint  | 1\n start_value   | bigint  | 1\n increment_by  | bigint  | 1\n max_value     | bigint  | 9223372036854775807\n min_value     | bigint  | 1\n cache_value   | bigint  | 1\n log_cnt       | bigint  | 0\n is_cycled     | boolean | f\n is_called     | boolean | f\n<\/pre>\n<p>The pretty cool thing is what happens if one or more of the statements fail:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; begin;\nBEGIN\nTime: 0.076 ms\n(postgres@[local]:5000) [postgres] &gt; create table tab1 ( a int, b date );\nCREATE TABLE\nTime: 0.500 ms\n(postgres@[local]:5000) [postgres] &gt; create view view1 as select a from tab99999;\nERROR:  relation \"tab99999\" does not exist\n<\/pre>\n<p>As the table referenced by the view does not exist the create view statements fails. What is the status of my transaction now? Lets try to continue with the create sequence statement as it would happen if all the DDLs are executed inside a script:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; create sequence seq1;\nERROR:  current transaction is aborted, commands ignored until end of transaction block\nTime: 0.126 ms\n<\/pre>\n<p>The transaction was aborted and everything will be ignored until we close the transaction. So lets do it:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; end;\nROLLBACK\nTime: 0.108 ms\n<\/pre>\n<p>And here we go: Instead of a commit a rollback was executed. Does the table tab1 exist?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5000) [postgres] &gt; d tab1\nDid not find any relation named \"tab1\".\n<\/pre>\n<p>No. So, without worrying about cleaning up anything that was created successfully we can just fix the issue and start again. Isn&#8217;t that a nice feature?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you are working on Oracle you probably learned that each DDL does an implicit commit in the background. There is no way to have DDLs wrapped in a real transaction (with the exception of the &#8220;create schema&#8221; command which I blogged about a long time ago). If you take a look a the SQL:2008 [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[713,77],"type_dbi":[],"class_list":["post-7239","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-enterprisedb","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Transactional DDL - 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\/transactional-ddl\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Transactional DDL\" \/>\n<meta property=\"og:description\" content=\"When you are working on Oracle you probably learned that each DDL does an implicit commit in the background. There is no way to have DDLs wrapped in a real transaction (with the exception of the &#8220;create schema&#8221; command which I blogged about a long time ago). If you take a look a the SQL:2008 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-09T13:24:21+00:00\" \/>\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\\\/transactional-ddl\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Transactional DDL\",\"datePublished\":\"2016-03-09T13:24:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/\"},\"wordCount\":407,\"commentCount\":0,\"keywords\":[\"enterprisedb\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/\",\"name\":\"Transactional DDL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-03-09T13:24:21+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/transactional-ddl\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Transactional DDL\"}]},{\"@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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"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":"Transactional DDL - 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\/transactional-ddl\/","og_locale":"en_US","og_type":"article","og_title":"Transactional DDL","og_description":"When you are working on Oracle you probably learned that each DDL does an implicit commit in the background. There is no way to have DDLs wrapped in a real transaction (with the exception of the &#8220;create schema&#8221; command which I blogged about a long time ago). If you take a look a the SQL:2008 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/","og_site_name":"dbi Blog","article_published_time":"2016-03-09T13:24:21+00:00","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\/transactional-ddl\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Transactional DDL","datePublished":"2016-03-09T13:24:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/"},"wordCount":407,"commentCount":0,"keywords":["enterprisedb","PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/","url":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/","name":"Transactional DDL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-03-09T13:24:21+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/transactional-ddl\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Transactional DDL"}]},{"@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\/4bae5e5dd8c7d48edbd948772b49b202","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\/7239","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=7239"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7239\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7239"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}