pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Handle self-referencing FKs correctly in partitioned tables 6+ messages / 1 participants [nested] [flat]
* pgsql: Handle self-referencing FKs correctly in partitioned tables @ 2025-05-02 19:30 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-05-02 19:30 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Handle self-referencing FKs correctly in partitioned tables For self-referencing foreign keys in partitioned tables, we weren't handling creation of pg_constraint rows during CREATE TABLE PARTITION AS as well as ALTER TABLE ATTACH PARTITION. This is an old bug -- mostly, we broke this in 614a406b4ff1 while trying to fix it (so 12.13, 13.9, 14.6 and 15.0 and up all behave incorrectly). This commit reverts part of that with additional fixes for full correctness, and installs more tests to verify the parts we broke, not just the catalog contents but also the user-visible behavior. Backpatch to all live branches. In branches 13 and 14, commit 46a8c27a7226 changed the behavior during DETACH to drop a FK constraint rather than trying to repair it, because the complete fix of repairing catalog constraints was problematic due to lack of previous fixes. For this reason, the test behavior in those branches is a bit different. However, as best as I can tell, the fix works correctly there. In release notes we have to recommend that all self-referencing foreign keys on partitioned tables be recreated if partitions have been created or attached after the FK was created, keeping in mind that violating rows might already be present on the referencing side. Reported-by: Guillaume Lelarge <guillaume@lelarge.info> Reported-by: Matthew Gabeler-Lee <fastcat@gmail.com> Reported-by: Luca Vallisa <luca.vallisa@gmail.com> Discussion: https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com Discussion: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org Discussion: https://postgr.es/m/CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/c83a38758d2a9b4a72ec8f7e218f7546457f265d Modified Files -------------- src/backend/commands/tablecmds.c | 21 ++---- src/test/regress/expected/foreign_key.out | 116 +++++++++++++++++------------- src/test/regress/expected/triggers.out | 8 ++- src/test/regress/sql/foreign_key.sql | 35 +++++++-- 4 files changed, 107 insertions(+), 73 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Handle self-referencing FKs correctly in partitioned tables @ 2025-05-02 19:30 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-05-02 19:30 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Handle self-referencing FKs correctly in partitioned tables For self-referencing foreign keys in partitioned tables, we weren't handling creation of pg_constraint rows during CREATE TABLE PARTITION AS as well as ALTER TABLE ATTACH PARTITION. This is an old bug -- mostly, we broke this in 614a406b4ff1 while trying to fix it (so 12.13, 13.9, 14.6 and 15.0 and up all behave incorrectly). This commit reverts part of that with additional fixes for full correctness, and installs more tests to verify the parts we broke, not just the catalog contents but also the user-visible behavior. Backpatch to all live branches. In branches 13 and 14, commit 46a8c27a7226 changed the behavior during DETACH to drop a FK constraint rather than trying to repair it, because the complete fix of repairing catalog constraints was problematic due to lack of previous fixes. For this reason, the test behavior in those branches is a bit different. However, as best as I can tell, the fix works correctly there. In release notes we have to recommend that all self-referencing foreign keys on partitioned tables be recreated if partitions have been created or attached after the FK was created, keeping in mind that violating rows might already be present on the referencing side. Reported-by: Guillaume Lelarge <guillaume@lelarge.info> Reported-by: Matthew Gabeler-Lee <fastcat@gmail.com> Reported-by: Luca Vallisa <luca.vallisa@gmail.com> Discussion: https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com Discussion: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org Discussion: https://postgr.es/m/CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/f51ae3187472536f0ab6e7dc421399062547eb9d Modified Files -------------- src/backend/commands/tablecmds.c | 21 ++---- src/test/regress/expected/foreign_key.out | 104 +++++++++++++++++++----------- src/test/regress/expected/triggers.out | 8 ++- src/test/regress/sql/foreign_key.sql | 35 ++++++++-- 4 files changed, 107 insertions(+), 61 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Handle self-referencing FKs correctly in partitioned tables @ 2025-05-02 19:30 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-05-02 19:30 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Handle self-referencing FKs correctly in partitioned tables For self-referencing foreign keys in partitioned tables, we weren't handling creation of pg_constraint rows during CREATE TABLE PARTITION AS as well as ALTER TABLE ATTACH PARTITION. This is an old bug -- mostly, we broke this in 614a406b4ff1 while trying to fix it (so 12.13, 13.9, 14.6 and 15.0 and up all behave incorrectly). This commit reverts part of that with additional fixes for full correctness, and installs more tests to verify the parts we broke, not just the catalog contents but also the user-visible behavior. Backpatch to all live branches. In branches 13 and 14, commit 46a8c27a7226 changed the behavior during DETACH to drop a FK constraint rather than trying to repair it, because the complete fix of repairing catalog constraints was problematic due to lack of previous fixes. For this reason, the test behavior in those branches is a bit different. However, as best as I can tell, the fix works correctly there. In release notes we have to recommend that all self-referencing foreign keys on partitioned tables be recreated if partitions have been created or attached after the FK was created, keeping in mind that violating rows might already be present on the referencing side. Reported-by: Guillaume Lelarge <guillaume@lelarge.info> Reported-by: Matthew Gabeler-Lee <fastcat@gmail.com> Reported-by: Luca Vallisa <luca.vallisa@gmail.com> Discussion: https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com Discussion: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org Discussion: https://postgr.es/m/CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1817d62ecbf55f27101b8ad30e24630c33b38325 Modified Files -------------- src/backend/commands/tablecmds.c | 21 ++---- src/test/regress/expected/foreign_key.out | 104 +++++++++++++++++++----------- src/test/regress/expected/triggers.out | 8 ++- src/test/regress/sql/foreign_key.sql | 35 ++++++++-- 4 files changed, 107 insertions(+), 61 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Handle self-referencing FKs correctly in partitioned tables @ 2025-05-02 19:30 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-05-02 19:30 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Handle self-referencing FKs correctly in partitioned tables For self-referencing foreign keys in partitioned tables, we weren't handling creation of pg_constraint rows during CREATE TABLE PARTITION AS as well as ALTER TABLE ATTACH PARTITION. This is an old bug -- mostly, we broke this in 614a406b4ff1 while trying to fix it (so 12.13, 13.9, 14.6 and 15.0 and up all behave incorrectly). This commit reverts part of that with additional fixes for full correctness, and installs more tests to verify the parts we broke, not just the catalog contents but also the user-visible behavior. Backpatch to all live branches. In branches 13 and 14, commit 46a8c27a7226 changed the behavior during DETACH to drop a FK constraint rather than trying to repair it, because the complete fix of repairing catalog constraints was problematic due to lack of previous fixes. For this reason, the test behavior in those branches is a bit different. However, as best as I can tell, the fix works correctly there. In release notes we have to recommend that all self-referencing foreign keys on partitioned tables be recreated if partitions have been created or attached after the FK was created, keeping in mind that violating rows might already be present on the referencing side. Reported-by: Guillaume Lelarge <guillaume@lelarge.info> Reported-by: Matthew Gabeler-Lee <fastcat@gmail.com> Reported-by: Luca Vallisa <luca.vallisa@gmail.com> Discussion: https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com Discussion: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org Discussion: https://postgr.es/m/CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/6ba979cf570cdab8187aa2a1ffcd3d242a4856cf Modified Files -------------- src/backend/commands/tablecmds.c | 21 ++---- src/test/regress/expected/foreign_key.out | 104 +++++++++++++++++++----------- src/test/regress/expected/triggers.out | 8 ++- src/test/regress/sql/foreign_key.sql | 35 ++++++++-- 4 files changed, 107 insertions(+), 61 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Handle self-referencing FKs correctly in partitioned tables @ 2025-05-02 19:30 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-05-02 19:30 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Handle self-referencing FKs correctly in partitioned tables For self-referencing foreign keys in partitioned tables, we weren't handling creation of pg_constraint rows during CREATE TABLE PARTITION AS as well as ALTER TABLE ATTACH PARTITION. This is an old bug -- mostly, we broke this in 614a406b4ff1 while trying to fix it (so 12.13, 13.9, 14.6 and 15.0 and up all behave incorrectly). This commit reverts part of that with additional fixes for full correctness, and installs more tests to verify the parts we broke, not just the catalog contents but also the user-visible behavior. Backpatch to all live branches. In branches 13 and 14, commit 46a8c27a7226 changed the behavior during DETACH to drop a FK constraint rather than trying to repair it, because the complete fix of repairing catalog constraints was problematic due to lack of previous fixes. For this reason, the test behavior in those branches is a bit different. However, as best as I can tell, the fix works correctly there. In release notes we have to recommend that all self-referencing foreign keys on partitioned tables be recreated if partitions have been created or attached after the FK was created, keeping in mind that violating rows might already be present on the referencing side. Reported-by: Guillaume Lelarge <guillaume@lelarge.info> Reported-by: Matthew Gabeler-Lee <fastcat@gmail.com> Reported-by: Luca Vallisa <luca.vallisa@gmail.com> Discussion: https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com Discussion: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org Discussion: https://postgr.es/m/CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1649d153a1729dd09d1e9af79b3868d1892b11ac Modified Files -------------- src/backend/commands/tablecmds.c | 21 ++---- src/test/regress/expected/foreign_key.out | 112 ++++++++++++++++++++---------- src/test/regress/expected/triggers.out | 24 ++++--- src/test/regress/sql/foreign_key.sql | 38 ++++++++-- 4 files changed, 126 insertions(+), 69 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Handle self-referencing FKs correctly in partitioned tables @ 2025-05-02 19:30 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 6+ messages in thread From: Álvaro Herrera @ 2025-05-02 19:30 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Handle self-referencing FKs correctly in partitioned tables For self-referencing foreign keys in partitioned tables, we weren't handling creation of pg_constraint rows during CREATE TABLE PARTITION AS as well as ALTER TABLE ATTACH PARTITION. This is an old bug -- mostly, we broke this in 614a406b4ff1 while trying to fix it (so 12.13, 13.9, 14.6 and 15.0 and up all behave incorrectly). This commit reverts part of that with additional fixes for full correctness, and installs more tests to verify the parts we broke, not just the catalog contents but also the user-visible behavior. Backpatch to all live branches. In branches 13 and 14, commit 46a8c27a7226 changed the behavior during DETACH to drop a FK constraint rather than trying to repair it, because the complete fix of repairing catalog constraints was problematic due to lack of previous fixes. For this reason, the test behavior in those branches is a bit different. However, as best as I can tell, the fix works correctly there. In release notes we have to recommend that all self-referencing foreign keys on partitioned tables be recreated if partitions have been created or attached after the FK was created, keeping in mind that violating rows might already be present on the referencing side. Reported-by: Guillaume Lelarge <guillaume@lelarge.info> Reported-by: Matthew Gabeler-Lee <fastcat@gmail.com> Reported-by: Luca Vallisa <luca.vallisa@gmail.com> Discussion: https://postgr.es/m/CAECtzeWHCA+6tTcm2Oh2+g7fURUJpLZb-=pRXgeWJ-Pi+VU=_w@mail.gmail.com Discussion: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org Discussion: https://postgr.es/m/CAAT=myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b3a9c536db677586887fe31a4f41fd942d573e6d Modified Files -------------- src/backend/commands/tablecmds.c | 21 ++---- src/test/regress/expected/foreign_key.out | 112 ++++++++++++++++++++---------- src/test/regress/expected/triggers.out | 24 ++++--- src/test/regress/sql/foreign_key.sql | 38 ++++++++-- 4 files changed, 126 insertions(+), 69 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-05-02 19:30 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-05-02 19:30 pgsql: Handle self-referencing FKs correctly in partitioned tables Álvaro Herrera <alvherre@kurilemu.de> 2025-05-02 19:30 pgsql: Handle self-referencing FKs correctly in partitioned tables Álvaro Herrera <alvherre@kurilemu.de> 2025-05-02 19:30 pgsql: Handle self-referencing FKs correctly in partitioned tables Álvaro Herrera <alvherre@kurilemu.de> 2025-05-02 19:30 pgsql: Handle self-referencing FKs correctly in partitioned tables Álvaro Herrera <alvherre@kurilemu.de> 2025-05-02 19:30 pgsql: Handle self-referencing FKs correctly in partitioned tables Álvaro Herrera <alvherre@kurilemu.de> 2025-05-02 19:30 pgsql: Handle self-referencing FKs correctly in partitioned tables Á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