更新时间:2024-05-07 GMT+08:00

规格约束

  • 自治事务执行时,将会在后台启动自治事务session,我们可以通过max_concurrent_autonomous_transactions设置自治事务执行的最大并行数量,该参数取值范围为0~10000,默认值为10。
  • 当max_concurrent_autonomous_transactions参数设置为0时,自治事务将无法执行。
  • 自治事务新启session后,将使用默认session参数,不共享主session下对象(包括session级别变量,本地临时变量,全局临时表的数据等)。
  • 自治事务理论上限为10000,实际上限为动态值,参考GUC参数max_concurrent_autonomous_transactions描述。
  • 自治事务受通信缓冲区影响,返回给客户端的信息大小受限于通信缓冲区长度,超过通信缓冲区长度时报错。
  • 触发器函数不支持自治事务。
    gaussdb=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
    CREATE TABLE
    gaussdb=# 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;
    ERROR:  Triggers do not support autonomous transactions
    DETAIL:  N/A
    CONTEXT:  compilation of PL/pgSQL function "tri_insert_func" near line 4
  • 自治事务不支持非顶层匿名块调用(仅支持顶层自治事务,包括存储过程、函数、匿名块)。
    gaussdb=# drop table if exists t1;
    gaussdb=# create table t1(a int ,b text);
    CREATE TABLE
    gaussdb=# 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;
    /
    just use call.
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=# select * from t1;
     a | b
    ---+---
    (0 rows)
  • 自治事务仅支持PROCEDURE OUT参数传递ref cursor参数,不支持IN、INOUT以及FUNCTION传递ref cursor参数。
    gaussdb=# create table sections(section_ID int);
    CREATE TABLE
    gaussdb=# insert into sections values(1);
    INSERT 0 1
    gaussdb=# insert into sections values(1);
    INSERT 0 1
    gaussdb=# insert into sections values(1);
    INSERT 0 1
    gaussdb=# insert into sections values(1);
    INSERT 0 1
    
    1. PROCEDURE OUT出参传递ref cursor(支持)
    gaussdb=# CREATE OR REPLACE PROCEDURE proc_sys_ref(OUT c1 refcursor)
    IS
    declare
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
     OPEN c1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
     
    END;
    /
    CREATE PROCEDURE
    
    gaussdb=# CREATE OR REPLACE PROCEDURE proc_sys_call() AS 
    DECLARE
      c1 SYS_REFCURSOR;
      TEMP NUMBER(4);
    BEGIN
      proc_sys_ref(c1);
      if c1%isopen then
        raise notice '%','ok';
     end if;
    
      LOOP
        FETCH C1 INTO TEMP;
        raise notice '%',C1%ROWCOUNT;
        EXIT WHEN C1%NOTFOUND;
      END LOOP;
    END;
    /
    CREATE PROCEDURE
    
    2. PROCEDURE IN或INOUT出参传递ref cursor(不支持)
    gaussdb=# CREATE OR REPLACE PROCEDURE proc_sys_ref(IN c1 refcursor)
    IS
    declare
      PRAGMA AUTONOMOUS_TRANSACTION;
      TEMP NUMBER(4);
    BEGIN
      if c1%isopen then
        raise notice '%','ok';
      end if;
    
      LOOP
        FETCH C1 INTO TEMP;
        raise notice '%',C1%ROWCOUNT;
        EXIT WHEN C1%NOTFOUND;
      END LOOP;
    END;
    /
    CREATE PROCEDURE
    
    gaussdb=# CREATE OR REPLACE PROCEDURE proc_sys_call() AS 
    DECLARE
      c1 SYS_REFCURSOR;
      TEMP NUMBER(4);
    BEGIN
      OPEN c1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
      proc_sys_ref(c1);
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL proc_sys_call();
    ERROR:  Unsupported: ref_cursor parameter is not supported for autonomous transactions.
    CONTEXT:  SQL statement "CALL proc_sys_ref(c1)"
    PL/pgSQL function proc_sys_call() line 7 at PERFORM
    
    3. FUNCTION RETURN传递ref cursor(不支持)
    gaussdb=# 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;
    /
    gaussdb=# DROP PROCEDURE proc_sys_ref;
    DROP PROCEDURE
    
    4. FUNCTION OUT出参传递ref cursor(不支持)
    gaussdb=# CREATE OR REPLACE function proc_sys_ref(C1 out SYS_REFCURSOR)
    return SYS_REFCURSOR
    IS
    declare
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
     OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
     return 1;
    END;
    /
    ERROR:  Autonomous function do not support ref cursor as return types or out, inout arguments.
    DETAIL:  N/A
    CONTEXT:  compilation of PL/pgSQL function "proc_sys_ref" near line 4
  • 自治事务函数不支持直接返回record类型和out出参&record类型同时返回。
    gaussdb=# create table test_in (id int,a date);
    CREATE TABLE
    gaussdb=# CREATE OR REPLACE FUNCTION autonomous_out()
    RETURNS record
    LANGUAGE plpgsql AS $$
    DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
             r1 record;
    BEGIN
        DBE_OUTPUT.PRINT_LINE('this is in autonomous_f_139_7');
        truncate test_in;
        insert into test_in values (1,'1909-01-01');
        select * into r1 from test_in;
    RETURN r1;
    END;
    $$;
    CREATE FUNCTION
    gaussdb=# select ok.id,ok.a from autonomous_out() as ok(id int,a date);
    ERROR:  unrecognized return type for PLSQL function.
    gaussdb=# create type rec is (e1 integer, e2 varchar2);
    CREATE TYPE
    gaussdb=# create or replace function func(ele3 inout varchar2) return rec as
    i integer;
    ele1 rec;
    PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    NULL;
    return ele1;
    end;
    /
    CREATE FUNCTION
    gaussdb=# call func(1);
     e1 | e2
    ----+----
        |
    (1 row)
    
  • 不支持修改自治事务的隔离级别。
  • 不支持自治事务返回集合类型(setof)。
    gaussdb=# drop table if exists test_in;
    gaussdb=# create table test_in (id int,a date);
    CREATE TABLE
    gaussdb=# create table test_main (id int,a date);
    CREATE TABLE
    gaussdb=# insert into test_main values (1111,'2021-01-01'),(2222,'2021-02-02');
    INSERT 0 2
    gaussdb=# truncate test_in,test_main;
    TRUNCATE TABLE
    gaussdb=# CREATE OR REPLACE FUNCTION autonomous_f_022(num1  int) RETURNS SETOF test_in
    LANGUAGE plpgsql AS $$
    DECLARE
    count int :=3;
    test_row test_in%ROWTYPE;
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        while true
        loop
        if count=3 then
        null;
        else 
        if count=2 then
            insert into test_main values (count,'2021-03-03');
            goto pos1;
        end if;                
        end if;
        count=count-1;
        end loop;
        insert into test_main values (1000,'2021-04-04');
         <<pos1>>
         for test_row in select * from test_main
         loop
            return next test_row;
           end loop;
          return;
    END;
    $$
    ;
    ERROR:  Autonomous transactions do not support RETURN SETOF.
    DETAIL:  N/A
    CONTEXT:  compilation of PL/pgSQL function "autonomous_f_022" near line 6