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.