[MASSMAIL]PL/pgSQL techniques better than bash for dynamic DO? - Mailing list pgsql-general
| From | Ron Johnson |
|---|---|
| Subject | [MASSMAIL]PL/pgSQL techniques better than bash for dynamic DO? |
| Date | |
| Msg-id | CANzqJaCRQWegYptQfZSba91hynNfa9tLqrO6UaLKnS0UjEEp4w@mail.gmail.com Whole thread Raw |
| Responses |
Re: PL/pgSQL techniques better than bash for dynamic DO?
|
| List | pgsql-general |
PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now.
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}
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
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
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} = %',
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 \$\$;
"
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
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 = %',
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 $$;
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)?
pgsql-general by date: