# 规格约束
![](https://support.huaweicloud.com/centralized-devg-v2-gaussdb/public_sys-resources/caution_3.0-zh-cn.png)
- 自治事务执行时，将会在后台启动自治事务session，可以通过max_concurrent_autonomous_transactions设置自治事务执行的最大并行数量，该参数取值范围为0\~1024，默认值为10。
- 当max_concurrent_autonomous_transactions参数设置为0时，自治事务将无法执行。
- 自治事务新启session后，将使用默认session参数，不共享主session下对象（包括session级别变量，本地临时变量，全局临时表的数据等）。
- 自治事务受通信缓冲区影响，返回给客户端的信息大小受限于通信缓冲区长度，超过通信缓冲区长度时报错。
 
- 触发器函数不支持自治事务。
  ```
  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;
  ```
  
- 自治事务不支持非顶层匿名块调用（仅支持顶层自治事务,包括存储过程、函数、匿名块）。
  ```
  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;
  ```
  
- 自治事务仅支持PROCEDURE OUT参数传递ref cursor参数，不支持IN、INOUT以及FUNCTION传递ref cursor参数。
  ```
  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);
  1. PROCEDURE OUT出参传递ref cursor(支持）
  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 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;
  /
  2. PROCEDURE IN或INOUT出参传递ref cursor(不支持）
  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 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;
  /
  3. FUNCTION RETURN传递ref cursor（不支持）
  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;
  /
  4. FUNCTION OUT出参传递ref cursor（不支持）
  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;
  /
  ```
  
- 自治事务函数不支持返回非out形式的record类型。
  ```
  create table test_in (id int,a date);
  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;
  $$;
  select ok.id,ok.a from autonomous_f_139_7() as ok(id int,a date);
  ```
  
- 不支持修改自治事务的隔离级别。
- 不支持自治事务返回集合类型（setof）。
  ```
  create table test_in (id int,a date);
  create table test_main (id int,a date);
  insert into test_main values (1111,'2021-01-01'),(2222,'2021-02-02');
  truncate test_in,test_main;
  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;
  $$
  ;
  ```