Re: BUG #18628: Race condition during attach/detach partition breaks constraints of partition having foreign key

Alexander Lakhin <exclusion(at)gmail(dot)com> 于2024年9月24日周二 19:00写道：

> 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?
>
>
I haven't looked at it in detail, but I have a feeling that this issue has
a very close relation to the issue in [1].
Now we don't do well when the partition table references itself.

[1]
https://www.postgresql.org/message-id/flat/CAECtzeWHCA%2B6tTcm2Oh2%2Bg7fURUJpLZb-%3DpRXgeWJ-Pi%2BVU%3D_w%40mail.gmail.com

--
Thanks,
Tender Wang
https://www.openpie.com/