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