MIME-Version: 1.0
References: <CAEzWdqfUnGNn1jpgVFr9+xCr5A-Pp9KjdxY=vv8E8iqqQ9OuFw@mail.gmail.com>
In-Reply-To: <CAEzWdqfUnGNn1jpgVFr9+xCr5A-Pp9KjdxY=vv8E8iqqQ9OuFw@mail.gmail.com>
From: Muhammad Ikram <mmikram@gmail.com>
Date: Tue, 2 Jul 2024 12:13:21 +0500
Message-ID: <CAGeimVrmuHGCpg85oZuF0Y0DFDngEeYuceH8d3+Oe=Bu9-TrKA@mail.gmail.com>
Subject: Re: Question on partman extension while relation exist
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000098e153061c3e73cd"
Archived-At: <https://www.postgresql.org/message-id/CAGeimVrmuHGCpg85oZuF0Y0DFDngEeYuceH8d3%2BOe%3DBu9-TrKA%40mail.gmail.com>
Precedence: bulk

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

Hi Yudhi,

I think disabling foreign keys before maintenance will help.

Regards,
Muhammad Ikram
Bitnine global

On Tue, Jul 2, 2024 at 11:41=E2=80=AFAM yudhi s <learnerdatabase99@gmail.co=
m> wrote:

> Hello All,
> In postgres we are seeing issues during automatic partition maintenance
> using pg_partman extension. So basically it automatically creates one new
> partition and drops one historical partition each day based on the set
> retention period in part_config. We just call it like
> partman.run_maintenance_proc('table_name');
>
> While there exists foreign key relationships between the partitioned
> tables, Mainly during dropping the parent partitions it takes a lot of
> time, as it validates every child table partitions record and also is
> taking lock longer. Ideally it should check only the respective parent
> partition, but it's not doing that because the foreign key is defined in
> table level rather than partition level. So we are planning to create the
> foreign keys on the partition level but not at table level.
>
> And we were thinking of doing it dynamically by having an "event trigger"
> which will fire on "create statement" i.e while the "create new partition=
"
> statement will be triggered by the Pg_partman. It will try to also create
> the foreign key constraints on the new child partition referring to the
> respective parent partition during the same time. So that things will be
> automated.
>
> But now we are stuck in one scenario , say for example if we execute the
> pg_partman for the parent table first then it will create the new partiti=
on
> independently which is fine, but when it will try to drop the historical
> partition, it will complain stating the child partition already exists.
>
> On the other hand,
>
> If we run the pg_partman for the child table first, then it will drop the
> historical child partition without any issue , however it will throw an
> error while creating the foreign key , as because the respective parent
> partition has not yet been created.
>
> Need advice, how we should handle this scenario. Basically in which order
> we should call the "pg_partman.run_maintenance_proc" for the parent and
> child tables?
>


--=20
Muhammad Ikram

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

<div><div dir=3D"ltr">Hi Yudhi,<div><br></div><div dir=3D"auto">I think dis=
abling foreign keys before maintenance will help.</div><div dir=3D"auto"><b=
r></div><div dir=3D"auto">Regards,</div><div dir=3D"auto">Muhammad Ikram=C2=
=A0</div><div dir=3D"auto">Bitnine global</div></div></div><div><br><div cl=
ass=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Jul 2, 20=
24 at 11:41=E2=80=AFAM yudhi s &lt;<a href=3D"mailto:learnerdatabase99@gmai=
l.com" target=3D"_blank">learnerdatabase99@gmail.com</a>&gt; wrote:<br></di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello =
All,<div>In postgres we are seeing issues during automatic partition mainte=
nance using pg_partman extension. So basically it automatically creates one=
 new partition and drops one historical partition each day based on the set=
 retention period in part_config. We just call it like partman.run_maintena=
nce_proc(&#39;table_name&#39;);<br><br>While there exists foreign key relat=
ionships between the partitioned tables, Mainly during dropping the parent =
partitions it takes a lot of time, as it validates every child table partit=
ions record and also is taking lock longer. Ideally it should check only th=
e respective parent partition, but it&#39;s not doing that because the fore=
ign key is defined in table level rather than partition level. So we are pl=
anning to create the foreign keys on the partition level but not at table l=
evel. <br><br>And we were thinking of doing it dynamically by having an &qu=
ot;event trigger&quot; which will fire on &quot;create statement&quot; i.e =
while the &quot;create new partition&quot; statement will be triggered by t=
he Pg_partman. It will try to also create the foreign key constraints on th=
e new child partition referring to the respective parent partition during t=
he same time. So that things will be automated.<br><br>But now we are stuck=
 in one scenario , say for example if we execute the pg_partman for the par=
ent table first then it will create the new partition independently which i=
s fine, but when it will try to drop the historical partition, it will comp=
lain stating the child partition already exists. <br><br>On the other hand,=
<br><br>If we run the pg_partman for the child table first, then it will dr=
op the historical child partition without any issue , however it will throw=
 an error while creating the foreign key , as because the respective parent=
 partition has not yet been created.<br><br>Need advice, how we should hand=
le this scenario. Basically in which order we should call the &quot;pg_part=
man.run_maintenance_proc&quot; for the parent and child tables?<br></div></=
div>
</blockquote></div><br clear=3D"all"><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr"><div>Muhammad Ikram<br><br></div></div></div>
</div>

--00000000000098e153061c3e73cd--