An SPL program is declared as an autonomous transaction when the following directive is specified in the declaration section of the SPL block:

PRAGMA AUTONOMOUS_TRANSACTION;

An autonomous transaction is an independent transaction started by a calling program. A commit or rollback of SQL statements within the autonomous transaction has no effect on the commit or rollback in any transaction of the calling program. A commit or rollback in the calling program has no effect on the commit or rollback of SQL statements in the autonomous transaction.

The following SPL programs can include PRAGMA AUTONOMOUS_TRANSACTION:

  • Standalone procedures and functions
  • Anonymous blocks
  • Procedures and functions declared as subprograms within packages and other calling procedures, functions, and anonymous blocks
  • Triggers
  • Object type methods

The following issues and restrictions are related to autonomous transactions:

  • Each autonomous transaction consumes a connection slot as long as it is in progress. In some cases, this may mean that the max_connections parameter in the postgresql.conf file needs to be raised.
  • In most respects, an autonomous transaction behaves exactly as if it was a completely separate session, but GUCs (that is, settings established with SET) are a deliberate exception. Autonomous transactions absorb the surrounding values and can propagate values they commit to the outer transaction.
  • Autonomous transactions can be nested. A maximum of 16 levels of autonomous transactions are allowed within a single session.
  • Parallel query is not supported within autonomous transactions.
  • The implementation of PolarDB databases compatible with Oracle of autonomous transactions is not entirely compatible with Oracle databases in that the autonomous transactions for PolarDB databases compatible with Oracle do not produce an error if an uncommitted transaction exists at the end of an SPL block.

The following set of examples illustrate the usage of autonomous transactions. This first set of scenarios show the default behavior when no autonomous transactions exist.

Before each scenario, the dept table is reset to the following initial values:

SELECT * FROM dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)    

Scenario 1a - No autonomous transactions with only a final COMMIT statement

This first set of scenarios show the insertion of three rows starting just after the initial BEGIN statement of the transaction, then from within an anonymous block within the starting transaction, and finally from a stored procedure executed from within the anonymous block.

The following example shows the stored procedure:

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;

The following example shows the PostgreSQL session:

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
END;
COMMIT;

After the final commit, all three rows are inserted:

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |     loc
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(7 rows)

Scenario 1b - No autonomous transactions, but a final ROLLBACK statement

The next scenario shows that a final ROLLBACK statement after all inserts results in the rollback of all three insertions:

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
END;
ROLLBACK;

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

Scenario 1c - No autonomous transactions, but anonymous block ROLLBACK

A ROLLBACK statement given at the end of the anonymous block also eliminates all three prior insertions:

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    ROLLBACK;
END;
COMMIT;

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

The next set of scenarios shows the effect of using autonomous transactions with PRAGMA AUTONOMOUS_TRANSACTION in various locations.

Scenario 2a - Autonomous transaction of anonymous block with COMMIT

The procedure remains as initially created:

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;

PRAGMA AUTONOMOUS_TRANSACTION is given with the anonymous block along with the COMMIT statement at the end of the anonymous block.

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
ROLLBACK;    

After the ROLLBACK statement at the end of the transaction, only the first row insertion at the very beginning of the transaction is discarded. The other two row insertions within the anonymous block with PRAGMA AUTONOMOUS_TRANSACTION have been independently committed.

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |     loc
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(6 rows)

Scenario 2b - Autonomous transaction anonymous block with COMMIT including procedure with ROLLBACK, but not an autonomous transaction procedure

The procedure has the ROLLBACK statement at the end. However, note that PRAGMA ANONYMOUS_TRANSACTION is not included in this procedure.

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;

The rollback within the procedure removes the two rows inserted within the anonymous block (deptno 60 and 70) before the final COMMIT statement within the anonymous block.

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
COMMIT;

After the final commit at the end of the transaction, the only row inserted is the first one from the beginning of the transaction. Because the anonymous block is an autonomous transaction, the rollback within the enclosed procedure has no effect on the insertion that occurs before the anonymous block is executed.

SELECT * FROM dept ORDER by 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
(5 rows)

Scenario 2c - Autonomous transaction anonymous block with COMMIT including procedure with ROLLBACK that is also an autonomous transaction procedure

The procedure with the ROLLBACK statement at the end also has PRAGMA ANONYMOUS_TRANSACTION included. This isolates the effect of the ROLLBACK statement within the procedure.

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;

The rollback within the procedure removes the row inserted by the procedure, but not the other row inserted within the anonymous block.

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
COMMIT;

After the final commit at the end of the transaction, the row inserted is the first one from the beginning of the transaction as well as the row inserted at the beginning of the anonymous block. The only insertion rolled back is the one within the procedure.

SELECT * FROM dept ORDER by 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     60 | FINANCE    | CHICAGO
(6 rows)

The following sections show examples of PRAGMA AUTONOMOUS_TRANSACTION in a couple of other SPL program types.

Autonomous transaction trigger

The following example shows the effect of declaring a trigger with PRAGMA AUTONOMOUS_TRANSACTION.

The following table is created to log changes to the emp table:

CREATE TABLE empauditlog (
    audit_date      DATE,
    audit_user      VARCHAR2(20),
    audit_desc      VARCHAR2(20)
);

The following example shows the trigger attached to the emp table that inserts these changes into the empauditlog table. Note the inclusion of PRAGMA AUTONOMOUS_TRANSACTION in the declaration section.

CREATE OR REPLACE TRIGGER emp_audit_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_action        VARCHAR2(20);
BEGIN
    IF INSERTING THEN
        v_action := 'Added employee(s)';
    ELSIF UPDATING THEN
        v_action := 'Updated employee(s)';
    ELSIF DELETING THEN
        v_action := 'Deleted employee(s)';
    END IF;
    INSERT INTO empauditlog VALUES (SYSDATE, USER,
        v_action);
END;

The following two inserts are made into the emp table within a transaction started by the BEGIN statement:

BEGIN;
INSERT INTO emp VALUES (9001,'SMITH','ANALYST',7782,SYSDATE,NULL,NULL,10);
INSERT INTO emp VALUES (9002,'JONES','CLERK',7782,SYSDATE,NULL,NULL,10);

The following example shows the two new rows in the emp table as well as the two entries in the empauditlog table:

SELECT * FROM emp WHERE empno > 9000;

 empno | ename |   job   | mgr  |      hiredate      | sal | comm | deptno
-------+-------+---------+------+--------------------+-----+------+--------
  9001 | SMITH | ANALYST | 7782 | 23-AUG-18 07:12:27 |     |      |     10
  9002 | JONES | CLERK   | 7782 | 23-AUG-18 07:12:27 |     |      |     10
(2 rows)

SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "audit date",
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     audit date     |  audit_user  |    audit_desc
--------------------+--------------+-------------------
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
(2 rows)

But then the ROLLBACK statement is given during this session. The emp table no longer contains the two rows, but the empauditlog table still contains its two entries because the trigger implicitly performed a commit and PRAGMA AUTONOMOUS_TRANSACTION commits those changes independent from the rollback given in the calling transaction.

ROLLBACK;

SELECT * FROM emp WHERE empno > 9000;

 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "audit date",
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     audit date     |  audit_user  |    audit_desc
--------------------+--------------+-------------------
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
(2 rows)

Object type methods of autonomous transactions

The following example shows the effect of declaring an object method with PRAGMA AUTONOMOUS_TRANSACTION.

The following object type and object type body are created. The member procedure within the object type body contains PRAGMA AUTONOMOUS_TRANSACTION in the declaration section along with COMMIT at the end of the procedure.

CREATE OR REPLACE TYPE insert_dept_typ AS OBJECT (
    deptno          NUMBER(2),
    dname           VARCHAR2(14),
    loc             VARCHAR2(13),
    MEMBER PROCEDURE insert_dept
);

CREATE OR REPLACE TYPE BODY insert_dept_typ AS
    MEMBER PROCEDURE insert_dept
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO dept VALUES (SELF.deptno,SELF.dname,SELF.loc);
        COMMIT;
    END;
END;

In the following anonymous block, an insert is performed into the dept table, followed by invocation of the insert_dept method of the object, ending with a ROLLBACK statement in the anonymous block.

BEGIN;
DECLARE
    v_dept          INSERT_DEPT_TYP :=
                      insert_dept_typ(60,'FINANCE','CHICAGO');
BEGIN
    INSERT INTO dept VALUES (50,'HR','DENVER');
    v_dept.insert_dept;
    ROLLBACK;
END;

Because insert_dept has been declared as an autonomous transaction, its insert of department number 60 remains in the table, but the rollback removes the insertion of department 50.

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
(5 rows)