Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ruF0B-003ouE-KD for pgsql-general@arkaria.postgresql.org; Tue, 09 Apr 2024 17:11:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ruF0A-008InU-Ny for pgsql-general@arkaria.postgresql.org; Tue, 09 Apr 2024 17:11:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ruF0A-008InL-96 for pgsql-general@lists.postgresql.org; Tue, 09 Apr 2024 17:11:18 +0000 Received: from mail-yw1-x1131.google.com ([2607:f8b0:4864:20::1131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ruF07-0022ip-Q7 for pgsql-general@postgresql.org; Tue, 09 Apr 2024 17:11:17 +0000 Received: by mail-yw1-x1131.google.com with SMTP id 00721157ae682-61500da846fso47072267b3.1 for ; Tue, 09 Apr 2024 10:11:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712682675; x=1713287475; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=pJ0iPPhJsfZ/3NO+HD/fzqsuQKqM6DzoBSzZn6ZmhRM=; b=C9JGuQ2u5fX93LsMBfo535KJEaIVkrBU7NnSIt5sjyNQFojgC1HGDkSBkIuU1wLwca WCY1G3N/Y2bOgnfhjvLVmlRCVUaBlUgBJH++kwad9f01Nes3DxHi3t8oMn6hyiEPYPf1 OJXV6LKWnb25jKCv5S0BMwgEZsNSLhGW0Vmx9oDy4lgAQdNYGDldtjr9Diij4eo8R/Pb Z3kAyJIx1dp0DhIfzZzWAZjaDW0gs75qC2JH+CZhE9maSI1hWqaLEqZaaxq0Wo4IjU+u yy5FhiReJDlgCpWNoooPwYVqBi+rvOhoae0cnIr4wRMi5R1Xy5Vb2S71NhsEDctB0oQq x/SA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712682675; x=1713287475; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=pJ0iPPhJsfZ/3NO+HD/fzqsuQKqM6DzoBSzZn6ZmhRM=; b=R8D3+N2vUgIL82WhufVkxdQczeDEQpgNbrx7N/5zMekzU7uDYywJDqWsY8rDucdJkN BW51g9chCJEtGFKP65TcD1dmTav3euKM4piIaZG5eLBejOHk2W7Kk3ucME6b1BkaiFZc pMWhQ+LDSLSFb6zBq4Wjchf+XZhEDBFCTk3JIDKqnW3mC6xnnqCqQhuE0tBUwbC7TKoj wfr1hJqbEodMV3LXMtbQUhxFms6sxUwl8QHm1uopUiK8Dd0uS0zmB2H8HVd7RXdm81V0 R/EcNuH7MqNPls650V17tfbP9+oDeERCYJeH/cFIMqWf4uOE4UHKTmntPAji2qMksy42 u0lw== X-Gm-Message-State: AOJu0Yz5sC5RCTTzgQqLARWRJRd4N6oNLYpqGytP/ec4dWzgjwaYnfj7 QWgZKDYsFw+nNoxDgRpRwxwR6qCcNJ3omFKx62k/cbLxsSTJ+ZevOw4CP46ffDUaqW944r9hW16 0opcSKb7v9ePUgX5pKjQ3bL0WiPs= X-Google-Smtp-Source: AGHT+IGZ4pdBn0OU5nJH9BOs7blhn+q4yxilS/yI97K+JEsb8G+gEmscylpVFyRp/GWONg0pT3OJ6+ySCf1jShP+SQQ= X-Received: by 2002:a81:4eca:0:b0:611:200c:9229 with SMTP id c193-20020a814eca000000b00611200c9229mr335131ywb.8.1712682675145; Tue, 09 Apr 2024 10:11:15 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Tue, 9 Apr 2024 19:10:38 +0200 Message-ID: Subject: Re: PL/pgSQL techniques better than bash for dynamic DO? To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000007713a10615ad02b4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007713a10615ad02b4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C3=BAt 9. 4. 2024 v 18:33 odes=C3=ADlatel Ron Johnson 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 d= o > I want to fight with the 3rd party app vendor, since it defeats the purpo= se > of FK constraints.) > > Here's the snippet of bash code: > local Schema=3D$1 > local Table=3D$2 > local Field=3D$3 > local DtCol=3D$4 # CURRENT_TIMESTAMP or CURRENT_DATE > local Thresh=3D$5 # example: '90 day' > local FQTable=3D${Schema}.${Table} > DeS=3D"DO \$\$ > DECLARE > delsum INTEGER =3D 0; > delcnt INTEGER; > skipsum integer =3D 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 =3D ROW_COUNT; > delsum =3D delsum + delcnt; EXCEPTION > WHEN others THEN > skipsum =3D skipsum + 1; > RAISE NOTICE ' Skipped ${FQTable} WHERE ${Table}_id = =3D > %; ${Field} =3D %', > 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 =3D 0; > delcnt INTEGER; > skipsum integer =3D 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 =3D ROW_COUNT; > delsum =3D delsum + delcnt; > EXCEPTION > WHEN others THEN > skipsum =3D skipsum + 1; > RAISE NOTICE ' Skipped tms.check WHERE check_id =3D %; > modified_on =3D %', > 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=3D"AHOJ" Assertions: on psql (17devel) Type "help" for help. (2024-04-09 19:07:55) postgres=3D# select set_config('my.var', :'var', fals= e); =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 set_config =E2=94=82 =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1 =E2=94=82 AHOJ =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 (1 row) (2024-04-09 19:08:46) postgres=3D# do $$ postgres$# declare myvar varchar default current_setting('my.var'); postgres$# begin postgres$# raise notice '%', myvar; postgres$# end; postgres$# $$; NOTICE: AHOJ DO Regards Pavel --0000000000007713a10615ad02b4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
=C3=BAt 9. 4. 2024 v=C2=A018:33 odes= =C3=ADlatel Ron Johnson <ronl= johnsonjr@gmail.com> napsal:
PG 9.6.11, if relevant, migrating= to PG 14 Real Soon Now.

I must purge the oldest X peri= od of records from 70 tables, every Sunday.=C2=A0 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.=C2=A0 T= his works well, except for Foreign Key constraints; carefully ordering the = file to purge records in the correct order eliminates most FK errors, but n= ot all.

Therefore, I created an anonymous DO state= ment to delete the "deletable" old records, while skipping the on= es that would fail from a FK constraint.=C2=A0 (Eventually, the records in = the FK table will get deleted, so eventually the records who's DELETE f= ailed will succeed in getting deleted.)

(NOTE: I c= annot 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.)

Here's the snippet of bas= h code:
=C2=A0 =C2=A0 local Schema=3D$1<= br>=C2=A0 =C2=A0 local Table=3D$2
=C2=A0 =C2=A0 local Field=3D$3
=C2= =A0 =C2=A0 local DtCol=3D$4 =C2=A0# CURRENT_TIMESTAMP or CURRENT_DATE
= =C2=A0 =C2=A0 local Thresh=3D$5 # example: '90 day'
=C2=A0 =C2= =A0 local FQTable=3D${Schema}.${Table}
=C2=A0 =C2=A0 DeS=3D"DO \$\$
DECLARE
=C2=A0 =C2=A0 del= sum INTEGER =3D 0;
=C2=A0 =C2=A0 delcnt INTEGER;
=C2=A0 =C2=A0 skipsu= m integer =3D 0;
=C2=A0 =C2=A0 cur_row CURSOR FOR
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 SELECT $Field, ${Table}_id=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 from ${FQTable}=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 where $Field < (= ${DtCol} - interval ${Thresh});
BEGIN
=C2=A0 =C2=A0 FOR arow IN cur_r= ow
=C2=A0 =C2=A0 LOOP
=C2=A0 =C2=A0 =C2=A0 =C2=A0 BEGIN
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DELETE FROM ${FQTable} WHERE CURRENT OF cur= _row;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GET DIAGNOSTICS delcnt = =3D ROW_COUNT;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 delsum =3D dels= um + delcnt;=C2=A0 =C2=A0 =C2= =A0 =C2=A0 EXCEPTION
<= font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN othe= rs THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 skipsum= =3D skipsum + 1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 RAISE NOTICE ' =C2=A0 =C2=A0Skipped ${FQTable} WHERE ${Table}_id = =3D %; ${Field} =3D %',=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 arow= .${Table}_id, arow.${Field};
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END;
=C2=A0 = =C2=A0 END LOOP;
=C2=A0 =C2=A0 RAISE NOTICE 'Sum of deleted rows: %&= #39;, delsum;
=C2=A0 =C2=A0 RAISE NOTICE 'Sum of skipped rows: %'= ;, skipsum;
END \$\$;
"

It g= enerates the perfectly functional SQL:
D= O $$
DECLARE
=C2=A0 =C2=A0 delsum INTEGER =3D 0;
=C2=A0 =C2=A0 del= cnt INTEGER;
=C2=A0 =C2=A0 skipsum integer =3D 0;
=C2=A0 =C2=A0 cur_r= ow CURSOR FOR
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT modified_on, check_id= =C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 from tms.check=C2=A0
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 where modified_on < (CURRENT_TIMESTAMP - interval &= #39;90 day');
BEGIN
=C2=A0 =C2=A0 FOR arow IN cur_row
=C2=A0 = =C2=A0 LOOP
=C2=A0 =C2=A0 =C2=A0 =C2=A0 BEGIN
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 DELETE FROM tms.check WHERE CURRENT OF cur_row;
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GET DIAGNOSTICS delcnt =3D ROW_COUNT= ;
=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
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 WHEN others THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 skipsum =3D skipsum + 1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 RAISE NOTICE ' =C2=A0 =C2=A0Skipped tms.check = WHERE check_id =3D %; modified_on =3D %',=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 arow.check_id, arow.modified_on;
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 END;
=C2=A0 =C2=A0 END LOOP;
=C2=A0 =C2=A0 RAISE NOTICE 'Sum = of deleted rows: %', delsum;
=C2=A0 =C2=A0 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=3D"AHOJ"
= Assertions: on
psql (17devel)
Type "help" for help.

= (2024-04-09 19:07:55) postgres=3D# select set_config('my.var', := 9;var', false);
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90=
=E2=94=82 set_config =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=A1
=E2=94=82 AHOJ =C2=A0 =C2=A0 =C2=A0 =E2=94=82
=E2=94= =94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
(1 row)

(2024-04-09= 19:08:46) postgres=3D# do $$
postgres$# declare myvar varchar default c= urrent_setting('my.var');
postgres$# begin
postgres$# =C2=A0 = raise notice '%', myvar;
postgres$# end;
postgres$# $$;
NO= TICE: =C2=A0AHOJ
DO

Regards

Pavel
--0000000000007713a10615ad02b4--