public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bolaji Wahab <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: Clarification of behaviour when dropping partitions
Date: Wed, 4 Dec 2024 23:00:23 +0100
Message-ID: <CA+f_mLO=qNkHCumSfbcLz7Fb7DBoByNLLby3NDUod_TuHn1ZeA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+f_mLPhCa_G3nRrB4=e3trqCSQ88x56ZvfeweH-AhwS77xEGg@mail.gmail.com>
	<[email protected]>

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 protected]>
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
>

view thread (3+ 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]
  Subject: Re: Clarification of behaviour when dropping partitions
  In-Reply-To: <CA+f_mLO=qNkHCumSfbcLz7Fb7DBoByNLLby3NDUod_TuHn1ZeA@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