Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit12a47c6

Browse files
committed
Disallow referential integrity actions from being deferred; only the
NO ACTION check is deferrable. This seems to be a closer approximationto what the SQL spec says than what we were doing before, and it preventssome anomalous behaviors that are possible now that triggers can fireduring the execution of PL functions.Stephan Szabo.
1 parent7627b91 commit12a47c6

File tree

4 files changed

+63
-37
lines changed

4 files changed

+63
-37
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.30 2004/08/08 21:33:11 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.31 2004/10/21 21:33:59 tgl Exp $ -->
22

33
<chapter id="ddl">
44
<title>Data Definition</title>
@@ -940,11 +940,17 @@ CREATE TABLE order_items (
940940

941941
<para>
942942
Restricting and cascading deletes are the two most common options.
943-
<literal>RESTRICT</literal> can also be written as <literal>NO
944-
ACTION</literal> and it's also the default if you do not specify
945-
anything. There are two other options for what should happen with
946-
the foreign key columns when a primary key is deleted:
943+
<literal>RESTRICT</literal> prevents a statement from deleting a
944+
referenced row. <literal>NO ACTION</literal> means that if any
945+
referencing rows still exist when the constraint is checked, an error
946+
is raised; this is the default if you do not specify anything.
947+
(The essential difference between these choices is that
948+
<literal>NO ACTION</literal> allows the check to be deferred until
949+
later in the transaction, whereas <literal>RESTRICT</literal> does not.)
950+
There are two other options:
947951
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
952+
These cause the referencing columns to be set to nulls or default
953+
values, respectively, when the referenced row is deleted.
948954
Note that these do not excuse you from observing any constraints.
949955
For example, if an action specifies <literal>SET DEFAULT</literal>
950956
but the default value would not satisfy the foreign key, the
@@ -964,7 +970,7 @@ CREATE TABLE order_items (
964970

965971
<para>
966972
Finally, we should mention that a foreign key must reference
967-
columns thatareeither a primary key or form a unique constraint.
973+
columns that either are a primary key or form a unique constraint.
968974
If the foreign key references a unique constraint, there are some
969975
additional possibilities regarding how null values are matched.
970976
These are explained in the reference documentation for

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 25 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.84 2004/08/02 04:25:31 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.85 2004/10/21 21:33:59 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -417,12 +417,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
417417

418418
<listitem>
419419
<para>
420-
Theses clauses specify a foreign key constraint, whichspecifies
420+
These clauses specify a foreign key constraint, whichrequires
421421
that a group of one or more columns of the new table must only
422-
contain values which match against values in the referenced
423-
column(s) <replaceable class="parameter">refcolumn</replaceable>
424-
of the referenced table <replaceable
425-
class="parameter">reftable</replaceable>. If <replaceable
422+
contain values that match values in the referenced
423+
column(s) of some row of the referenced table. If <replaceable
426424
class="parameter">refcolumn</replaceable> is omitted, the
427425
primary key of the <replaceable
428426
class="parameter">reftable</replaceable> is used. The
@@ -431,7 +429,7 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
431429
</para>
432430

433431
<para>
434-
A value inserted intothese columns is matched against the
432+
A value inserted intothe referencing column(s) is matched against the
435433
values of the referenced table and referenced columns using the
436434
given match type. There are three match types: <literal>MATCH
437435
FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
@@ -452,7 +450,9 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
452450
clause specifies the action to perform when a referenced column
453451
in the referenced table is being updated to a new value. If the
454452
row is updated, but the referenced column is not actually
455-
changed, no action is done. There are the following possible
453+
changed, no action is done. Referential actions apart from the
454+
check of <literal>NO ACTION</literal> can not be deferred even if
455+
the constraint is deferrable. There are the following possible
456456
actions for each clause:
457457

458458
<variablelist>
@@ -461,8 +461,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
461461
<listitem>
462462
<para>
463463
Produce an error indicating that the deletion or update
464-
would create a foreign key constraint violation. This is
465-
the default action.
464+
would create a foreign key constraint violation.
465+
If the constraint is deferred, this
466+
error will be produced at constraint check time if there still
467+
exist any referencing rows. This is the default action.
466468
</para>
467469
</listitem>
468470
</varlistentry>
@@ -471,9 +473,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
471473
<term><literal>RESTRICT</literal></term>
472474
<listitem>
473475
<para>
474-
Same as <literal>NO ACTION</literal> except that this action
475-
will not be deferred even if the rest of the constraint is
476-
deferrable and deferred.
476+
Produce an error indicating that the deletion or update
477+
would create a foreign key constraint violation.
478+
This is the same as <literal>NO ACTION</literal> except that
479+
the check is not deferrable.
477480
</para>
478481
</listitem>
479482
</varlistentry>
@@ -493,7 +496,7 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
493496
<term><literal>SET NULL</literal></term>
494497
<listitem>
495498
<para>
496-
Set the referencing column values to null.
499+
Set the referencing column(s) to null.
497500
</para>
498501
</listitem>
499502
</varlistentry>
@@ -502,19 +505,18 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
502505
<term><literal>SET DEFAULT</literal></term>
503506
<listitem>
504507
<para>
505-
Set the referencing column valuesto their defaultvalue.
508+
Set the referencing column(s)to their defaultvalues.
506509
</para>
507510
</listitem>
508511
</varlistentry>
509512
</variablelist>
510513
</para>
511514

512515
<para>
513-
If primary key column is updated frequently, it may be wise to
514-
add an index to the foreign key column so that <literal>NO
515-
ACTION</literal> and <literal>CASCADE</literal> actions
516-
associated with the foreign key column can be more efficiently
517-
performed.
516+
If the referenced column(s) are changed frequently, it may be wise to
517+
add an index to the foreign key column so that referential actions
518+
associated with the foreign key column can be performed more
519+
efficiently.
518520
</para>
519521
</listitem>
520522
</varlistentry>
@@ -844,9 +846,9 @@ CREATE TABLE distributors (
844846

845847
<programlisting>
846848
CREATE TABLE cinemas (
847-
id serial,
848-
name text,
849-
location text
849+
id serial,
850+
name text,
851+
location text
850852
) TABLESPACE diskvol1;
851853
</programlisting>
852854
</para>

‎src/backend/commands/tablecmds.c

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.135 2004/10/16 21:16:36 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.136 2004/10/21 21:33:59 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -4361,12 +4361,12 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
43614361
fk_trigger->actions[1]='\0';
43624362

43634363
fk_trigger->isconstraint= true;
4364-
fk_trigger->deferrable=fkconstraint->deferrable;
4365-
fk_trigger->initdeferred=fkconstraint->initdeferred;
43664364
fk_trigger->constrrel=myRel;
43674365
switch (fkconstraint->fk_del_action)
43684366
{
43694367
caseFKCONSTR_ACTION_NOACTION:
4368+
fk_trigger->deferrable=fkconstraint->deferrable;
4369+
fk_trigger->initdeferred=fkconstraint->initdeferred;
43704370
fk_trigger->funcname=SystemFuncName("RI_FKey_noaction_del");
43714371
break;
43724372
caseFKCONSTR_ACTION_RESTRICT:
@@ -4375,12 +4375,18 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
43754375
fk_trigger->funcname=SystemFuncName("RI_FKey_restrict_del");
43764376
break;
43774377
caseFKCONSTR_ACTION_CASCADE:
4378+
fk_trigger->deferrable= false;
4379+
fk_trigger->initdeferred= false;
43784380
fk_trigger->funcname=SystemFuncName("RI_FKey_cascade_del");
43794381
break;
43804382
caseFKCONSTR_ACTION_SETNULL:
4383+
fk_trigger->deferrable= false;
4384+
fk_trigger->initdeferred= false;
43814385
fk_trigger->funcname=SystemFuncName("RI_FKey_setnull_del");
43824386
break;
43834387
caseFKCONSTR_ACTION_SETDEFAULT:
4388+
fk_trigger->deferrable= false;
4389+
fk_trigger->initdeferred= false;
43844390
fk_trigger->funcname=SystemFuncName("RI_FKey_setdefault_del");
43854391
break;
43864392
default:
@@ -4425,12 +4431,12 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
44254431
fk_trigger->actions[0]='u';
44264432
fk_trigger->actions[1]='\0';
44274433
fk_trigger->isconstraint= true;
4428-
fk_trigger->deferrable=fkconstraint->deferrable;
4429-
fk_trigger->initdeferred=fkconstraint->initdeferred;
44304434
fk_trigger->constrrel=myRel;
44314435
switch (fkconstraint->fk_upd_action)
44324436
{
44334437
caseFKCONSTR_ACTION_NOACTION:
4438+
fk_trigger->deferrable=fkconstraint->deferrable;
4439+
fk_trigger->initdeferred=fkconstraint->initdeferred;
44344440
fk_trigger->funcname=SystemFuncName("RI_FKey_noaction_upd");
44354441
break;
44364442
caseFKCONSTR_ACTION_RESTRICT:
@@ -4439,12 +4445,18 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
44394445
fk_trigger->funcname=SystemFuncName("RI_FKey_restrict_upd");
44404446
break;
44414447
caseFKCONSTR_ACTION_CASCADE:
4448+
fk_trigger->deferrable= false;
4449+
fk_trigger->initdeferred= false;
44424450
fk_trigger->funcname=SystemFuncName("RI_FKey_cascade_upd");
44434451
break;
44444452
caseFKCONSTR_ACTION_SETNULL:
4453+
fk_trigger->deferrable= false;
4454+
fk_trigger->initdeferred= false;
44454455
fk_trigger->funcname=SystemFuncName("RI_FKey_setnull_upd");
44464456
break;
44474457
caseFKCONSTR_ACTION_SETDEFAULT:
4458+
fk_trigger->deferrable= false;
4459+
fk_trigger->initdeferred= false;
44484460
fk_trigger->funcname=SystemFuncName("RI_FKey_setdefault_upd");
44494461
break;
44504462
default:

‎src/backend/commands/trigger.c

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.172 2004/09/10 18:39:56 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.173 2004/10/21 21:33:59 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -2729,11 +2729,17 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
27292729

27302730
/*
27312731
* If we found some, check that they fit the deferrability
2732-
* but skipON <event> RESTRICT ones, since they are
2732+
* but skipreferential action ones, since they are
27332733
* silently never deferrable.
27342734
*/
27352735
if (pg_trigger->tgfoid!=F_RI_FKEY_RESTRICT_UPD&&
2736-
pg_trigger->tgfoid!=F_RI_FKEY_RESTRICT_DEL)
2736+
pg_trigger->tgfoid!=F_RI_FKEY_RESTRICT_DEL&&
2737+
pg_trigger->tgfoid!=F_RI_FKEY_CASCADE_UPD&&
2738+
pg_trigger->tgfoid!=F_RI_FKEY_CASCADE_DEL&&
2739+
pg_trigger->tgfoid!=F_RI_FKEY_SETNULL_UPD&&
2740+
pg_trigger->tgfoid!=F_RI_FKEY_SETNULL_DEL&&
2741+
pg_trigger->tgfoid!=F_RI_FKEY_SETDEFAULT_UPD&&
2742+
pg_trigger->tgfoid!=F_RI_FKEY_SETDEFAULT_DEL)
27372743
{
27382744
if (stmt->deferred&& !pg_trigger->tgdeferrable)
27392745
ereport(ERROR,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp