Menu
PostgreSQL supports nested transactions since version 8.0 (see commit 573a71a made on June 30, 2004). It supports a slightly different syntax compared to the SQL standard – for example, allowing to omit the word SAVEPOINT in the RELEASE and ROLLBACK statements: SAVEPOINT savepoint_name (doc)
There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. PostgreSQL supports that too; see SET TRANSACTION for details.
DDL auto-commit The first row (n=1) was never explicitly committed. It was inserted with DDL (CREATE TABLE) and DDL are always auto-committed.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With