public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alvaro Herrera <[email protected]>
To: Tom Lane <[email protected]>
Cc: Tender Wang <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Jehan-Guillaume de Rorthais <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Guillaume Lelarge <[email protected]>
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Date: Wed, 6 Nov 2024 18:53:07 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
On 2024-Nov-06, Alvaro Herrera wrote:
> Perhaps one more task for me is to figure out a way to get a list of all
> the constraints that are broken because of this ... let me see if I can
> figure that out.
It's gotta be something like this,
SELECT conrelid::regclass AS "constrained table",
conname as constraint, confrelid::regclass AS "references"
FROM pg_constraint
WHERE contype = 'f' and conparentid = 0 AND
(SELECT count(*) FROM pg_constraint p2 WHERE conparentid = pg_constraint.oid) <>
(SELECT count(*)
FROM pg_inherits
WHERE inhparent = pg_constraint.conrelid OR inhparent = pg_constraint.confrelid);
Essentially, top-level constraints should have as many children
constraint as direct partitions each partitioned table has. Ideally
here you'd get an empty set, but you won't if the DETACH problem has
occurred. I'll test this further later or maybe tomorrow as time
allows.
A quick test rig for this is:
create table pk (a int primary key) partition by list (a);
create table pk1 partition of pk for values in (1);
create table pk2367 partition of pk for values in (2, 3, 6, 7) partition by list (a);
create table pk67 partition of pk2367 for values in (6, 7) partition by list (a);
create table pk2 partition of pk2367 for values in (2);
create table pk3 partition of pk2367 for values in (3);
create table pk6 partition of pk67 for values in (6);
create table pk7 partition of pk67 for values in (7);
create table pk45 partition of pk for values in (4, 5) partition by list (a);
create table pk4 partition of pk45 for values in (4);
create table pk5 partition of pk45 for values in (5);
create table fk (a int references pk) partition by list (a);
create table fk1 partition of fk for values in (1);
create table fk2367 partition of fk for values in (2, 3, 6, 7) partition by list (a);
create table fk67 partition of fk2367 for values in (6, 7) partition by list (a);
create table fk2 partition of fk2367 for values in (2);
create table fk3 partition of fk2367 for values in (3);
create table fk6 partition of fk67 for values in (6);
create table fk7 partition of fk67 for values in (7);
create table fk45 partition of fk for values in (4, 5) partition by list (a);
create table fk4 partition of fk45 for values in (4);
create table fk5 partition of fk45 for values in (5);
alter table fk detach partition fk2367;
Before the fix, you get
constrained table │ constraint │ references
───────────────────┼────────────┼────────────
fk2367 │ fk_a_fkey │ pk
(1 fila)
which means you need to
ALTER TABLE fk2367 DROP CONSTRAINT fk_a_fkey;
and then put it back. Maybe it'd be better to have the query emit the
commands to drop and reconstruct the FK?
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
view thread (6+ 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], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
In-Reply-To: <[email protected]>
* 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