7

I am trying find out what is postgres can handle safely inside of transaction, but I cannot find the relavant information in the postgres manual. So far I have found out the following:

  • UPDATE, INSERT and DELTE are fully supported inside transactions and rolled back when the transaction is not finished
  • DROP TABLE is not handled safely inside a transaction, and is undone with a CREATE TABLE, thus recreates the dropped table but does not repopulate it
  • CREATE TABLE is also not truly transactionized and is instead undone with a corresponding DROP TABLE

Is this correct? Also I could not find any hints as to the handling of ALTER TABLE and TRUNCATE. In what way are those handled and are they safe inside transactions? Is there a difference of the handling between different types of transactions and different versions of postgres?

LiKao
  • 10,408
  • 6
  • 53
  • 91

2 Answers2

7

DROP TABLE is transactional. To undo this, you need to issue a ROLLBACK not a CREATE TABLE. The same goes for CREATE TABLE (which is also undone using ROLLBACK).

ROLLBACK is always the only correct way to undo a transaction - that includes ALTER TABLE and TRUNCATE.

The only thing that is never transactional in Postgres are the numbers generated by a sequence (CREATE/ALTER/DROP SEQUENCE themselves are transactional though).

  • Yeah, i meant the way it is internally handled... Guess I just was confused by some answers to [this question](http://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql) – LiKao Jun 10 '11 at 13:39
  • @LiKao: the answer in the linked question does not specifically talk about PostgreSQL. For a DBMS that does not support transactional DDL it is true that a DROP TABLE cannot only be "rolled back" by a CREATE TABLE. –  Jun 10 '11 at 13:40
4

Best I'm aware all of these commands are transaction aware, except for TRUNCATE ... RESTART IDENTITY (and even that one is transactional since 9.1.)

See the manual on concurrency control and transaction-related commands.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154