Thread: foreign key constraints referencing inherited relations - WIP

Warning: This patch is only a work in work in progress.

Current Status:

This patch modifies postgres's current behavior by creating foreign-key
triggers on not only the source and referenced tables, but also the children
of the referenced table.  It modifies the source triggers, removing the ONLY
keyword when looking up the referenced key.

Shortcomings:

The triggers are only created when the foreign key is created, so any new
tables that inherit from the referenced table will not have the triggers.
This can be fixed fairly easily.

When removing the ONLY clause on the foreign key checks, I also had to remove
the FOR SHARE OF x part.  This was in the following functions: RI_FKey_check,
ri_Check_Pk_Match, RI_FKey_noaction_del, RI_FKey_noaction_upd,
RI_FKey_restrict_del. I don't really understand the significance of this.

There is no way to turn off the new behavior, which will likely cause
breakages in existing applications.  If this behavior was made optional on a
per foreign key basis, with the current syntax being backwards compatible,
then this would not be an issue.

Inherited relations don't share primary key indexes, so there is the
possibility of duplicates.  Can this be fixed by putting triggers on the
inherited tables to ensure they aren't duplicating a primary key?

Can/should indexes, constraints, triggers be shared between inherited tables?
Would this be a lot of work, and is it desired?  Does it need to be
configurable per index, or per inheritance?

Matt Newell,
Blur Studio

Matt Newell <newellm@blur.com> writes:
> When removing the ONLY clause on the foreign key checks, I also had to remove
> the FOR SHARE OF x part.  This was in the following functions: RI_FKey_check,
> ri_Check_Pk_Match, RI_FKey_noaction_del, RI_FKey_noaction_upd,
> RI_FKey_restrict_del. I don't really understand the significance of this.

It means you broke it for concurrent-update cases; there's no interlock
preventing someone from dropping the PK row after you look at it
and before you commit the referencing row.

> Inherited relations don't share primary key indexes, so there is the
> possibility of duplicates.

Which means the whole thing doesn't work, really.  Without a uniqueness
constraint across the set of PK rows, the semantics of FK constraints
are not well-defined.  The multi-table-unique-constraint problem has to
be solved before we can even think much about multi-table FKs :-(

Without having read the patch in detail, I'm guessing that what you've
done might allow the referencing side of an FK constraint to be
inherited (since all this takes is copying down the triggers to the
child tables), but the referenced side still has to be a single table.

            regards, tom lane