public inbox for [email protected]help / color / mirror / Atom feed
Re: 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]> 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-05 01:38 Laurenz Albe <[email protected]> parent: 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-11 06:46 Bolaji Wahab <[email protected]> parent: Laurenz Albe <[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