Restrictions

gaussdb=# CREATE TABLE test_trigger_des_tbl(id1 int, id2 int, id3 int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
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

gaussdb=# DROP TABLE test_trigger_des_tbl;
DROP TABLE

gaussdb=# CREATE TABLE t1(a INT ,b TEXT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
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)

gaussdb=# DROP TABLE t1;
DROP TABLE

-- Create a table.
gaussdb=# CREATE TABLE sections(section_id INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'section_id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
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. The PROCEDURE OUT output parameter passes the ref cursor parameter (supported).

   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
   
   gaussdb=# CALL proc_sys_call();
   NOTICE:  OK
   NOTICE:  1
   NOTICE:  2
   NOTICE:  3
   NOTICE:  4
   NOTICE:  4
    proc_sys_call 
   ---------------
   
   (1 row)

2. The PROCEDURE IN or INOUT output parameter passes the ref cursor parameter (not supported).

   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. The FUNCTION RETURN output parameter passes the ref cursor parameter (not supported).

   gaussdb=# DROP PROCEDURE IF EXISTS proc_sys_ref;
   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;
   /
   ERROR:  Autonomous function do not support ref cursor as return types or out, inout arguments.
   DETAIL:  N/A

4. The FUNCTION OUT output parameter passes the ref cursor parameter (not supported).

   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

5. On the client (such as gsql and JDBC), the autonomous transaction PROCEDURE with the output parameter ref cursor is directly called. (Not supported. In this case, cursor data cannot be read.)

   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=# begin;
   BEGIN
   gaussdb=# call proc_sys_ref(null);
            c1
   --------------------
    <unnamed portal 1>
   (1 row)
   
   gaussdb=# fetch "<unnamed portal 1>";
   ERROR:  cursor "<unnamed portal 1>" does not exist
   gaussdb=# end;
   ROLLBACK
   gaussdb=# DROP PROCEDURE proc_sys_ref;
   DROP PROCEDURE
   -- Drop the table.
   gaussdb=# DROP TABLE sections;
   DROP TABLE

gaussdb=# CREATE OR REPLACE PROCEDURE autonomous_test_in_p_116(num1 INT ) 
IMMUTABLE 
AS
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
PERFORM pg_sleep(1);
END;
/
ERROR:  Autonomous transactions do not support STABLE/IMMUTABLE.
DETAIL:  Please remove stable/immutable.

gaussdb=# CREATE OR REPLACE PROCEDURE autonomous_test_in_p_117(num1 INT ) STABLE AS
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
PERFORM pg_sleep(1);
END;
/
ERROR:  Autonomous transactions do not support STABLE/IMMUTABLE.
DETAIL:  Please remove stable/immutable.

gaussdb=# CREATE TABLE test_lock (id INT,a DATE);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# INSERT INTO test_lock VALUES (10,SYSDATE),(11,SYSDATE),(12,SYSDATE);
INSERT 0 3
gaussdb=# CREATE OR REPLACE FUNCTION autonomous_test_lock(num1 INT,num2 INT) RETURNS
INTEGER LANGUAGE plpgsql AS $$
DECLARE num3 INT := 4;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE test_lock SET a=SYSDATE WHERE id =11;
RETURN num1+num2+num3;
END;
$$;
CREATE FUNCTION

gaussdb=# START TRANSACTION;
START TRANSACTION
gaussdb=# UPDATE test_lock SET a=SYSDATE WHERE id =11;
UPDATE 1
gaussdb=# CALL autonomous_test_lock(1,1);
ERROR:  ERROR:  Lock wait timeout: thread 139874535470848 on node datanode1 waiting for ShareLock on transaction 16214 after 120000.124 ms
DETAIL:  blocked by hold lock thread 139874577413888, statement <UPDATE test_lock SET a = "sysdate"() WHERE id =11;>, hold lockmode ExclusiveLock.
CONTEXT:  SQL statement "UPDATE test_lock SET a=SYSDATE WHERE id =11"
PL/SQL function autonomous_test_lock(integer,integer) line 5 at SQL statement
referenced column: autonomous_test_lock

gaussdb=# END;
ROLLBACK
gaussdb=# DROP TABLE test_lock;
DROP TABLE

gaussdb=# CREATE OR REPLACE FUNCTION auto_func() RETURN RECORD
AS
DECLARE
  TYPE rec_type IS RECORD(c1 INT, c2 INT);
  r rec_type;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  r.c1:=101;
  r.c2:=201;
  RETURN r;
END;
/
CREATE FUNCTION

gaussdb=# SELECT auto_func();
ERROR:  unrecognized return type for PLSQL function.
CONTEXT:  referenced column: auto_func

gaussdb=# CREATE OR REPLACE PROCEDURE auto_func(r INT)
AS
DECLARE
  a INT;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 a:=r;
END;
/
CREATE FUNCTION
gaussdb=# call auto_func(1);
ERROR:  ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
PL/SQL function auto_func(integer) line 6 at SQL statement
referenced column: auto_func

gaussdb=# CREATE OR REPLACE FUNCTION test_set() RETURN SETOF INT
AS
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    RETURN QUERY (SELECT unnest(ARRAY[ARRAY[1, 2], ARRAY[3, 4]]));
END;
/
ERROR:  Autonomous transactions do not support RETURN SETOF.
DETAIL:  N/A