forked frompostgres/postgres
- Notifications
You must be signed in to change notification settings - Fork6
Commit53af949
Restructure foreign key handling code for ATTACH/DETACH
... to fix bugs when the referenced table is partitioned.The catalog representation we chose for foreign keys connectingpartitioned tables (in commitf56f8f8) is inconvenient, in thesense that a standalone table has a different way to represent theconstraint when referencing a partitioned table, than when the sametable becomes a partition (and vice versa). Because of this, we need tocreate additional catalog rows on detach (pg_constraint and pg_trigger),and remove them on attach. We were doing some of those things, but notall of them, leading to missing catalog rows in certain cases.The worst problem seems to be that we are missing action triggers afterdetaching a partition, which means that you could update/delete rowsfrom the referenced partitioned table that still had referencing rows onthat table, the server failing to throw the required errors.!!!Note that this means existing databases with FKs that referencepartitioned tables might have rows that break relational integrity, ontables that were once partitions on the referencing side of the FK.Another possible problem is that trying to reattach a tablethat had been detached would fail indicating that internal triggerscannot be found, which from the user's point of view is nonsensical.In branches 15 and above, we fix this by creating a new helper functionaddFkConstraint() which is in charge of creating a standalonepg_constraint row, and repurposing addFkRecurseReferencing() andaddFkRecurseReferenced() so that they're only the recursive routine foreach side of the FK, and they call addFkConstraint() to createpg_constraint at each partitioning level and add the necessary triggers.These new routines can be used during partition creation, partitionattach and detach, and foreign key creation. This reduces redundantcode and simplifies the flow.In branches 14 and 13, we have a much simpler fix that consists onsimply removing the constraint on detach. The reason is that thosebranches are missing commitf456634, which reworked the way thisworks in a way that we didn't consider back-patchable at the time.We opted to leave branch 12 alone, because it's different from branch 13enough that the fix doesn't apply; and because it is going in EOL modevery soon, patching it now might be worse since there's no way to undothe damage if it goes wrong.Existing databases might need to be repaired.In the future we might want to rethink the catalog representation toavoid this problem, but for now the code seems to do what's required tomake the constraints operate correctly.Co-authored-by: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>Co-authored-by: Tender Wang <tndrwang@gmail.com>Co-authored-by: Alvaro Herrera <alvherre@alvh.no-ip.org>Reported-by: Guillaume Lelarge <guillaume@lelarge.info>Reported-by: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>Reported-by: Thomas Baehler (SBB CFF FFS) <thomas.baehler2@sbb.ch>Discussion:https://postgr.es/m/20230420144344.40744130@karstDiscussion:https://postgr.es/m/20230705233028.2f554f73@karstDiscussion:https://postgr.es/m/GVAP278MB02787E7134FD691861635A8BC9032@GVAP278MB0278.CHEP278.PROD.OUTLOOK.COMDiscussion:https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org1 parente155564 commit53af949
File tree
4 files changed
+575
-254
lines changed- src
- backend/commands
- test/regress
- expected
- sql
- tools/pgindent
4 files changed
+575
-254
lines changed0 commit comments
Comments
(0)