postgres stored procedure pl pgSQL transaction managament - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.fastware.com/postgresql-insider-mgr-sto-prc
• https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/

Use CALL to execute the stored procedure.

If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Czyli nie moze zawierac commit-a.

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

call proc_with_commit();   -- ok
begin;  -- synonim start transaction
call proc_with_commit();  -- error - not supported nested transaction
end;    -- synonnym commit 
begin;  -- synonim start transaction
call proc_without_commit();  -- ok
end;    -- synonnym commit 

Transaction control

CREATE PROCEDURE proc2()
LANGUAGE plpgsql
AS $$
BEGIN
   FOR idx IN 1..100 LOOP
       INSERT INTO my_tbl(col_num) VALUES(idx);
       IF idx % 10 = 0 THEN A
            COMMIT; B
        ELSE
            ROLLBACK; C 
        END IF;
    END LOOP;
END
$$;

Call (nest) another stored procedure in a stored procedure.

CREATE PROCEDURE proc3() LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO my_tbl VALUES(1); A 
 CALL proc4();
INSERT INTO my_tbl VALUES(4); D 
 COMMIT;
END;
$$;
CREATE PROCEDURE proc4() LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO my_tbl VALUES(2); B 
 ROLLBACK;
INSERT INTO my_tbl VALUES(3); C 
END;
$$;

Restrictions on transaction control

COMMIT/ROLLBACK cannot be written between BEGIN and EXCEPTION. Instead, use EXCEPTION to make necessary processing into a subblock from BEGIN to END, and write COMMIT/ROLLBACK outside the subblock.

Return cursor from prcedure

There are two kinds of CURSORs: WITH HOLD outlive transactions and WITHOUT HOLD (the default) don't. You may point a refcursor variable to a WITH HOLD cursor in plpgsql with a bit of dynamic SQL. Here's a modified version of your procedure doing that:

CREATE OR REPLACE PROCEDURE public.build_and_populate(INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$
    BEGIN
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;
        COMMIT;
        EXECUTE 'DECLARE ' || quote_ident(cresults::text) || ' CURSOR WITH HOLD FOR SELECT * FROM procsampledata';                                               
    END;
$procedure$

postgres=# call BUILD_AND_POPULATE('res');
 cresults 
----------
 res
(1 row)

postgres=# fetch all from res;
 x  | c2 | c3 |               md5                
----+----+----+----------------------------------
  1 |  1 |  2 | 11a3d2e637332a25118c2f4d5dac49c0
  2 |  1 |  2 | 4c5b9cb5ec79479a9daa9ae7a131a078
  3 |  1 |  2 | 320b49912e94de90c3370836706c5494
  4 |  1 |  2 | a9ccf45dbbbbec19d18b69cf4a0fc26b
  5 |  1 |  2 | 1a52801f1761cb0e357a4468803473e1
  6 |  1 |  2 | 96c395dad11ee19526bc08b5ad114905
  7 |  1 |  2 | 7d0da9a0cf2520ee871185677d7062cf
  8 |  1 |  2 | 65ecf1e68806a0b9133fa0e47056574e
  9 |  1 |  2 | bae5c688e1ef6b9c389b37ecc7a18342
 10 |  1 |  2 | ed07142f3b51a282c05370890a8af468
(10 rows)

postgres=# close res;
CLOSE CURSOR