I need to do periodic commits in my purge function

Question

I wrote a purge function that runs in Postgres 9.6. The function goes through all of the tables in our application starting from the bottom most child table and ending with the top most parent table and purges based on date or client. When we run this in production and beta, there are other processes happening at the same time and the database is getting blocking locks based on my uncommitted deletes. One possible solution would be to do a commit every thousand or so rows. But I am new to Postgres and I can't get Postgres 9.6 to do commits within a loop. It's possible that Postgres 9.6 won't do this and maybe Postgres 12 will. We are moving to Postgres 12.

Is there anyway to get the commits to work in Postgres 9.6? I included some test code so that you can test the commits on your own. My actual code is far more complicated.

Thanks

\timing

DROP TABLE IF EXISTS _tmp_test_transactions_table;
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );

create or replace function _tmp_test_transactions ( p_number_of_rows INTEGER )
returns int language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
BEGIN
   START TRANSACTION ISOLATION LEVEL READ COMMITTED;

   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;
      
      IF MOD( v_counter, 10 ) = 0
      THEN
         COMMIT;
         START TRANSACTION ISOLATION LEVEL READ COMMITTED;
      END IF;
   END LOOP;

   SELECT COUNT(*) 
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;
   COMMIT;
END
$fun$;

SELECT _tmp_test_transactions( 100 );
psql:bbyrd_test_transactions.sql:36: ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 6 at SQL statement
Time: 0.473 ms

Answer 1

Transaction control (commit, rollback) is not allowed inside PostgreSQL functions.

Starting with PostgreSQL 11, it's possible in procedures (see CREATE PROCEDURE in the documentation). In fact that's the main difference between functions and procedures.

Before PostgreSQL 11, the ability to commit inside a loop comes from client-side beyond-SQL programming. The psql CLI can of course issue commit and rollback but it doesn't have some of the basic constructs found in programming languages, such as loops.

Answer 2

I ran a test to commit using a procedure and it works well.

Thanks

\timing
Timing is on.
DROP TABLE IF EXISTS _tmp_test_transactions_table;
DROP TABLE
Time: 38.583 ms
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );
CREATE TABLE
Time: 17.007 ms
create or replace procedure _tmp_test_transactions ( p_number_of_rows INTEGER )
language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
   v_dummy       INTEGER;
BEGIN
   TRUNCATE TABLE _tmp_test_transactions_table;
   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;

      INSERT INTO _tmp_test_transactions_table VALUES ( v_counter );
      COMMIT;
   END LOOP;

   SELECT COUNT(*)
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;

   RAISE EXCEPTION 'This is an error, roll back';
END
$fun$;
CREATE PROCEDURE
Time: 5.104 ms
CALL _tmp_test_transactions( 100 );
psql:run_commit_test.sql:34: NOTICE:  Inserted 100 rows in the _tmp_test_transactions_table table
psql:run_commit_test.sql:34: ERROR:  This is an error, roll back
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 22 at RAISE
Time: 116.180 ms
SELECT COUNT(*) FROM _tmp_test_transactions_table;
 count
-------
   100
(1 row)