文档首页/ 云数据库 GaussDB/ 开发指南（集中式_V2.0-3.x）/ 自治事务/ 规格约束
更新时间：2024-04-25 GMT+08:00
查看PDF
分享

规格约束

  • 自治事务执行时，将会在后台启动自治事务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
父主题： 自治事务

相关文档