Transactional DDLs

07.2015 / Category: / Tags:

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:

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:

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

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:

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.

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.

0 0 votes
Article Rating
Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments
Slava Moudry
Slava Moudry
8 years ago

>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.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram