Re: Clarification of behaviour when dropping partitions

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Bolaji Wahab <bolajiwahab23(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Clarification of behaviour when dropping partitions
Date: 2024-12-05 01:38:34
Message-ID: f68465771b664815c47d50bad814db2ba4e4e9ba.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> > > I have these two partitioned tables, with referential integrity. The tables
> > > are structured in such a way that we have 1 to 1 mapping between their
> > > partitions. This is achieved with a foreign key.
> >
> > I recommend that you don't create the foreign key constraint between the
> > partitioned tables, but between the individual partitions.
> >
> > That will make detaching and dropping partitions easier, and you will have
> > the same integrity guarantees.
>
> Yes, this is what I have done.
> But the whole point of declaring the foreign key constraint on the partitioned
> table is to have it automatically created on subsequent/future partitions.

Sure, but then you have to accept the disadvantage that it becomes more
difficult to detach partitions. I think it is less pain to create the
constraint on the partition level.

Yours,
Laurenz Albe

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gerhard Wiesinger 2024-12-05 06:16:45 Updated Fedora 40 and Fedora 41 RPM packages of Pgpool-II 4.5.5 in the repo
Previous Message Bolaji Wahab 2024-12-04 22:00:23 Re: Clarification of behaviour when dropping partitions