# 规格约束
![](https://support.huaweicloud.com/centralized-devg-v3-gaussdb/public_sys-resources/caution_3.0-zh-cn.png)
- 自治事务执行时，将会在后台启动自治事务session，可以通过max_concurrent_autonomous_transactions设置自治事务执行的最大并行量，该参数取值范围为0\~10000，默认值为10。
- 当max_concurrent_autonomous_transactions参数设置为0时，自治事务将无法执行。
- 自治事务新启session后，将使用默认session参数，不共享主session下对象（包括session级别变量，本地临时变量，全局临时表的数据等）。
- 自治事务理论上限为10000，实际上限为动态值，参考GUC参数**max_concurrent_autonomous_transactions**描述。
- 自治事务受通信缓冲区影响，返回给客户端的信息大小受限于通信缓冲区长度，超过通信缓冲区长度时报错。
- 自治事务的锁不受lock timeout影响，锁超时时间为2147483s，自治事务执行超过此时间会报错锁超时。
- 自治事务设置建立连接超时时间5s，建立连接尝试5次。建立连接期间不立即响应信号，每次建立连接前检查信号。高并发、高CPU、高内存，以及线程池扩容场景下可能存在超时报错现象。
 
- 触发器函数不支持自治事务。
  ```
  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
  ```