CODEWITHSUNDEEP
postgresqltransactions
LiKao
LiKao

Reputation: 10658

What is supported as transactional in postgres

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:

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?

Upvotes: 7

Views: 3458

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

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.

Upvotes: 4

user330315
user330315

Reputation:

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

Upvotes: 7

Related Questions