As a side effect of fixing timing issues with the new trigger
timing, Tom and I noticed that some foreign key actions were broken
(especially with deferred constraints) and as part of looking at that we
believe that we had made the wrong decision as to when the actions were
meant to fire in the first place.
We had used a note in the foreign key section
NOTE 182 - Subclause 10.9, "<constraint name definition> and
<constraint characteristics>", specifies when a constraint is
effectively checked.
and the referenced section to run both the constraint check and
referential actions at the time specified by the constraint
characteristics. However, both the note and section talk about checking
and not other rules of the constraint.
While going through the spec, Tom noticed that SQL99 14.20 "Effect
of replacing rows in base tables" mentions as part of the actual
replacement of rows for updates (GR7), that "The General Rules of
Subclause 11.8, "<referential constraint definition>", are now
applicable." The likely general rules being mentioned are the referential
action rules, which make statements like: "If a non-null value of a
referenced column in the referenced table is updated to a value that is
distinct from the current value of that column, then for every member F of
the subtable family of the referencing table:..." The rules seem to define
the actions as if they happen as part of the statement's main execution
rather than as part of integrity constraint checking, due to things like
interactions between 11.8 GR15 and 11.8 GR7: "15) All rows marked for
deletion are effectively deleted at the end of the SQL-statement prior to
the checking of any integrity constraints", "7) If a row of the referenced
table that has not previously been marked for deletion is marked for
deletion, then [description of on delete action behavior]".
While not exactly what the spec invisions, I believe we can come
closer to the correct behavior by treating all of the referential actions
as non-deferrable while allowing deferment of NO ACTION and the check
itself. The behavior is not quite right because we can both put an after
trigger before the referential action trigger (which might be seen as a
feature) and because I think an after trigger on one row of a multi-row
update may see the old referencing rows for later rows of the update that
haven't had their triggers run yet. In addition, to handle an odd case for
set default, we are currently immediately checking for referencing rows
after doing the action inside the function. For deferred constraints, this
check would now happen immediately where it should presumably be deferred.
Index: doc/src/sgml/ddl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ddl.sgml,v
retrieving revision 1.30
diff -c -r1.30 ddl.sgml
*** doc/src/sgml/ddl.sgml 8 Aug 2004 21:33:11 -0000 1.30
--- doc/src/sgml/ddl.sgml 5 Oct 2004 14:39:27 -0000
***************
*** 940,949 ****
<para>
Restricting and cascading deletes are the two most common options.
! <literal>RESTRICT</literal> can also be written as <literal>NO
! ACTION</literal> and it's also the default if you do not specify
! anything. There are two other options for what should happen with
! the foreign key columns when a primary key is deleted:
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
Note that these do not excuse you from observing any constraints.
For example, if an action specifies <literal>SET DEFAULT</literal>
--- 940,951 ----
<para>
Restricting and cascading deletes are the two most common options.
! <literal>RESTRICT</literal> prevents a statement from deleting a
! referenced row. <literal>NO ACTION</literal> means that if at the
! constraint check time, any referencing rows still exist, an error
! is raised and is the default if you do not specify anything.
! There are two other options for what should happen with the foreign
! key columns when a primary key is deleted:
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
Note that these do not excuse you from observing any constraints.
For example, if an action specifies <literal>SET DEFAULT</literal>
Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.84
diff -c -r1.84 create_table.sgml
*** doc/src/sgml/ref/create_table.sgml 2 Aug 2004 04:25:31 -0000 1.84
--- doc/src/sgml/ref/create_table.sgml 5 Oct 2004 14:39:29 -0000
***************
*** 452,458 ****
clause specifies the action to perform when a referenced column
in the referenced table is being updated to a new value. If the
row is updated, but the referenced column is not actually
! changed, no action is done. There are the following possible
actions for each clause:
<variablelist>
--- 452,460 ----
clause specifies the action to perform when a referenced column
in the referenced table is being updated to a new value. If the
row is updated, but the referenced column is not actually
! changed, no action is done. Referential actions apart from the
! check of <literal>NO ACTION<literal> can not be deferred even if
! the constraint is deferrable. There are the following possible
actions for each clause:
<variablelist>
***************
*** 460,468 ****
<term><literal>NO ACTION</literal></term>
<listitem>
<para>
! Produce an error indicating that the deletion or update
! would create a foreign key constraint violation. This is
! the default action.
</para>
</listitem>
</varlistentry>
--- 462,471 ----
<term><literal>NO ACTION</literal></term>
<listitem>
<para>
! Produce an error that the deletion or update would create a
! foreign key violation. If the constraint is deferred, this
! error will be produced at constraint check time if there still
! exist any referencing rows. This is the default action.
</para>
</listitem>
</varlistentry>
***************
*** 471,479 ****
<term><literal>RESTRICT</literal></term>
<listitem>
<para>
! Same as <literal>NO ACTION</literal> except that this action
! will not be deferred even if the rest of the constraint is
! deferrable and deferred.
</para>
</listitem>
</varlistentry>
--- 474,481 ----
<term><literal>RESTRICT</literal></term>
<listitem>
<para>
! Produce an error that the deletion or update would create a
! foreign key constraint violation.
</para>
</listitem>
</varlistentry>
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.133
diff -c -r1.133 tablecmds.c
*** src/backend/commands/tablecmds.c 23 Sep 2004 23:20:24 -0000 1.133
--- src/backend/commands/tablecmds.c 5 Oct 2004 14:39:34 -0000
***************
*** 4357,4368 ****
fk_trigger->actions[1] = '\0';
fk_trigger->isconstraint = true;
- fk_trigger->deferrable = fkconstraint->deferrable;
- fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_del_action)
{
case FKCONSTR_ACTION_NOACTION:
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del");
break;
case FKCONSTR_ACTION_RESTRICT:
--- 4357,4368 ----
fk_trigger->actions[1] = '\0';
fk_trigger->isconstraint = true;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_del_action)
{
case FKCONSTR_ACTION_NOACTION:
+ fk_trigger->deferrable = fkconstraint->deferrable;
+ fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del");
break;
case FKCONSTR_ACTION_RESTRICT:
***************
*** 4371,4382 ****
--- 4371,4388 ----
fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_del");
break;
case FKCONSTR_ACTION_CASCADE:
+ fk_trigger->deferrable = false;
+ fk_trigger->initdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del");
break;
case FKCONSTR_ACTION_SETNULL:
+ fk_trigger->deferrable = false;
+ fk_trigger->initdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del");
break;
case FKCONSTR_ACTION_SETDEFAULT:
+ fk_trigger->deferrable = false;
+ fk_trigger->initdeferred = false;
fk_trigger->funcname =
SystemFuncName("RI_FKey_setdefault_del");
break;
default:
***************
*** 4421,4432 ****
fk_trigger->actions[0] = 'u';
fk_trigger->actions[1] = '\0';
fk_trigger->isconstraint = true;
- fk_trigger->deferrable = fkconstraint->deferrable;
- fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_upd_action)
{
case FKCONSTR_ACTION_NOACTION:
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd");
break;
case FKCONSTR_ACTION_RESTRICT:
--- 4427,4438 ----
fk_trigger->actions[0] = 'u';
fk_trigger->actions[1] = '\0';
fk_trigger->isconstraint = true;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_upd_action)
{
case FKCONSTR_ACTION_NOACTION:
+ fk_trigger->deferrable = fkconstraint->deferrable;
+ fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd");
break;
case FKCONSTR_ACTION_RESTRICT:
***************
*** 4435,4446 ****
--- 4441,4458 ----
fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_upd");
break;
case FKCONSTR_ACTION_CASCADE:
+ fk_trigger->deferrable = false;
+ fk_trigger->initdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd");
break;
case FKCONSTR_ACTION_SETNULL:
+ fk_trigger->deferrable = false;
+ fk_trigger->initdeferred = false;
fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd");
break;
case FKCONSTR_ACTION_SETDEFAULT:
+ fk_trigger->deferrable = false;
+ fk_trigger->initdeferred = false;
fk_trigger->funcname =
SystemFuncName("RI_FKey_setdefault_upd");
break;
default:
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/trigger.c,v
retrieving revision 1.172
diff -c -r1.172 trigger.c
*** src/backend/commands/trigger.c 10 Sep 2004 18:39:56 -0000 1.172
--- src/backend/commands/trigger.c 5 Oct 2004 14:39:37 -0000
***************
*** 2729,2739 ****
/*
* If we found some, check that they fit the
deferrability
! * but skip ON <event> RESTRICT ones, since they are
* silently never deferrable.
*/
if (pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_UPD &&
! pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_DEL)
{
if (stmt->deferred &&
!pg_trigger->tgdeferrable)
ereport(ERROR,
--- 2729,2745 ----
/*
* If we found some, check that they fit the
deferrability
! * but skip referential action ones, since they are
* silently never deferrable.
*/
if (pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_UPD &&
! pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_DEL &&
! pg_trigger->tgfoid != F_RI_FKEY_CASCADE_UPD &&
! pg_trigger->tgfoid != F_RI_FKEY_CASCADE_DEL &&
! pg_trigger->tgfoid != F_RI_FKEY_SETNULL_UPD &&
! pg_trigger->tgfoid != F_RI_FKEY_SETNULL_DEL &&
! pg_trigger->tgfoid != F_RI_FKEY_SETDEFAULT_UPD
&&
! pg_trigger->tgfoid != F_RI_FKEY_SETDEFAULT_DEL)
{
if (stmt->deferred &&
!pg_trigger->tgdeferrable)
ereport(ERROR,
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
