PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now.
I must purge the oldest X period of records from 70 tables, every Sunday.
The field name, interval (X days or months) and date (CURRENT_DATE or
CURRENT_TIMESTAMP) varies for each table.
Thus, I put all the relevant data in a tab-separated value file, and use
bash to read through it, purging one table at a time. This works well,
except for Foreign Key constraints; carefully ordering the file to purge
records in the correct order eliminates most FK errors, but not all.
Therefore, I created an anonymous DO statement to delete the "deletable"
old records, while skipping the ones that would fail from a FK constraint.
(Eventually, the records in the FK table will get deleted, so eventually
the records who's DELETE failed will succeed in getting deleted.)
(NOTE: I cannot change the FK constraints to ON DELETE CASCADE, and nor do
I want to fight with the 3rd party app vendor, since it defeats the purpose
of FK constraints.)
Here's the snippet of bash code:
local Schema=$1
local Table=$2
local Field=$3
local DtCol=$4 # CURRENT_TIMESTAMP or CURRENT_DATE
local Thresh=$5 # example: '90 day'
local FQTable=${Schema}.${Table}
DeS="DO \$\$
DECLARE
delsum INTEGER = 0;
delcnt INTEGER;
skipsum integer = 0;
cur_row CURSOR FOR
SELECT $Field, ${Table}_id
from ${FQTable}
where $Field < (${DtCol} - interval ${Thresh});
BEGIN
FOR arow IN cur_row
LOOP
BEGIN
DELETE FROM ${FQTable} WHERE CURRENT OF cur_row;
GET DIAGNOSTICS delcnt = ROW_COUNT;
delsum = delsum + delcnt; EXCEPTION
WHEN others THEN
skipsum = skipsum + 1;
RAISE NOTICE ' Skipped ${FQTable} WHERE ${Table}_id = %;
${Field} = %',
arow.${Table}_id, arow.${Field};
END;
END LOOP;
RAISE NOTICE 'Sum of deleted rows: %', delsum;
RAISE NOTICE 'Sum of skipped rows: %', skipsum;
END \$\$;
"
It generates the perfectly functional SQL:
DO $$
DECLARE
delsum INTEGER = 0;
delcnt INTEGER;
skipsum integer = 0;
cur_row CURSOR FOR
SELECT modified_on, check_id
from tms.check
where modified_on < (CURRENT_TIMESTAMP - interval '90 day');
BEGIN
FOR arow IN cur_row
LOOP
BEGIN
DELETE FROM tms.check WHERE CURRENT OF cur_row;
GET DIAGNOSTICS delcnt = ROW_COUNT;
delsum = delsum + delcnt;
EXCEPTION
WHEN others THEN
skipsum = skipsum + 1;
RAISE NOTICE ' Skipped tms.check WHERE check_id = %;
modified_on = %',
arow.check_id, arow.modified_on;
END;
END LOOP;
RAISE NOTICE 'Sum of deleted rows: %', delsum;
RAISE NOTICE 'Sum of skipped rows: %', skipsum;
END $$;
Can I do this better in PL/pgSQL with dynamic SQL (that doesn't get hairy
with nested quotes, etc)?
