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.
Table of Contents
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
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:
|
1 2 3 4 5 |
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:
|
1 2 |
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:
|
1 2 |
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.
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.
|
1 2 |
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 Facebook or LinkedIn.
>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.
one case when someone would need it - is when you try to run VACUUM from stored procedure. Since stored procedure creates an implicit transaction, you can't run vacuum in it.