Subject: Re: Clarification of behaviour when dropping partitions

From: Bolaji Wahab <[email-masked]>
Date: Wed, 4 Dec 2024 23:00:23 +0100

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.

On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe <[email-masked]>
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.
> >
> > CREATE TABLE parent (
> >     partition_date date NOT NULL,
> >     id uuid NOT NULL,
> >     external_transaction_id uuid NOT NULL,
> >
> >     CONSTRAINT parent_pkey
> >         PRIMARY KEY (id, partition_date),
> >
> >     CONSTRAINT parent_external_transaction_id_key
> >         UNIQUE (external_transaction_id, partition_date)
> > ) PARTITION BY RANGE (partition_date);
> >
> > CREATE TABLE parent_2024_12_01
> >     PARTITION OF public.parent
> >     FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
> >
> > CREATE TABLE parent_2024_12_02
> >     PARTITION OF public.parent
> >     FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
> >
> > [...]
> >
> > CREATE TABLE child (
> >     partition_date date NOT NULL,
> >     transaction_id uuid NOT NULL,
> >     key            text NOT NULL,
> >     value          text NOT NULL,
> >
> >     CONSTRAINT child_pkey
> >         PRIMARY KEY (transaction_id, key, partition_date),
> >
> >     CONSTRAINT child_transaction_id_fkey
> >         FOREIGN KEY (transaction_id, partition_date)
> >         REFERENCES parent (id, partition_date)
> > ) PARTITION BY RANGE (partition_date);
> >
> > CREATE TABLE child_2024_12_01
> >     PARTITION OF child
> >     FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
> >
> > CREATE TABLE child_2024_12_02
> >     PARTITION OF public.child
> >     FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
>
> 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.
>
> Yours,
> Laurenz Albe
>

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 05 Dec 2024 02:38:34 +0100

On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe <[email-masked]> wrote:
> > > I have these two partitioned tables, with referential integrity. The tables
> 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

---

From: Bolaji Wahab <[email-masked]>
Date: Wed, 11 Dec 2024 07:46:11 +0100

Yes, right. I wonder if the team sees an opportunity for some optimization
here, supporting such a scenario efficiently. I can't think of any
downsides to it but I may be missing something.

Cheers.

On Thu, Dec 5, 2024 at 2:38 AM Laurenz Albe <[email-masked]>
wrote:

> partitioned