public inbox for [email protected]  

help / color / mirror / Atom feed

From: Muhammad Ikram <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Question on partman extension while relation exist
Date: Tue, 2 Jul 2024 12:13:21 +0500
Message-ID: <CAGeimVrmuHGCpg85oZuF0Y0DFDngEeYuceH8d3+Oe=Bu9-TrKA@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqfUnGNn1jpgVFr9+xCr5A-Pp9KjdxY=vv8E8iqqQ9OuFw@mail.gmail.com>
References: <CAEzWdqfUnGNn1jpgVFr9+xCr5A-Pp9KjdxY=vv8E8iqqQ9OuFw@mail.gmail.com>

Hi Yudhi,

I think disabling foreign keys before maintenance will help.

Regards,
Muhammad Ikram
Bitnine global

On Tue, Jul 2, 2024 at 11:41 AM yudhi s <[email protected]> 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 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?
>


-- 
Muhammad Ikram

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Question on partman extension while relation exist
  In-Reply-To: <CAGeimVrmuHGCpg85oZuF0Y0DFDngEeYuceH8d3+Oe=Bu9-TrKA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox