Transactional DDLs – a message to non-PostgreSQL users

06.2013 / Category: / Tags: |

Sometimes it is astonishing to see which basic functionality commercial vendors lack. One of the things that strikes me most is the fact that many expensive relational database systems are simply not capable of handling transactional DDLs (such as CREATE TABLE, CREATE INDEX, and so on).

How can you ever deploy software in a reasonable way, if CREATE TABLE will implicitly commit a transaction? How can you ever deploy software if your scripts cannot be atomic? How can you ever handle errors that way? 

The scenario 

Let us assume you are deploying software – all you do is adding three tables.

You might come up with a script:

This script contains a little mistake. Who is capable of writing long scripts without a single mistake? I am clearly not able to do that. So, if you run that script you need a “fix script” to clean out the problems of the first one. Who says that the cleanout script will be any better than the script you are trying to write? Deploying software in a non-transactional way is definitely not the way to go.

Using transactions includes:

In this example we will use a file called START.sql to deploy:

[hs@paula tmp]$ cat START.sql


 i module_1.sql


We can include additional modules here:

 [hs@paula tmp]$ cat module_1.sql

CREATE TABLE a (aid int4);

CREATE TABBLE b (bid int4);

CREATE TABLE c (cid int4);

Once we have written the code we can test it:

 [hs@paula tmp]$ psql test < START.sql



psql:module_1.sql:2: ERROR: syntax error at or near "TABBLE"

LINE 1: CREATE TABBLE b (bid int4);


psql:module_1.sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block


The important thing here is that there are NO LEFTOVERs. Nothing will stay behind because the transaction will be rolled back. It means that we can already test our stuff without having to persist it. We can nicely fix the script and execute it cleanly then.


And yes, there is a thing called CREATE EXTENSION – we also suggest taking a look at that one.

Comments are closed.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram