There's more…

The following commands cannot be included in a script that uses transactions in the way we just described:

• CREATE DATABASE/DROP DATABASE
• CREATE TABLESPACE/DROP TABLESPACE
• CREATE INDEX CONCURRENTLY
• VACUUM
• REINDEX DATABASE/REINDEX SYSTEM
• CLUSTER

None of these actions need to be run manually on a regular basis within complex programs, so this shouldn't be a problem for you.

Note also that these commands do not substantially alter the logical content of a database; that is, they don't create new user tables or alter any rows, so there's less need to use them inside complex transactions.

While PostgreSQL does not support nested transaction commands, it supports the notion of SAVEPOINT, which can be used to achieve the same behavior. Suppose we wanted to implement the following pseudocode:

(begin transaction T1)
  (statement 1)
  (begin transaction T2)
    (statement 2)
  (commit transaction T2)
  (statement 3)
(commit transaction t1)

The effect we seek has the following properties:

• If statements 1 and 3 succeed, and statement 2 fails, then statements 1 and 3 will be committed
• If all three statements succeed, then they will all be committed
• Otherwise, no statement will be committed

These properties also hold with the following PostgreSQL commands:

BEGIN;
  – (statement 1)
  SAVEPOINT T2;
    – (statement 2)
  RELEASE SAVEPOINT T2; /* we assume that statement 2 does not fail */
  – (statement 3)
COMMIT;

This form, as noted in the code, applies only if statement 2 does not fail. If it fails, we must replace RELEASE SAVEPOINT with ROLLBACK TO SAVEPOINT, or we will get an error. This is a slight difference with top-level transaction commands; a COMMIT statement is silently converted in a ROLLBACK when the transaction is in a failed state.