规格约束

注意:

  • 自治事务执行时,将会在后台启动自治事务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;
    
  • 自治事务不支持非顶层匿名块调用(仅支持顶层自治事务,包括存储过程、函数、匿名块)。

  • 自治事务不支持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);
    
    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 PROCEDURE proc_sys_call() AS 
    DECLARE
      C1 SYS_REFCURSOR;
      TEMP NUMBER(4);
    BEGIN
      c1 = proc_sys_ref();
      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;
    /
    
    select proc_sys_call();
    
    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;
    /
    
    CREATE OR REPLACE PROCEDURE proc_sys_call() AS 
    DECLARE
      C1 SYS_REFCURSOR;
      TEMP NUMBER(4);
      a int;
    BEGIN
      OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
      c1 = proc_sys_ref(C1,a);
      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;
    /
    
    select proc_sys_call();
    
  • 自治事务函数不支持返回非out形式的record类型。

  • 不支持修改自治事务的隔离级别。

  • 不支持自治事务返回集合类型(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;
    $$
    ;
    
意见反馈
编组 3备份
    openGauss 2024-11-01 08:47:40
    取消