Limits on PostgreSQL schema changes inside transactions?

Question

My database background is with Oracle, so I was surprised to discover that Postgres includes schema changes in transactions - if you begin one, create a table and then rollback, the table goes away. It works for adding and removing columns as well. Obviously this is very nice.

We're about to make some changes to the way we deploy schema deltas that rely on this feature. Before we do, I'd like to find out how far the transactional guarantee extends, but I can't find any information on it in the documentation. I assume I'm just using the wrong search terms - my searches just go to big lists of commands which include the words 'transaction', 'create' and 'table'.

Can anyone give me some pointers to docs or discussions about transactional schema changes in Postgres? (We're using 8.2.13, although we'll be upgrading in the not too distant future.) Or just some details about statement that won't be included in the transaction?

Answer 1

According to quick grep on docs, these commands cannot be executed in transactions:

• cluster
• commit prepared
• create database
• create tablespace
• discard
• drop database
• drop tablespace
• rollback prepared
• vacuum

Answer 2

• nextval and setval operations on sequences are never rolled back.
• REINDEX DATABASE
• REINDEX SYSTEM

There's an article about transactional DDL on the PostgreSQL Wiki