Transaction Control Statements

A stored procedure is in a transaction. When the outermost stored procedure is called, a transaction is automatically started. When the call ends, the transaction is automatically committed. If an exception occurs, the transaction is rolled back. In addition to the system's automatic transaction control, you can use the COMMIT or ROLLBACK statement to control transactions in stored procedures. When the COMMIT or ROLLBACK statement is called in a stored procedure, the current transaction is committed or rolled back, and a new transaction is automatically started. All subsequent operations are performed in the new transaction.

Scenarios

Stored procedures support COMMIT and ROLLBACK statements that contain DML statements.
Stored procedures support COMMIT and ROLLBACK statements that contain DDL statements.
COMMIT and ROLLBACK statements can be used to call nested stored procedures.
Stored procedures containing EXCEPTION support COMMIT and ROLLBACK statements.
Anonymous blocks support COMMIT and ROLLBACK statements.
Transaction blocks support stored procedures with the COMMIT or ROLLBACK statement.

Constraints

COMMIT and ROLLBACK statements are not allowed in functions.
Stored procedures that contain the COMMIT or ROLLBACK statement are not allowed in functions.
COMMIT and ROLLBACK statements are not allowed for cursor loop writing.
COMMIT and ROLLBACK statements are not allowed for stored procedures with parameters.
COMMIT and ROLLBACK statements are not allowed for a cursor loop. If a COMMIT or ROLLBACK statement is executed, the cursor will be disabled.
Calling in SQL statements (other than SELECT PROCEDURE) is not supported.
COMMIT and ROLLBACK statements cannot be executed in a stored procedure after an explicit subtransaction savepoint.

Examples

Example 1: Stored procedures support COMMIT and ROLLBACK commands that contain DML statements.

       
            CREATE TABLE IF NOT EXISTS test1(a int, b int);
 
CREATE OR REPLACE PROCEDURE transaction_test1()
AS
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/
 
CALL transaction_test1();

Example 2: Stored procedures support COMMIT and ROLLBACK statements that contain DDL statements.

       
            DROP TABLE IF EXISTS test2;
 
CREATE OR REPLACE PROCEDURE transaction_test2()
AS
BEGIN
    CREATE TABLE test2(a int);
    COMMIT;
DROP TABLE test2;
END;
/
 
CALL transaction_test2();

Example 3: Call a nested stored procedure using the COMMIT or ROLLBACK statement.

       
            CREATE OR REPLACE PROCEDURE transaction_test3()
AS
BEGIN
    PERFORM transaction_test1();
    ROLLBACK;
PERFORM transaction_test2();
ROLLBACK;
END;
/
 
CALL transaction_test3();

Example 4: Call a stored procedure that contains EXCEPTION using the COMMIT or ROLLBACK statement.

       
            CREATE OR REPLACE PROCEDURE TEST_COMMIT_EXCEPTION()
AS
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    INSERT INTO test1 (a) VALUES (2);
    COMMIT;
    INSERT INTO test1 (a) VALUES (3);
    RAISE EXCEPTION 'RAISE EXCEPTION-A';
EXCEPTION
    WHEN OTHERS THEN
    INSERT INTO test1 (a) VALUES (4);
END;
/
 
CALL TEST_COMMIT_EXCEPTION();

Example 5: Anonymous blocks support COMMIT and ROLLBACK statements.

       
            DO LANGUAGE plpgsql $$
BEGIN
    BEGIN
        INSERT INTO test1 (a) VALUES (1);
        COMMIT;
        INSERT INTO test1 (a) VALUES (1/0);
        COMMIT;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'caught division_by_zero';
    END;
END;
$$;

Example 6: Transaction blocks support stored procedures with the COMMIT or ROLLBACK statement.

       
            CREATE TABLE IF NOT EXISTS test1(a int, b int);
 
CREATE OR REPLACE PROCEDURE transaction_test1()
AS
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/
 
START TRANSACTION;
CALL transaction_test1();
COMMIT;

Example 7: COMMIT and ROLLBACK statements are not allowed in functions.

       
            CREATE FUNCTION transaction_test2() RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
    RETURN 1;
END
$$;
 
SELECT transaction_test2();

Example 8: Stored procedures that contain the COMMIT or ROLLBACK statement are not allowed in functions.

       
            CREATE OR REPLACE PROCEDURE transaction_test1()
AS
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    ROLLBACK;
    INSERT INTO test1 (a) VALUES (2);
END;
/
CREATE FUNCTION transaction_test3() RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
    CALL transaction_test1();
    RETURN 1;
END;
$$;
 
SELECT transaction_test3();

Example 9: COMMIT and ROLLBACK statements are not allowed for cursor loop writing.

       
            DO LANGUAGE plpgsql $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN UPDATE test2 SET x = x * 2 RETURNING x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        ROLLBACK;
    END LOOP;
END;
$$;

Example 10: COMMIT and ROLLBACK statements are not allowed for stored procedures with parameters.

       
            CREATE OR REPLACE PROCEDURE transaction_test5()
SET work_mem = 555
AS
BEGIN
    insert into test1 select setting from pg_settings where name like 'work_mem';
    COMMIT;
END;
/
 
CALL transaction_test5();

Example 11: COMMIT and ROLLBACK statements are not allowed for a cursor loop. If a COMMIT or ROLLBACK statement is executed, the cursor will be disabled.

       
        
          
          
            DROP TABLE IF EXISTS cursor_back_tab2;
CREATE TABLE cursor_back_tab2(i_int bigint,i_char varchar ,i_numeric numeric(18,3),i_data date);
CREATE OR REPLACE PROCEDURE cursor_back_pro_2()
AS
declare 
o_int bigint;
o_char varchar;
o_numeric numeric;
o_data date;
cursor cursor1 for select * from cursor_back_tab1 order by 1,2,3,4;
begin 
    open cursor1;
    fetch  next from cursor1 into o_int,o_char,o_numeric,o_data;
    LOOP
           fetch  next from cursor1 into o_int,o_char,o_numeric,o_data;
exit when cursor1%NOTFOUND;
insert into cursor_back_tab2 values(o_int,o_char,o_numeric,o_data);
    END LOOP;
COMMIT;
ROLLBACK;
CLOSE cursor1;
END;
/

CALL cursor_back_pro_2();

           

         

       
      

Example 12: Calling in SQL statements (other than SELECT PROCEDURE) is not supported.

       
            CREATE TABLE IF NOT EXISTS test1(a int, b int);
 
CREATE OR REPLACE PROCEDURE transaction_test1()
AS
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/
 
SELECT transaction_test1() from test1;

Example 13: COMMIT and ROLLBACK statements cannot be executed in a stored procedure after an explicit subtransaction savepoint.

       
            CREATE OR REPLACE PROCEDURE sp_txn_test1_1()
AS
BEGIN
INSERT INTO test1 (a) VALUES (1);
COMMIT;
INSERT INTO test1 (a) VALUES (2);
END;
/
 
BEGIN;
SAVEPOINT s1;
CALL sp_txn_test1_1();
ROLLBACK;