On Dec2, 2013, at 10:06 , Andres Freund <and...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
