Re: Proposed feature: Selective Foreign Keys

On Dec2, 2013, at 10:06 , Andres Freund <andres(at)2ndquadrant(dot)com> 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.

Very true. I'm unsure whether that's an argument in favour of extending
the built-in FK triggers, or to expose the necessary functionality at the
SQL level, though ;-)

I once tried to do the latter, in a way, by removing the need for the
cross-checking logic (which is the only real low-level game that the
built-in FK triggers play) altogether. That, unfortunately, didn't pan
out - it would have required enlarging the on-disk tuple size to be
able to record to last transaction which locked a tuple even after the
transaction completes.

A simpler way would be to provide a special command which enabled the
re-checking logic for ordinary query. Something like

CONSTRAINT UPDATE table SET … WHERE ...
CONSTRAINT DELETE FROM table WHERE …

which would execute the command with a cross-check snapshot just like
ri_trigger.c's ri_PerformCheck() does.

best regards,
Florian Pflug