MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Tue, 2 Jul 2024 12:10:44 +0530
Message-ID: <CAEzWdqfUnGNn1jpgVFr9+xCr5A-Pp9KjdxY=vv8E8iqqQ9OuFw@mail.gmail.com>
Subject: Question on partman extension while relation exist
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5041d061c3dfe7d"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqfUnGNn1jpgVFr9%2BxCr5A-Pp9KjdxY%3Dvv8E8iqqQ9OuFw%40mail.gmail.com>
Precedence: bulk

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

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

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

<div dir=3D"ltr">Hello All,<div>In postgres we are seeing issues during aut=
omatic partition maintenance using pg_partman extension. So basically it au=
tomatically creates one new partition and drops one historical partition ea=
ch day based on the set retention period in part_config. We just call it li=
ke partman.run_maintenance_proc(&#39;table_name&#39;);<br><br>While there e=
xists foreign key relationships between the partitioned tables, Mainly duri=
ng dropping the parent partitions it takes a lot of time, as it validates e=
very child table partitions record and also is taking lock longer. Ideally =
it should check only the respective parent partition, but it&#39;s not doin=
g that because the foreign key is defined in table level rather than partit=
ion level. So we are planning to create the foreign keys on the partition l=
evel but not at table level. <br><br>And we were thinking of doing it dynam=
ically by having an &quot;event trigger&quot; which will fire on &quot;crea=
te statement&quot; i.e while the &quot;create new partition&quot; statement=
 will be triggered by the Pg_partman. It will try to also create the foreig=
n key constraints on the new child partition referring to the respective pa=
rent partition during the 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 parent table first then it will create the new partitio=
n independently which is fine, but when it will try to drop the historical =
partition, it will complain 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 drop the historical child partition without any issue =
, however it will throw an error while creating the foreign key , as becaus=
e the respective parent partition has not yet been created.<br><br>Need adv=
ice, how we should handle this scenario. Basically in which order we should=
 call the &quot;pg_partman.run_maintenance_proc&quot; for the parent and ch=
ild tables?<br></div></div>

--000000000000f5041d061c3dfe7d--