Transactional DDLs

When checking out those new features of Sybase 15.7 (yes, from time to time I got 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).