aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2004-10-21 21:33:59 +0000
committerTom Lane <tgl@sss.pgh.pa.us>2004-10-21 21:33:59 +0000
commit12a47c6aca43e33d42ef946761ec0406476dd1b0 (patch)
treea66d775b8a16bbac21af385fc2a5bfb9a51aafe8
parent7627b91cd5d9a35415ecb15a55ea46d6b481700c (diff)
downloadpostgresql-12a47c6aca43e33d42ef946761ec0406476dd1b0.tar.gz
postgresql-12a47c6aca43e33d42ef946761ec0406476dd1b0.zip
Disallow referential integrity actions from being deferred; only the
NO ACTION check is deferrable. This seems to be a closer approximation to what the SQL spec says than what we were doing before, and it prevents some anomalous behaviors that are possible now that triggers can fire during the execution of PL functions. Stephan Szabo.
-rw-r--r--doc/src/sgml/ddl.sgml18
-rw-r--r--doc/src/sgml/ref/create_table.sgml48
-rw-r--r--src/backend/commands/tablecmds.c22
-rw-r--r--src/backend/commands/trigger.c12
4 files changed, 63 insertions, 37 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 547c6e57de4..f50c6e47825 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.30 2004/08/08 21:33:11 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.31 2004/10/21 21:33:59 tgl Exp $ -->
<chapter id="ddl">
<title>Data Definition</title>
@@ -940,11 +940,17 @@ CREATE TABLE order_items (
<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>RESTRICT</literal> prevents a statement from deleting a
+ referenced row. <literal>NO ACTION</literal> means that if any
+ referencing rows still exist when the constraint is checked, an error
+ is raised; this is the default if you do not specify anything.
+ (The essential difference between these choices is that
+ <literal>NO ACTION</literal> allows the check to be deferred until
+ later in the transaction, whereas <literal>RESTRICT</literal> does not.)
+ There are two other options:
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
+ These cause the referencing columns to be set to nulls or default
+ values, respectively, when the referenced row is deleted.
Note that these do not excuse you from observing any constraints.
For example, if an action specifies <literal>SET DEFAULT</literal>
but the default value would not satisfy the foreign key, the
@@ -964,7 +970,7 @@ CREATE TABLE order_items (
<para>
Finally, we should mention that a foreign key must reference
- columns that are either a primary key or form a unique constraint.
+ columns that either are a primary key or form a unique constraint.
If the foreign key references a unique constraint, there are some
additional possibilities regarding how null values are matched.
These are explained in the reference documentation for
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 36396f62ace..e70f0c3dfd0 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.84 2004/08/02 04:25:31 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.85 2004/10/21 21:33:59 tgl Exp $
PostgreSQL documentation
-->
@@ -417,12 +417,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
<listitem>
<para>
- Theses clauses specify a foreign key constraint, which specifies
+ These clauses specify a foreign key constraint, which requires
that a group of one or more columns of the new table must only
- contain values which match against values in the referenced
- column(s) <replaceable class="parameter">refcolumn</replaceable>
- of the referenced table <replaceable
- class="parameter">reftable</replaceable>. If <replaceable
+ contain values that match values in the referenced
+ column(s) of some row of the referenced table. If <replaceable
class="parameter">refcolumn</replaceable> is omitted, the
primary key of the <replaceable
class="parameter">reftable</replaceable> is used. The
@@ -431,7 +429,7 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
</para>
<para>
- A value inserted into these columns is matched against the
+ A value inserted into the referencing column(s) is matched against the
values of the referenced table and referenced columns using the
given match type. There are three match types: <literal>MATCH
FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
@@ -452,7 +450,9 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
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
+ 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>
@@ -461,8 +461,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
<listitem>
<para>
Produce an error indicating that the deletion or update
- would create a foreign key constraint violation. This is
- the default action.
+ would create a foreign key constraint 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,9 +473,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
<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.
+ Produce an error indicating that the deletion or update
+ would create a foreign key constraint violation.
+ This is the same as <literal>NO ACTION</literal> except that
+ the check is not deferrable.
</para>
</listitem>
</varlistentry>
@@ -493,7 +496,7 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
<term><literal>SET NULL</literal></term>
<listitem>
<para>
- Set the referencing column values to null.
+ Set the referencing column(s) to null.
</para>
</listitem>
</varlistentry>
@@ -502,7 +505,7 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
<term><literal>SET DEFAULT</literal></term>
<listitem>
<para>
- Set the referencing column values to their default value.
+ Set the referencing column(s) to their default values.
</para>
</listitem>
</varlistentry>
@@ -510,11 +513,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
</para>
<para>
- If primary key column is updated frequently, it may be wise to
- add an index to the foreign key column so that <literal>NO
- ACTION</literal> and <literal>CASCADE</literal> actions
- associated with the foreign key column can be more efficiently
- performed.
+ If the referenced column(s) are changed frequently, it may be wise to
+ add an index to the foreign key column so that referential actions
+ associated with the foreign key column can be performed more
+ efficiently.
</para>
</listitem>
</varlistentry>
@@ -844,9 +846,9 @@ CREATE TABLE distributors (
<programlisting>
CREATE TABLE cinemas (
- id serial,
- name text,
- location text
+ id serial,
+ name text,
+ location text
) TABLESPACE diskvol1;
</programlisting>
</para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c69bf29d832..463d2506c2e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.135 2004/10/16 21:16:36 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.136 2004/10/21 21:33:59 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -4361,12 +4361,12 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
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->deferrable = fkconstraint->deferrable;
+ fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del");
break;
case FKCONSTR_ACTION_RESTRICT:
@@ -4375,12 +4375,18 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
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:
@@ -4425,12 +4431,12 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
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->deferrable = fkconstraint->deferrable;
+ fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd");
break;
case FKCONSTR_ACTION_RESTRICT:
@@ -4439,12 +4445,18 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
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:
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 5480fce189f..7b1bdddf0ba 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -7,7 +7,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.172 2004/09/10 18:39:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.173 2004/10/21 21:33:59 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2729,11 +2729,17 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
/*
* If we found some, check that they fit the deferrability
- * but skip ON <event> RESTRICT ones, since they are
+ * 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_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,