16.1. Behavior

Although an autonomous transaction is always run inside another transaction, only a single transaction can be active at any given moment; other transactions have to wait until the active transaction is complete. When an autonomous transaction is started, its parent transaction is paused and pushed into the autonomous transaction stack. Once the autonomous transaction is committed or rolled back, the parent transaction is popped from the stack and resumed. You cannot commit the parent transaction until all its autonomous transactions are complete.

Within a single parent transaction, you can start several autonomous ones, which can be both consecutive and nested. By default, Postgres Pro allows running up to 100 autonomous transactions in all sessions simultaneously. You can increase this limit by changing the max_autonomous_transactions configuration parameter. The maximum nesting level is restricted to 128 and cannot be changed.

Autonomous transactions do not support the following scenarios, so they will cause an error if attempted:

  • The FOR UPDATE locking clause is not supported, so you cannot lock a row in a parent transaction and skip it in the autonomous transaction.

  • If created with the ON COMMIT DROP clause, temporary tables with the same name cannot be used in nested autonomous transactions.

  • You cannot redefine isolation level for autonomous transactions within PL/pgSQL or PL/Python blocks.

  • Logical replication slots cannot be used in autonomous transactions.

  • Autonomous transactions cannot be used together with the following extensions:

    • in_memory

    • online_analyze

Consider the following examples. A continuous line denotes an active transaction, while a dotted line denotes a transaction which has been paused and pushed into the autonomous transaction stack. Time flows downwards.

BEGIN; -- starts ordinary transaction T0
|
INSERT INTO t VALUES (1);
:\
: BEGIN AUTONOMOUS TRANSACTION; -- starts autonomous transaction 
: |                             -- T1, pushes T0 into stack
: |
: INSERT INTO t VALUES (2);
: |
: COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION; 
: |                             -- ends autonomous transaction 
: |                             -- T1, pops transaction T0 from stack
:/
COMMIT / ROLLBACK;              -- ends transaction T0

Depending on the two choices between COMMIT and ROLLBACK, the following query can return four different results:

SELECT sum(x) FROM t;

A parent transaction can have several autonomous transactions, which can be nested or follow one another:

BEGIN;                            -- starts ordinary transaction T0
|
INSERT INTO t VALUES (1);
:\
: BEGIN AUTONOMOUS TRANSACTION;   -- starts autonomous transaction
: |                               -- T1, pushes T0 into stack
: |
: INSERT INTO t VALUES (2);
: |
: COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION;
: |                               -- ends autonomous transaction
: |                               -- T1, pops T0 from stack
:/
|
:\
: BEGIN AUTONOMOUS TRANSACTION;   -- starts autonomous transaction
: |                               -- T2, pushes T0 into stack
: |
: INSERT INTO t VALUES (4);
: :\
: : BEGIN AUTONOMOUS TRANSACTION; -- starts autonomous transaction
: : |                             -- T3, pushes T2 into stack
: : |
: : INSERT INTO t VALUES (6);
: : |
: : COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION;
: : |                             -- ends autonomous transaction
: : |                             -- T3, pops T2 from stack
: :/
: |
: COMMIT AUTONOMOUS TRANSACTION / ROLLBACK AUTONOMOUS TRANSACTION;
: |                               -- ends autonomous transaction
: |                               -- T2, pops T0 from stack
:/
COMMIT / ROLLBACK;                -- ends transaction T0