Updated on 2025-07-15 GMT+08:00

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, and the default value is 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.
  • Autonomous transactions are affected by the communication buffer. The size of the information returned to the client is limited by the length of the communication buffer. If the size exceeds the length of the communication buffer, an error is reported.
  • 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 called by non-top-layer anonymous blocks (but can only be called 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;
    
  • Autonomous transaction functions only return records in the out format.
  • The isolation level of an autonomous transaction cannot be changed.
  • Autonomous transactions do not support the setof return type.