云数据库 GAUSSDB-事务管理:示例

时间:2024-11-02 18:45:46

示例

  • 示例1:支持在PL/SQL的存储过程内使用COMMIT/ROLLBACK,后续示例依赖此用例。
    gaussdb=# DROP TABLE IF EXISTS EXAMPLE1; 
    NOTICE:  table "example1" does not exist, skipping
    DROP TABLE
    gaussdb=# CREATE TABLE EXAMPLE1(COL1 INT);
    CREATE TABLE
    gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE()
    AS
    BEGIN
        FOR i IN 0..20 LOOP
            INSERT INTO EXAMPLE1(COL1) VALUES (i);
            IF i % 2 = 0 THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL TRANSACTION_EXAMPLE();
     transaction_example 
    ---------------------
    
    (1 row)
  • 示例2:

    支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK。

    支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK。

    支持DDL在COMMIT/ROLLBACK后的提交/回滚。

    gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK()
    AS
    BEGIN
     DROP TABLE IF EXISTS TEST_COMMIT; 
     CREATE TABLE TEST_COMMIT(A INT, B INT);
     INSERT INTO TEST_COMMIT SELECT 1, 1;
     COMMIT;
            CREATE TABLE TEST_ROLLBACK(A INT, B INT);
     RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT';
    EXCEPTION
        WHEN OTHERS THEN
     INSERT INTO TEST_COMMIT SELECT 2, 2;
     ROLLBACK;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
    NOTICE:  table "test_commit" does not exist, skipping
    CONTEXT:  SQL statement "DROP TABLE IF EXISTS TEST_COMMIT"
    PL/pgSQL function test_commit_insert_exception_rollback() line 3 at SQL statement
     test_commit_insert_exception_rollback 
    ---------------------------------------
    
    (1 row)
  • 示例3:支持在事务块里调用含有COMMIT/ROLLBACK的存储过程,即通过BEGIN/START/END等开启控制的外部事务。
    gaussdb=# BEGIN;
        -- TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK定义见示例2
        CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
    END;
     test_commit_insert_exception_rollback 
    ---------------------------------------
    
    (1 row)
    COMMIT
  • 示例4:支持多数PL/SQL的上下文和语句内调用COMMIT/ROLLBACK,包括常用的IF/FOR/CURSOR LOOP/WHILE。
    gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT2()
    IS
    BEGIN
        DROP TABLE IF EXISTS TEST_COMMIT;
        CREATE TABLE TEST_COMMIT(A INT);
        FOR I IN REVERSE 3..0 LOOP
     INSERT INTO TEST_COMMIT SELECT I;
     COMMIT;
        END LOOP;
        FOR I IN REVERSE 2..4 LOOP
     UPDATE TEST_COMMIT SET A=I;
     COMMIT;
        END LOOP;
    EXCEPTION
    WHEN OTHERS THEN   
     INSERT INTO TEST_COMMIT SELECT 4;
        COMMIT;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL TEST_COMMIT2();
     test_commit2 
    --------------
    
    (1 row)
  • 示例5:支持存储过程返回值与简单表达式计算。
    gaussdb=# CREATE OR REPLACE PROCEDURE exec_func3(RET_NUM OUT INT)
    AS
    BEGIN
        RET_NUM := 1+1;
    COMMIT;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL exec_func3('');
     ret_num 
    ---------
           2
    (1 row)
    gaussdb=# CREATE OR REPLACE PROCEDURE exec_func4(ADD_NUM IN INT)
    AS
    SUM_NUM INT;
    BEGIN
    SUM_NUM := ADD_NUM + exec_func3();
    COMMIT;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL exec_func4(1);
     exec_func4 
    ------------
    
    (1 row)
  • 示例6:支持存储过程内GUC参数的回滚提交。
    gaussdb=# SET explain_perf_mode='normal';
    SET
    gaussdb=# SHOW explain_perf_mode;
     explain_perf_mode 
    -------------------
     normal
    (1 row)
    gaussdb=# SHOW enable_force_vector_engine;
     enable_force_vector_engine 
    ----------------------------
     off
    (1 row)
    gaussdb=# CREATE OR REPLACE PROCEDURE GUC_ROLLBACK()
    AS
    BEGIN
        SET enable_force_vector_engine = on;
        COMMIT;
        SET explain_perf_mode TO pretty;
        ROLLBACK;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL GUC_ROLLBACK();
     guc_rollback 
    --------------
    
    (1 row)
    gaussdb=# SHOW explain_perf_mode;
     explain_perf_mode 
    -------------------
     normal
    (1 row)
    gaussdb=# SHOW enable_force_vector_engine;
     enable_force_vector_engine 
    ----------------------------
     on
    (1 row)
    gaussdb=# SET enable_force_vector_engine = off;
    SET
  • 示例7:不允许Trigger的存储过程包含commit/rollback语句,或调用带有commit/rollback语句的存储过程。
    gaussdb=# CREATE OR REPLACE FUNCTION FUNCTION_TRI_EXAMPLE2() RETURN TRIGGER
    AS
    EXP INT;
    BEGIN
        FOR i IN 0..20 LOOP
            INSERT INTO EXAMPLE1(col1) VALUES (i);
            IF i % 2 = 0 THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
        SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
    END;
    /
    CREATE FUNCTION
    gaussdb=# CREATE TRIGGER TRIGGER_EXAMPLE AFTER DELETE ON EXAMPLE1 
    FOR EACH ROW EXECUTE PROCEDURE FUNCTION_TRI_EXAMPLE2();
    CREATE TRIGGER
    gaussdb=# DELETE FROM EXAMPLE1;
    ERROR:  Can not commit/rollback if it's atomic is true: can not use commit rollback in Complex SQL
    CONTEXT:  PL/pgSQL function function_tri_example2() line 7 at COMMIT
  • 示例8:不支持带有IMMUTABLE以及SHIPPABLE的存储过程调用commit/rollback,或调用带有commit/rollback语句的存储过程。
    gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE1()
    IMMUTABLE
    AS
    EXP INT;
    BEGIN
        FOR i IN 0..20 LOOP
            SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
            IF i % 2 = 0 THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
    END;
    /
    CREATE PROCEDURE
    gaussdb=#CALL TRANSACTION_EXAMPLE1();
    ERROR:  Can not commit/rollback if it's atomic is true: commit/rollback/savepoint is not allowed in a non-volatile function
    CONTEXT:  PL/pgSQL function transaction_example1() line 7 at COMMIT
  • 示例9:不支持存储过程中任何变量的提交,包括存储过程内声明的变量或者传入的参数。
    gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE2(EXP_OUT OUT INT)
    AS
    EXP INT:=-1;
    BEGIN
        EXP_OUT := 0;
        EXP := 0;
        COMMIT;
        DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP);
        DBE_OUTPUT.PRINT_LINE('EXP_OUT IS:'||EXP_OUT);
        EXP := 1;
        EXP_OUT := 1;
        ROLLBACK;
        DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP);
        DBE_OUTPUT.PRINT_LINE('EXP_OUT IS:'||EXP_OUT);
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL TRANSACTION_EXAMPLE2(1);
    EXP IS:0
    EXP_OUT IS:0
    EXP IS:1
    EXP_OUT IS:1
     exp_out 
    ---------
           1
    (1 row)
  • 示例10:不支持出现在SQL中的调用(除了Select Procedure)。
    gaussdb=# CREATE OR REPLACE FUNCTION TRANSACTION_EXAMPLE3()
    RETURN INT
    IS
    BEGIN
        FOR i IN 0..20 LOOP
            INSERT INTO EXAMPLE1 (col1) VALUES (i);
            IF i % 2 = 0 THEN
                EXECUTE IMMEDIATE 'COMMIT';
            ELSE
                EXECUTE IMMEDIATE 'ROLLBACK';
            END IF;
        END LOOP;
        RETURN 1;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# SELECT * FROM example1 WHERE col1=TRANSACTION_EXAMPLE3();
    ERROR:  cannot call transaction statements in EXECUTE IMMEDIATE statement.
    CONTEXT:  PL/pgSQL function transaction_example3() line 6 at EXECUTE statement
  • 示例11:存储过程头带有GUC参数设置的不允许调用commit/rollback语句。
    gaussdbo=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE4()
    SET ARRAY_NULLS TO "ON"
    AS
    BEGIN
        FOR i IN 0..20 LOOP
            INSERT INTO EXAMPLE1 (col1) VALUES (i);
            IF i % 2 = 0 THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL TRANSACTION_EXAMPLE4();
    ERROR:  Can not commit/rollback if it's atomic is true: transaction statement in store procedure with GUC setting in option clause is not supported
    CONTEXT:  PL/pgSQL function transaction_example4() line 6 at COMMIT
  • 示例12:游标open的对象不允许为带有commit/rollback语句的存储过程。
    gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE5(INTIN IN INT, INTOUT OUT INT)
    AS
    BEGIN
    INTOUT := INTIN + 1;
    COMMIT;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE6()
    AS
    CURSOR CURSOR1(EXPIN INT)
    IS SELECT TRANSACTION_EXAMPLE5(EXPIN);
    INTEXP INT;
    BEGIN
        FOR i IN 0..20 LOOP
            OPEN CURSOR1(i);
            FETCH CURSOR1 INTO INTEXP;
            INSERT INTO EXAMPLE1(COL1) VALUES (INTEXP);
            IF i % 2 = 0 THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
            CLOSE CURSOR1;
        END LOOP;
    END; 
    /
    CREATE PROCEDURE
    gaussdb=# CALL TRANSACTION_EXAMPLE6();
    ERROR:  Can not commit/rollback if it's atomic is true: transaction statement in store procedure used as cursor is not supported
    CONTEXT:  PL/pgSQL function transaction_example5(integer) line 4 at COMMIT
    referenced column: transaction_example5
    PL/pgSQL function transaction_example6() line 8 at FETCH
  • 示例13:不支持CURSOR/EXECUTE语句,以及各类表达式内调用COMMIT/ROLLBACK。
    gaussdb=# CREATE OR REPLACE PROCEDURE exec_func1()
    AS
    BEGIN
        CREATE TABLE TEST_exec(A INT);
    COMMIT;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CREATE OR REPLACE PROCEDURE exec_func2()
    AS
    BEGIN
    EXECUTE exec_func1();
    COMMIT;
    END;
    /
    CREATE PROCEDURE
    gaussdb=# CALL exec_func2();
    ERROR:  Can not commit/rollback if it's atomic is true: transaction statement in store procedure used as a expression is not supported
    CONTEXT:  PL/pgSQL function exec_func1() line 4 at COMMIT
    PL/pgSQL function exec_func2() line 3 at EXECUTE statement
support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0816.html