Обсуждение: IMMEDIATE constraint enforcement does not comply with the SQLstandard
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
Вложения
"Finnerty, Jim" <jfinnert@amazon.com> writes:
> PostgreSQL executes AFTER triggers (and constraints) in alphabetical order according to the name assigned to the
triggerat creation time; therefore, PostgreSQL permits IMMEDIATE foreign key constraints to be deferred until after
AFTERtriggers are fired if their name sorts lower than “RI_ConstraintTrigger”. This is a violation of the standard.
We consider that a feature, not a bug. If you want standard-compliant
behavior, don't name your triggers that way. On the other hand, if
you'd like to get something done before FK constraints fire, you have
the option to do so.
(I think this is documented somewhere. If not, there's an opportunity
for documentation improvement.)
The fact that the sort is plain ASCII means that "RI_ConstraintTrigger"
sorts before any lower-case trigger name, so the issue is not as large
as it might otherwise seem.
regards, tom lane
"Finnerty, Jim" <jfinnert@amazon.com> writes:
> PostgreSQL executes AFTER triggers (and constraints) in alphabetical order according to the name assigned to the
triggerat creation time; therefore, PostgreSQL permits IMMEDIATE foreign key constraints to be deferred until after
AFTERtriggers are fired if their name sorts lower than “RI_ConstraintTrigger”. This is a violation of the standard.
We consider that a feature, not a bug. If you want standard-compliant
behavior, don't name your triggers that way. On the other hand, if
you'd like to get something done before FK constraints fire, you have
the option to do so.
(I think this is documented somewhere. If not, there's an opportunity
for documentation improvement.)
The fact that the sort is plain ASCII means that "RI_ConstraintTrigger"
sorts before any lower-case trigger name, so the issue is not as large
as it might otherwise seem.
regards, tom lane
We do want standard-compliant behavior, and standard-compliance a strength of
PostgreSQL, and as mentioned we already do have a way (DEFERRABLE INITIALLY
DEFERRED) if this is the user's intent.
The consequences of non-compliance is much more insidious than it at first
appears. At stake here is whether PostgreSQL supports non-deferrable
constraints at all. The purpose of having non-deferrable IMMEDIATE
constraints in the standard is to make query optimizations possible.
Also, as far as I can tell, we do not document the specific name that we
generate for constraint triggers; therefore, any application that depends on
undocumented naming conventions is skating on precariously thin ice, in
addition to writing non-portable code.
The equivalent logic was implemented on Oracle and MySQL. Both of those
systems implement IMMEDIATE mode constraints consistently with the standard,
and produce a different result than PostgreSQL.
best regards,
/Jim F
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
We do want standard-compliant behavior, and standard-compliance a strength of
PostgreSQL, and as mentioned we already do have a way (DEFERRABLE INITIALLY
DEFERRED) if this is the user's intent.
The consequences of non-compliance is much more insidious than it at first
appears. At stake here is whether PostgreSQL supports non-deferrable
constraints at all. The purpose of having non-deferrable IMMEDIATE
constraints in the standard is to make query optimizations possible.
Also, as far as I can tell, we do not document the specific name that we
generate for constraint triggers; therefore, any application that depends on
undocumented naming conventions is skating on precariously thin ice, in
addition to writing non-portable code.
The equivalent logic was implemented on Oracle and MySQL. Both of those
systems implement IMMEDIATE mode constraints consistently with the standard,
and produce a different result than PostgreSQL.
best regards,
/Jim F
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
>>>>> "Jim" == Jim Finnerty <jfinnert@amazon.com> writes: Jim> The consequences of non-compliance is much more insidious than it Jim> at first appears. At stake here is whether PostgreSQL supports Jim> non-deferrable constraints at all. The purpose of having Jim> non-deferrable IMMEDIATE constraints in the standard is to make Jim> query optimizations possible. Unfortunately the possibility of query optimizations for FKs is already defeated by the issue of referential actions, where we also violate the spec. https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Trigger_firing_relative_to_referential_constraint_actions (I just added your issue to that entry, which was already there to discuss the referential actions vs BEFORE trigger timing.) -- Andrew (irc:RhodiumToad)
>>>>> "Jim" == Jim Finnerty <jfinnert@amazon.com> writes: Jim> The consequences of non-compliance is much more insidious than it Jim> at first appears. At stake here is whether PostgreSQL supports Jim> non-deferrable constraints at all. The purpose of having Jim> non-deferrable IMMEDIATE constraints in the standard is to make Jim> query optimizations possible. Unfortunately the possibility of query optimizations for FKs is already defeated by the issue of referential actions, where we also violate the spec. https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Trigger_firing_relative_to_referential_constraint_actions (I just added your issue to that entry, which was already there to discuss the referential actions vs BEFORE trigger timing.) -- Andrew (irc:RhodiumToad)
ON [ DELETE | UPDATE ] [ CASCADE | RESTRICT ] pertains to inter-table
relationships. My understanding is that the constraint trigger names have
been carefully chosen in order to get the referential actions to work
correctly; however, this patch does not change the names or the relative
firing order of constraint triggers relative to each other.
The SET NULL action is not problematic with respect to standard compliance,
but the SET DEFAULT action potentially would be. According to the
documentation page that you referenced, though:
"... if an action specifies SET DEFAULT but the default value would not
satisfy the foreign key constraint, the operation will fail."
The patch passes all tests in installcheck serial, as well as the tests we
have added (included with the patch), so if there is a problem we don't have
test coverage for it. Can anyone devise a test that would cause the patch
to change the behavior of a BEFORE trigger or a referential action?
thank you,
/Jim F
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
We created a repro for the ON UPDATE CASCADE ... BEFORE trigger problem, which as Andrew said is a second area where the SQL standard is violated. ----- Jim Finnerty, AWS, Amazon Aurora PostgreSQL -- Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html