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