public inbox for [email protected]
help / color / mirror / Atom feedRe: Clarification of behaviour when dropping partitions
3+ messages / 2 participants
[nested] [flat]
* Re: Clarification of behaviour when dropping partitions
@ 2024-12-04 22:00 Bolaji Wahab <[email protected]>
2024-12-05 01:38 ` Re: Clarification of behaviour when dropping partitions Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Bolaji Wahab @ 2024-12-04 22:00 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: 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 <[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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clarification of behaviour when dropping partitions
2024-12-04 22:00 Re: Clarification of behaviour when dropping partitions Bolaji Wahab <[email protected]>
@ 2024-12-05 01:38 ` Laurenz Albe <[email protected]>
2024-12-11 06:46 ` Re: Clarification of behaviour when dropping partitions Bolaji Wahab <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2024-12-05 01:38 UTC (permalink / raw)
To: Bolaji Wahab <[email protected]>; +Cc: 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 <[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.
> >
> > 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clarification of behaviour when dropping partitions
2024-12-04 22:00 Re: Clarification of behaviour when dropping partitions Bolaji Wahab <[email protected]>
2024-12-05 01:38 ` Re: Clarification of behaviour when dropping partitions Laurenz Albe <[email protected]>
@ 2024-12-11 06:46 ` Bolaji Wahab <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Bolaji Wahab @ 2024-12-11 06:46 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general
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 protected]>
wrote:
> On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> > 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.
> > >
> > > 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-12-11 06:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-04 22:00 Re: Clarification of behaviour when dropping partitions Bolaji Wahab <[email protected]>
2024-12-05 01:38 ` Laurenz Albe <[email protected]>
2024-12-11 06:46 ` Bolaji Wahab <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox