IMMEDIATE constraint enforcement does not comply with the SQLstandard - Mailing list pgsql-bugs

Problem Description:

The current implementation of immediate-mode foreign key constraints in PostgreSQL does not comply with the SQL standard.  According to the standard, an immediate-mode foreign key constraint must be enforced immediately upon statement completion.  This implies that an immediate-mode constraint must be enforced before any ‘after statement’ triggers are fired. 

Foreign Key Constraints in PostgreSQL are implemented as triggers. When a user creates a foreign key constraint, internally the system creates 4 triggers:

·         An after trigger on update of an entry in the foreign key table to check that the constraint is met by the update.

·         An after trigger on insert into the foreign key table to check that the constraint is met by the insert.

·         An after trigger on delete of an entry in the primary key table to check that the constraint is not broken by the delete.

·         An after trigger on update of an entry in the primary key table to check that the constraint is not broken by the update.

·         Each of these triggers is given a name that starts with "RI_ConstraintTrigger".

PostgreSQL executes AFTER triggers (and constraints) in alphabetical order according to the name assigned to the trigger at creation time; therefore, PostgreSQL permits IMMEDIATE foreign key constraints to be deferred until after AFTER triggers are fired if their name sorts lower than “RI_ConstraintTrigger”.  This is a violation of the standard.

If an AFTER trigger or user-mode constraint has a name (such as “A_trig”) that causes it to fire before an IMMEDIATE foreign key constraint, then arbitrary code can be executed while the constraint is violated, including code that causes the foreign key condition to be satisfied again. In that case the statement should fail according to the standard but it will succeed in PostgreSQL. A user that wants to have code fire before the constraint is enforced can (and should) do so in a standard-compliant way by making the constraint DEFERRABLE INITIALLY DEFERRED.

We believe that the scenario of having a trigger intentionally fixing up an immediate-mode foreign key constraint probably doesn’t happen very often.  This is fortunate, because it makes it possible to conform with the SQL standard without affecting a large number of applications.  What is desired is a solution that preserves the name ordering of triggers, except for the IMMEDIATE constraints, which must fire first.  User-defined IMMEDIATE constraints are not covered by the standard, but we believe that the interpretation most consistent with both the standard and existing PostgreSQL behavior is:

·         Fire the IMMEDIATE system constraint triggers in ascending name order,

·         Then fire the IMMEDIATE user-defined constraint triggers in ascending name order, 

·         Then fire all other triggers in ascending name order

A patch implementing this proposed fix is attached (see after_trigger.diff).

Example:

<see fk_bug.sql, attached>

postgres=# \i ~/fk_bug.sql

CREATE TABLE

CREATE TABLE

psql:/home/jfinnert/fk_bug.sql:7: ERROR:  insert or update on table "t2" violates foreign key constraint "t2_t1id_fkey"

DETAIL:  Key (t1id)=(1) is not present in table "t1".

CREATE FUNCTION

CREATE TRIGGER

INSERT 0 1

 id |                           data                           

----+----------------------------------------------------------

  1 | the presence of this row in t1 violates the SQL standard

(1 row)

 id | t1id |                           data                           

----+------+----------------------------------------------------------

  1 |    1 | the presence of this row in t2 violates the SQL standard

(1 row)

Platform Information:

postgres=# select version();

                                    version                                     

--------------------------------------------------------------------------------

 PostgreSQL 10beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit

(1 row)

Thank you,

    Jim Finnerty, Principal Engineer, Amazon Aurora PostgreSQL