MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 9 Apr 2024 12:33:27 -0400
Message-ID: <CANzqJaCRQWegYptQfZSba91hynNfa9tLqrO6UaLKnS0UjEEp4w@mail.gmail.com>
Subject: PL/pgSQL techniques better than bash for dynamic DO?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f0660b0615ac7b6c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCRQWegYptQfZSba91hynNfa9tLqrO6UaLKnS0UjEEp4w%40mail.gmail.com>
Precedence: bulk

--000000000000f0660b0615ac7b6c
Content-Type: text/plain; charset="UTF-8"

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

--000000000000f0660b0615ac7b6c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>PG 9.6.11, if relevant, migrating to PG 14 Real Soon =
Now.</div><div><br></div>I must purge the oldest X period of records from 7=
0 tables, every Sunday.=C2=A0 The field name, interval (X days or months) a=
nd date (CURRENT_DATE or CURRENT_TIMESTAMP) varies for each table.<div>Thus=
, I put all the relevant data in a tab-separated value file, and use bash t=
o read through it, purging one table at a time.=C2=A0 This works well, exce=
pt for Foreign Key constraints; carefully ordering the file to purge record=
s in the correct order eliminates most FK errors, but not all.</div><div><b=
r></div><div>Therefore, I created an anonymous DO statement to delete the &=
quot;deletable&quot; old records, while skipping the ones that would fail f=
rom a FK constraint.=C2=A0 (Eventually, the records in the FK table will ge=
t deleted, so eventually the records who&#39;s DELETE failed will succeed i=
n getting deleted.)</div><div><br></div><div>(NOTE: I cannot change the FK =
constraints to ON DELETE CASCADE, and nor do I want to fight with=C2=A0the=
=C2=A03rd party app vendor, since it defeats the purpose of FK constraints.=
)</div><div><br></div><div>Here&#39;s the snippet of bash code:</div><div><=
font face=3D"monospace">=C2=A0 =C2=A0 local Schema=3D$1<br>=C2=A0 =C2=A0 lo=
cal Table=3D$2<br>=C2=A0 =C2=A0 local Field=3D$3<br>=C2=A0 =C2=A0 local DtC=
ol=3D$4 =C2=A0# CURRENT_TIMESTAMP or CURRENT_DATE<br>=C2=A0 =C2=A0 local Th=
resh=3D$5 # example: &#39;90 day&#39;<br>=C2=A0 =C2=A0 local FQTable=3D${Sc=
hema}.${Table}<br></font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 =
DeS=3D&quot;DO \$\$<br>DECLARE<br>=C2=A0 =C2=A0 delsum INTEGER =3D 0;<br>=
=C2=A0 =C2=A0 delcnt INTEGER;<br>=C2=A0 =C2=A0 skipsum integer =3D 0;<br>=
=C2=A0 =C2=A0 cur_row CURSOR FOR<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT $Fie=
ld, ${Table}_id=C2=A0</font></div><div><font face=3D"monospace">=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 from ${FQTable}=C2=A0</font></div><div><font face=3D"mono=
space">=C2=A0 =C2=A0 =C2=A0 =C2=A0 where $Field &lt; (${DtCol} - interval $=
{Thresh});<br>BEGIN<br>=C2=A0 =C2=A0 FOR arow IN cur_row<br>=C2=A0 =C2=A0 L=
OOP<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 BEGIN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 DELETE FROM ${FQTable} WHERE CURRENT OF cur_row;<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GET DIAGNOSTICS delcnt =3D ROW_COUNT;<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 delsum =3D delsum + delcnt;</font=
><span style=3D"font-family:monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXCEPTIO=
N</span><font face=3D"monospace"><br></font></div><div><font face=3D"monosp=
ace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN others THEN <br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 skipsum =3D skipsum + 1;<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE NOTICE &#39=
; =C2=A0 =C2=A0Skipped ${FQTable} WHERE ${Table}_id =3D %; ${Field} =3D %&#=
39;,=C2=A0</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 arow.${Table}_id, arow.${F=
ield};<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 END;<br>=C2=A0 =C2=A0 END LOOP;<br>=
=C2=A0 =C2=A0 RAISE NOTICE &#39;Sum of deleted rows: %&#39;, delsum;<br>=C2=
=A0 =C2=A0 RAISE NOTICE &#39;Sum of skipped rows: %&#39;, skipsum;<br>END \=
$\$;<br>&quot;</font><br></div><div><br></div><div>It generates the perfect=
ly functional SQL:</div><div><font face=3D"monospace">DO $$<br>DECLARE<br>=
=C2=A0 =C2=A0 delsum INTEGER =3D 0;<br>=C2=A0 =C2=A0 delcnt INTEGER;<br>=C2=
=A0 =C2=A0 skipsum integer =3D 0;<br>=C2=A0 =C2=A0 cur_row CURSOR FOR<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT modified_on, check_id=C2=A0</font></div>=
<div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 from tms.check=C2=
=A0</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 w=
here modified_on &lt; (CURRENT_TIMESTAMP - interval &#39;90 day&#39;);<br>B=
EGIN<br>=C2=A0 =C2=A0 FOR arow IN cur_row<br>=C2=A0 =C2=A0 LOOP<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 BEGIN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DEL=
ETE FROM tms.check WHERE CURRENT OF cur_row;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 GET DIAGNOSTICS delcnt =3D ROW_COUNT;<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 delsum =3D delsum + delcnt;<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 EXCEPTION<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN othe=
rs THEN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 skipsum =
=3D skipsum + 1;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 RAISE NOTICE &#39; =C2=A0 =C2=A0Skipped tms.check WHERE check_id =3D %; mo=
dified_on =3D %&#39;,=C2=A0</font></div><div><font face=3D"monospace">=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 arow.che=
ck_id, arow.modified_on;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 END;<br>=C2=A0 =C2=
=A0 END LOOP;<br>=C2=A0 =C2=A0 RAISE NOTICE &#39;Sum of deleted rows: %&#39=
;, delsum;<br>=C2=A0 =C2=A0 RAISE NOTICE &#39;Sum of skipped rows: %&#39;, =
skipsum;<br>END $$;<br></font></div><div><br></div><div>Can I do this bette=
r in PL/pgSQL with dynamic SQL (that doesn&#39;t get hairy with nested quot=
es, etc)?</div></div>

--000000000000f0660b0615ac7b6c--