Restrictions

When an autonomous transaction is executed, an autonomous transaction session is started in the background. You can use max_concurrent_autonomous_transactions to set the maximum number of concurrent autonomous transactions. The value range is 0 to 1024.

Default value: 10

When max_concurrent_autonomous_transactions is set to 0, autonomous transactions cannot be executed.

After a new session is started for an autonomous transaction, the default session parameters are used and objects (including session-level variables, local temporary variables, and global temporary table data) of the primary session are not shared.

A trigger function does not support autonomous transactions.

CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

Autonomous transactions cannot be invoked by non-top-layer anonymous blocks (but can only be invoked by top-layer autonomous transactions, including stored procedures, functions, and anonymous blocks).

create table t1(a int ,b text);

DECLARE 
 --PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 DECLARE 
  PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
  dbe_output.print_line('just use call.');
  insert into t1 values(1,'can you rollback!');
 END;
 insert into t1 values(2,'I will rollback!');
 rollback;
END;
/

select * from t1;

Autonomous transactions do not support ref_cursor parameter transfer.

create table sections(section_ID int);
insert into sections values(1);
insert into sections values(1);
insert into sections values(1);
insert into sections values(1);

CREATE OR REPLACE function proc_sys_ref()
return SYS_REFCURSOR
IS
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
        C1 SYS_REFCURSOR;
BEGIN
 OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
 return C1;
END;
/

CREATE OR REPLACE function proc_sys_ref(OUT C2 SYS_REFCURSOR, OUT a int)
return SYS_REFCURSOR
IS
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
        C1 SYS_REFCURSOR;
BEGIN
 OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
 return C1;
END;
/

Distributed autonomous transactions of the IMMUTABLE and STABLE types cannot be pushed down.

CREATE OR REPLACE procedure autonomous_test_in_p_116(num1 int ) 
IMMUTABLE 
AS
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
perform pg_sleep(1);
END;
/

CREATE OR REPLACE procedure autonomous_test_in_p_117(num1 int ) STABLE AS
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
perform pg_sleep(1);
END;
/

The distributed system does not support detection. When a deadlock occurs, a lock waiting timeout error is reported.

create table test_lock (id int,a date);
insert into test_lock values (10,sysdate),(11,sysdate),(12,sysdate);
CREATE OR REPLACE FUNCTION autonomous_test_lock(num1 int,num2 int) RETURNS
integer LANGUAGE plpgsql AS $$
DECLARE num3 int := 4;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
update test_lock set a=sysdate where id =11;
RETURN num1+num2+num3;
END;
$$;
start transaction;
update test_lock set a=sysdate where id =11;
call autonomous_test_lock(1,1);
END;