Re: Proposed feature: Selective Foreign Keys

On 12/02/2013 05:06 AM, Andres Freund wrote:
> On 2013-12-02 08:57:01 +0000, Albe Laurenz wrote:
>> What strikes me is that since foreign key constraints are implemented
>> as triggers in PostgreSQL, this solution would probably not have many
>> performance benefits over a self-written trigger that implements the
>> same functionality. Since you need two triggers for your example,
>> the performance might even be worse than a single self-written trigger.
> Note that you cannot really write correct RI triggers without playing
> very low level games, i.e. writing C and using special kinds of
> snapshots and such.

Yeah, I really don't think that's a feasible way to to this.

The only way I have thought of as an alternative to this proposal is to
use a partitioned table with different FK constraints for each child.
That's certainly doable, but not without a deal of work, and even then
you'd be giving up certain things, such as guaranteeing the uniqueness
of the object key, at least without a lot more work.

You can think of it this way: we currently enforce FK constraints except
when the value being constrained is NULL (or part of it is NULL in the
MATCH SIMPLE case). This is really a user-defined extension of the
exception condition. I have at least one case where I could have used
this feature and saved a significant amount of work. We wanted to apply
FK constraints to a very large table, but grandfather in certain cases
that didn't meet the constraint. That could have been done very simply
using this feature.

cheers

andrew