Transactional DDLs – a message to non-PostgreSQL users

Posted on 2013-06-01 by Hans-Jürgen Schönig

Sometimes it is astonishing to see, which basic functionality commercial vendors are lacking. 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:

CREATE TABLE a (aid int4);
CREATE TABBLE b (bid int4);
CREATE TABLE c (cid int4);

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 and includes:

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

[hs@paula tmp]$ cat START.sql

BEGIN;

\i module_1.sql

ROLLBACK;

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

BEGIN

CREATE TABLE

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

ROLLBACK

The important thing here is that there are NO LEFTOVERs. Nothing will stay behind because the transaction will be rollbacked. 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.

PostgreSQL: CREATE EXTENSION

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