Re: Clarification of behaviour when dropping partitions

From: Bolaji Wahab <bolajiwahab23(at)gmail(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Clarification of behaviour when dropping partitions
Date: 2024-12-04 22:00:23
Message-ID: CA+f_mLO=qNkHCumSfbcLz7Fb7DBoByNLLby3NDUod_TuHn1ZeA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 <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.
> >
> > 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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2024-12-05 01:38:34 Re: Clarification of behaviour when dropping partitions
Previous Message Laurenz Albe 2024-12-04 17:20:55 Re: Clarification of behaviour when dropping partitions