Transactional DDLs in PostgreSQL - CYBERTEC

Posted on 2015-07-13 by Hans-Jürgen Schönig

When checking out the new features of Sybase 15.7 (yes, from time to time I get around to see what commercial databases are up to), I stumbled over an interesting and yet amusing line: “Fully Recoverable DDL”. The details seem to indicate that not just Sybase is still having a hard time to handle transactional and crash safe DDLs in a proper way.

After 15 years of professional PostgreSQL I actually don’t remember a time when PostgreSQL did not have transactional DDLs. The beauty of PostgreSQL is that all major DDLs are fully transactional:

test=# BEGIN;
BEGIN

test=# CREATE TABLE t_test (id int, name text);
CREATE TABLE
test=# \d
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | t_test | table | hs
(1 row)

test=# ROLLBACK;
ROLLBACK

test=# \d
No relations found.

Basically everything people need for their daily work is fully transactional. CREATE TABLE, ALTER TABLE, CREATE INDEX – everything works just like a charm.

The exceptions to the rule …

However, there are some minor exceptions to the rule. The most prominent thing is most likely CREATE INDEX CONCURRENTLY. The thing is: CREATE INDEX is a single transaction – CREATE INDEX CONCURRENTLY is not – and therefore it cannot be run inside a normal transaction block. The index needs to be visible to other transactions before the build can even begin:

test=# BEGIN;
BEGIN

test=# CREATE INDEX CONCURRENTLY idx_id ON t_test (id);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

But, there are some more corner cases. CREATE DATABASE is one of them:

test=# CREATE DATABASE xy;
ERROR:  CREATE DATABASE cannot run inside a transaction block

Performing a ROLLBACK in case of CREATE DATABASE should really not be a real problem anyway and nobody has ever complained about it.

The same applies to CREATE TABLESPACE:

test=# CREATE TABLESPACE some_name LOCATION '/storage';
ERROR:  CREATE TABLESPACE cannot run inside a transaction block

Of course there are some more commands along this line – this list is not meant to be complete. However, it has to be pointed out that in PostgreSQL at least 90% of all DDLs fully support transaction blocks as expected.

VACUUM: The great princess

What is interesting is that some people asked for a ROLLBACK on VACUUM. To make it clear: This is not possible and clearly makes no sense.

test=# VACUUM;
ERROR:  VACUUM cannot run inside a transaction block

VACUUM is here to physically clean up rows – it is really pointless to consider a ROLLBACK here (for countless reasons).

In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.