PL/pgSQL techniques better than bash for dynamic DO?

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: PL/pgSQL techniques better than bash for dynamic DO?
Date: 2024-04-09 16:33:27
Message-ID: CANzqJaCRQWegYptQfZSba91hynNfa9tLqrO6UaLKnS0UjEEp4w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message yudhi s 2024-04-09 16:36:30 Re: Moving delta data faster
Previous Message Lok P 2024-04-09 16:16:35 Re: Issue with date/timezone conversion function