pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Avoid bogus scans of partitions when validating FKs to partition 2+ messages / 1 participants [nested] [flat]
* pgsql: Avoid bogus scans of partitions when validating FKs to partition @ 2025-06-05 15:18 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 2+ messages in thread From: Álvaro Herrera @ 2025-06-05 15:18 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid bogus scans of partitions when validating FKs to partitioned tables Validating an unvalidated foreign key that references a partitioned table would try to queue validations for each individual partition of the referenced table, but this is wrong: each individual partition would not necessarily have all the referenced rows, so errors would be raised. Avoid doing that. The pg_constraint rows that cause this to happen are only there to support the action triggers that implement the DELETE/ UPDATE actions of the FK, so no validating scan is necessary. This was an oversight in commit b663b9436e75. An equivalent oversight exists for NOT ENFORCED constraints, which is not fixed in this commit. Author: Amul Sul <sulamul@gmail.com> Reported-by: Antonin Houska <ah@cybertec.at> Reviewed-by: jian he <jian.universality@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/26983.1748418675@localhost Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/cc733ed164c5b57fdf34d16e4cc8e9bbdc171699 Modified Files -------------- src/backend/commands/tablecmds.c | 51 ++++++++++++++++------- src/test/regress/expected/foreign_key.out | 68 ++++++++++++++++++++++++------- src/test/regress/sql/foreign_key.sql | 29 ++++++++++--- 3 files changed, 114 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Avoid bogus scans of partitions when marking FKs enforced @ 2025-06-05 16:39 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 2+ messages in thread From: Álvaro Herrera @ 2025-06-05 16:39 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid bogus scans of partitions when marking FKs enforced Similar to commit cc733ed164c5: when an unenforced foreign key that references a partitioned table is altered to be enforced, we scan the constrained table based on each partition on the referenced partitioned table. This is bogus and likely to cause the ALTER TABLE to fail: we must only scan the constrained table as pointing to the top-level partitioned table. Oversight in commit eec0040c4bcd. Fix by eliding those scans. Author: Amul Sul <sulamul@gmail.com> Reported-by: jian he <jian.universality@gmail.com> Discussion: https://postgr.es/m/CACJufxF1e_gPOLtsDoaE4VCgQPC8KZW_kPAjPR5Rvv4Ew=fb2A@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/e6f98d8848f1803fda32011998c786a1bf4eb87c Modified Files -------------- src/backend/commands/tablecmds.c | 7 ++++-- src/test/regress/expected/foreign_key.out | 41 +++++++++++++++++++------------ src/test/regress/sql/foreign_key.sql | 11 ++++++--- 3 files changed, 37 insertions(+), 22 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-06-05 16:39 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-05 15:18 pgsql: Avoid bogus scans of partitions when validating FKs to partition Álvaro Herrera <alvherre@kurilemu.de> 2025-06-05 16:39 pgsql: Avoid bogus scans of partitions when marking FKs enforced Álvaro Herrera <alvherre@kurilemu.de>
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