From: Alexander Lakhin <exclusion@gmail.com>
To: pgsql-bugs@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: BUG #18628: Race condition during attach/detach partition breaks constraints of partition having foreign key
Date: Tue, 24 Sep 2024 14:00:00 +0300
Message-ID: <2db29417-eac1-35b6-94e3-a9c0bdb9c035@gmail.com> (raw)
In-Reply-To: <18628-4f11be4e0b1c9002@postgresql.org>
References: <18628-4f11be4e0b1c9002@postgresql.org>
22.09.2024 17:00, PG Bug reporting form wrote:
> The following script:
> ...
> ends up with:
> ERROR: could not find ON INSERT check triggers of foreign key constraint
> 16409
I'm sorry, I' was too hasty to untangle those queries. Now I see that
this error can be produced with this serial script:
CREATE TABLE pt (id int PRIMARY KEY, rid int,
FOREIGN KEY (rid) REFERENCES pt(id))
PARTITION BY LIST (id);
CREATE TABLE p1 PARTITION OF pt FOR VALUES IN (1);
ALTER TABLE pt DETACH PARTITION p1;
-- p1 gets a constraint:
-- "pt_rid_fkey" FOREIGN KEY (rid) REFERENCES pt(id)
CREATE TABLE p2 PARTITION OF pt FOR VALUES IN (2);
-- CloneFkReferenced() -> addFkRecurseReferenced() clones the above
-- constraint and creates another one:
-- conname: p1_rid_fkey, conrelid: id of p1, confrelid: id of p2,
-- conparentid: id of the above constraint
-- (\d+ p2 doesn't show this constraint due to conparentid != 0)
ALTER TABLE pt ATTACH PARTITION p1 FOR VALUES IN (1);
-- p1 attached with no constraints added or removed
ALTER TABLE pt DETACH PARTITION p1;
-- DetachPartitionFinalize() gets the p1_rid_fkey constraint with
-- RelationGetFKeyList(partRel) and then fails to find INSERT trigger for
-- it, because the constraint has only DELETE and UPDATE triggers linked
-- to the p2 relation.
ERROR: could not find ON INSERT check triggers of foreign key constraint 16410
Alvaro, could you please take a look at this?
Best regards,
Alexander
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: pgsql-bugs@postgresql.org
Cc: exclusion@gmail.com, alvherre@alvh.no-ip.org
Subject: Re: BUG #18628: Race condition during attach/detach partition breaks constraints of partition having foreign key
In-Reply-To: <2db29417-eac1-35b6-94e3-a9c0bdb9c035@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox