Thread: BUG #18741: Detaching a partition referencing a partitioned table fails with a trigger-related error
BUG #18741: Detaching a partition referencing a partitioned table fails with a trigger-related error
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18741 Logged by: Alexander Lakhin Email address: exclusion@gmail.com PostgreSQL version: 17.2 Operating system: Ubuntu 22.04 Description: The following script: CREATE TABLE pt1 (a int PRIMARY KEY) PARTITION BY RANGE (a); CREATE TABLE p1 PARTITION OF pt1 FOR VALUES FROM (0) TO (1); CREATE TABLE pt2 (a int) PARTITION BY RANGE (a); CREATE TABLE p2 (a int, FOREIGN KEY (a) REFERENCES pt1); ALTER TABLE pt2 ATTACH PARTITION p2 FOR VALUES FROM (0) TO (1); ALTER TABLE pt2 DETACH PARTITION p2; fails with: ERROR: XX000: could not find ON INSERT check triggers of foreign key constraint 16404 LOCATION: GetForeignKeyCheckTriggers, tablecmds.c:11355 Reproduced on REL_15_STABLE (starting from f4566345c) .. master.
Re: BUG #18741: Detaching a partition referencing a partitioned table fails with a trigger-related error
From
Tender Wang
Date:
PG Bug reporting form <noreply@postgresql.org> 于2024年12月8日周日 22:43写道：
The following bug has been logged on the website:
Bug reference: 18741
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 17.2
Operating system: Ubuntu 22.04
Description:
The following script:
CREATE TABLE pt1 (a int PRIMARY KEY) PARTITION BY RANGE (a);
CREATE TABLE p1 PARTITION OF pt1 FOR VALUES FROM (0) TO (1);
CREATE TABLE pt2 (a int) PARTITION BY RANGE (a);
CREATE TABLE p2 (a int, FOREIGN KEY (a) REFERENCES pt1);
ALTER TABLE pt2 ATTACH PARTITION p2 FOR VALUES FROM (0) TO (1);
ALTER TABLE pt2 DETACH PARTITION p2;
fails with:
ERROR: XX000: could not find ON INSERT check triggers of foreign key
constraint 16404
LOCATION: GetForeignKeyCheckTriggers, tablecmds.c:11355
Reproduced on REL_15_STABLE (starting from f4566345c) .. master.
Thanks for reporting. I can reproduce this on master.
We currently can handle two cases:
1. parent rel has foreign key, but attachrel doesn't have. So we just clone parent's fks to attachrel.
2. parent rel and attachrel both have foreign keys, and these fks are consistent, so we can still use attachrel fk not doing expensive clone.
If I understand correctly, we don't do well when parent rel no fks but attachrel has one.
We can fix above case, but it seems not easy.
Would it work to reject this attaching directly? This may affect users who already do some attaching, like in this case.
If we allow the above case, we can still attach another partition without a foreign key. However, maintaining consistent catalog data between parent and child will take much work.
Thanks,
Tender Wang