规格约束

注意: 自治事务执行时,将会在后台启动自治事务session,我们可以通过max_concurrent_autonomous_transactions设置自治事务执行的最大并行数量,该参数取值范围为0~1024,默认值为10。当max_concurrent_autonomous_transactions参数设置为0时,自治事务将无法执行。

  • 触发器函数不支持自治事务。

    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
    		insert into t1 values(1,'can you rollback!');
    	END;
    	insert into t1 values(2,'I will rollback!');
    	rollback;
    END;
    /
    
    select * from t1;
    
  • 自治事务不支持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)。

意见反馈
编组 3备份
openGauss 2024-03-19 00:47:31
取消