Re: PL/pgSQL techniques better than bash for dynamic DO?

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

út 9. 4. 2024 v 18:33 odesílatel Ron Johnson <ronljohnsonjr@xxxxxxxxx> napsal:

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

you can pass values by GUC instead

pavel@nemesis:~$ psql -v var="AHOJ"
Assertions: on
psql (17devel)
Type "help" for help.

(2024-04-09 19:07:55) postgres=# select set_config('my.var', :'var', false);
┌────────────┐
│ set_config │
╞════════════╡
│ AHOJ       │
└────────────┘
(1 row)

(2024-04-09 19:08:46) postgres=# do $$
postgres$# declare myvar varchar default current_setting('my.var');
postgres$# begin
postgres$#   raise notice '%', myvar;
postgres$# end;
postgres$# $$;
NOTICE:  AHOJ
DO

Regards

Pavel