Postgresql – do periodic commits in the purge function

postgresqlpostgresql-9.6transaction

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

Best Answer

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.