MIME-Version: 1.0
References: <CAEzWdqcimp5dnNOavaSkMCOKW_FVsKC2101g=dFsyjQ-9dA3uw@mail.gmail.com>
 <CAKFQuwa+jpZ-pucWc92OCYcwCnj7C_POg8k=5BvbPZyL97R-Jw@mail.gmail.com>
 <CAEzWdqfqr9e3OpFd5Nhqha3Ggm=+UJdWkgvo7dpAa3W99S2g5Q@mail.gmail.com>
 <CAKFQuwYu8w7BMX_9xEP1t5ULT7pV-qO1Yotn1qtdMuEpWCqhFg@mail.gmail.com>
 <2720974.1713710606@sss.pgh.pa.us> <CAEzWdqcs8OYtK9dWbev986FfNO0i9rGDvChZTti2xWL8HuXnbg@mail.gmail.com>
 <CAKFQuwawNxB_XEGhGiTmGT6efj8jB+g=a++3G9msmh2_R=OVoQ@mail.gmail.com>
In-Reply-To: <CAKFQuwawNxB_XEGhGiTmGT6efj8jB+g=a++3G9msmh2_R=OVoQ@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Mon, 22 Apr 2024 00:27:44 +0530
Message-ID: <CAEzWdqffAd3_hFjCdkpDFSpYQ6OxjRQzv4ZCnJQZwnCzvbq+-g@mail.gmail.com>
Subject: Re: error in trigger creation
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001508da06169fe6ed"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqffAd3_hFjCdkpDFSpYQ6OxjRQzv4ZCnJQZwnCzvbq%2B-g%40mail.gmail.com>
Precedence: bulk

--0000000000001508da06169fe6ed
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Apr 22, 2024 at 12:02=E2=80=AFAM David G. Johnston <
david.g.johnston@gmail.com> wrote:

>
> I suggest you share a script that demonstrates exactly what you are tryin=
g
> to accomplish.  Which event triggers you need to create from the
> application and what the functions those triggers call do.
>
>
We are using pg_partman for automatic partition maintenance however as we
have foreign keys created on the tables, so the partition drop from parent
is taking longer as it scans all the partitions of the child table and also
locks the full child table for that duration(even SELECT query not allowed
during that period). So we are thinking of creating foreign keys on
partitions rather than on tables however there is no direct option for that
to happen through pg_partman.

So we are thinking of first creating the table without any foreign keys and
creating the partitions using pg_partman, then create the below event
trigger which will add the foreign key to the new partitions for all new
future partitions. And we are planning to create such an event trigger for
all such child tables that are partitioned and having FK's.

CREATE OR REPLACE FUNCTION add_partition_foreign_key()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
partition_table TEXT;
parent_table TEXT;
partition_name TEXT;
BEGIN
IF TG_TAG =3D 'CREATE TABLE' THEN
partition_table :=3D TG_TABLE_NAME;
parent_table :=3D 'parent_table_' || to_char(NEW.partition_key, 'YYYY_MM_DD=
');

EXECUTE format('ALTER TABLE %I ADD CONSTRAINT fk_%I_%I FOREIGN KEY
(partition_key, id) REFERENCES %I (partition_key, id)', partition_table,
partition_table, parent_table, parent_table);
END IF;
END;
$$;

CREATE EVENT TRIGGER add_partition_foreign_key_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION add_partition_foreign_key();

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Mon, Apr 22, 2024 at 12:02=E2=80=AFAM =
David G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g=
.johnston@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div style=3D"font-fam=
ily:arial,helvetica,sans-serif"><br></div></div><div class=3D"gmail_quote">=
<div style=3D"font-family:arial,helvetica,sans-serif">I suggest you share a=
 script that demonstrates exactly what you are trying to accomplish.=C2=A0 =
Which event triggers you need to create from the application and what the f=
unctions those triggers call do.</div><div style=3D"font-family:arial,helve=
tica,sans-serif"><br></div></div></div></blockquote><div><br></div><div>We =
are using=C2=A0pg_partman for automatic partition=C2=A0maintenance however =
as we have foreign keys created on the tables, so the partition drop from p=
arent is taking longer as it scans all the partitions of the child table an=
d also locks the=C2=A0full child table for that duration(even SELECT query =
not allowed during that period). So we are thinking of creating foreign key=
s on partitions rather than on tables however there is no direct option for=
 that to happen through pg_partman.</div><div><br></div><div>So we are thin=
king of first creating the table without any foreign keys and creating=C2=
=A0the partitions using pg_partman, then create the below event trigger whi=
ch will add the foreign key to the new partitions for all new future partit=
ions. And we are planning to create=C2=A0such an event trigger for all such=
 child tables that are partitioned and having FK&#39;s.<br></div><div><br><=
/div><div><font face=3D"monospace" size=3D"1">CREATE OR REPLACE FUNCTION ad=
d_partition_foreign_key()<br>RETURNS event_trigger<br>LANGUAGE plpgsql<br>A=
S $$<br>DECLARE<br>partition_table TEXT;<br>parent_table TEXT;<br>partition=
_name TEXT;<br>BEGIN<br>IF TG_TAG =3D &#39;CREATE TABLE&#39; THEN<br>partit=
ion_table :=3D TG_TABLE_NAME;<br>parent_table :=3D &#39;parent_table_&#39; =
|| to_char(NEW.partition_key, &#39;YYYY_MM_DD&#39;);<br><br>EXECUTE format(=
&#39;ALTER TABLE %I ADD CONSTRAINT fk_%I_%I FOREIGN KEY (partition_key, id)=
 REFERENCES %I (partition_key, id)&#39;, partition_table, partition_table, =
parent_table, parent_table);<br>END IF;<br>END;<br>$$;<br><br>CREATE EVENT =
TRIGGER add_partition_foreign_key_trigger<br>ON ddl_command_end<br>WHEN TAG=
 IN (&#39;CREATE TABLE&#39;)<br>EXECUTE FUNCTION add_partition_foreign_key(=
);</font><br></div></div></div>

--0000000000001508da06169fe6ed--