On Thu, May 6, 2010 at 6:37 AM, Jaime Casanova <ja...@2ndquadrant.com> wrote: > i would call it a bug, but this is a known issue > >> >> The only solution currently is that the referring table has to be >> partitioned the same way as the referred table in the FK, and >> its parent table has to be queried. >> > > no, you can install a trigger on the child table that verifies the > existence of the id on your partitioned parent table, the SELECT > you'll use inside that trigger will look at the entire set of tables > (as long as you don't use FROM ONLY) > > also could be useful to put an index (even a PK) on every child to > ensure uniqueness and make the SELECT more efficient, and of course a > check constraint in every child emulating a partition key
The referential integrity triggers contain some extra magic that isn't easily simulatable in userland, and that is necessary to make the foreign key constraints airtight. We've discussed this previously but I don't remember which thread it was or the details of when things blow up. I think it's something like this: the parent has a tuple that is not referenced by any child. Transaction 1 begins, deletes the parent tuple (checking that it has no children), and pauses. Transaction 2 begins, adds a child tuple that references the parent tuple (checking that the parent exists, which it does), and commits. Transaction 1 commits. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
