{"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":"

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 “create schema” command which I blogged about a long time ago<\/a>). If you take a look a the SQL:2008 standard (ISO\/IEC 9075-1:2008<\/a>) you can find this: “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.” Well, a schema is what DDL is about, isn’t it? Lets see how PostgreSQL handles this. <\/p>\n

When you create objects like you would do it in Oracle you will probably do it like this:<\/p>\n

\n(postgres@[local]:5000) [postgres] > create table tab1 ( a int, b date );\nCREATE TABLE\nTime: 60.109 ms\n(postgres@[local]:5000) [postgres] > create view view1 as select a from tab1;\nCREATE VIEW\nTime: 64.288 ms\n(postgres@[local]:5000) [postgres] > create sequence seq1;\nCREATE SEQUENCE\nTime: 36.861 ms\n<\/pre>\n
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
\n(postgres@[local]:5000) [postgres] > d tab1\n     Table \"public.tab1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n b      | date    | \n\n(postgres@[local]:5000) [postgres] > d view1\n     View \"public.view1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n\n(postgres@[local]:5000) [postgres] > 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
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
\n(postgres@[local]:5000) [postgres] > begin;\nBEGIN\nTime: 0.075 ms\n(postgres@[local]:5000) [postgres] > create table tab1 ( a int, b date );\nCREATE TABLE\nTime: 0.543 ms\n(postgres@[local]:5000) [postgres] > create view view1 as select a from tab1;\nCREATE VIEW\nTime: 0.520 ms\n(postgres@[local]:5000) [postgres] > create sequence seq1;\nCREATE SEQUENCE\nTime: 0.576 ms\n(postgres@[local]:5000) [postgres] > end;\nCOMMIT\nTime: 0.854 ms\n<\/pre>\n
All the DDLs are executed in one transaction. As soon as then transaction is closed by issuing “end;” a commit is performed. All objects are there:<\/p>\n
\n(postgres@[local]:5000) [postgres] > d tab1\n     Table \"public.tab1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n b      | date    | \n\n(postgres@[local]:5000) [postgres] > d view1\n     View \"public.view1\"\n Column |  Type   | Modifiers \n--------+---------+-----------\n a      | integer | \n\n(postgres@[local]:5000) [postgres] > 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
The pretty cool thing is what happens if one or more of the statements fail:<\/p>\n
\n(postgres@[local]:5000) [postgres] > begin;\nBEGIN\nTime: 0.076 ms\n(postgres@[local]:5000) [postgres] > create table tab1 ( a int, b date );\nCREATE TABLE\nTime: 0.500 ms\n(postgres@[local]:5000) [postgres] > create view view1 as select a from tab99999;\nERROR:  relation \"tab99999\" does not exist\n<\/pre>\n
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
\n(postgres@[local]:5000) [postgres] > create sequence seq1;\nERROR:  current transaction is aborted, commands ignored until end of transaction block\nTime: 0.126 ms\n<\/pre>\n
The transaction was aborted and everything will be ignored until we close the transaction. So lets do it:<\/p>\n
\n(postgres@[local]:5000) [postgres] > end;\nROLLBACK\nTime: 0.108 ms\n<\/pre>\n
And here we go: Instead of a commit a rollback was executed. Does the table tab1 exist?<\/p>\n
\n(postgres@[local]:5000) [postgres] > d tab1\nDid not find any relation named \"tab1\".\n<\/pre>\n
No. So, without worrying about cleaning up anything that was created successfully we can just fix the issue and start again. Isn’t that a nice feature?<\/p>\n","protected":false},"excerpt":{"rendered":"
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 “create schema” command which I blogged about a long time ago). If you take a look a the SQL:2008 […]<\/p>\n","protected":false},"author":29,"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":"\nTransactional 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 “create schema” command which I blogged about a long time ago). If you take a look a the SQL:2008 […]\" \/>\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=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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 & 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\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/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 “create schema” command which I blogged about a long time ago). If you take a look a the SQL:2008 […]","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":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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 & 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\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"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}]}}