On 14.11.24 09:04, Peter Eisentraut wrote:
You can also reproduce this with things that are not strings with
collations. You just need to find a type that has values that are
"equal" but "distinct", which is not common, but it exists, for example
0.0 and -0.0 in floats. Example:
create table parent (val float8 primary key);
insert into parent values ('0.0');
create table child (id int, val float8 references parent (val));
insert into child values (1, '0.0');
insert into child values (2, '-0.0');
update parent set val = '-0.0'; -- ok with NO ACTION
but
create table child (id int, val float8 references parent (val) on
update restrict);
insert into child values (1, '0.0');
insert into child values (2, '-0.0');
update parent set val = '-0.0'; -- error with RESTRICT
So this is a meaningful difference.
There is also a bug here in that the update in the case of NO ACTION
doesn't actually run, because it thinks the values are the same and the
update can be skipped.
I think there is room for improvement here, in the documentation, the
tests, and maybe in the code. And while these are thematically related
to this thread, they are actually separate issues.
Back to this. First, there is no bug above. This is all working
correctly, I was just confused.
I made a few patches to clarify this:
1. We were using the wrong error code for RESTRICT. A RESTRICT
violation is not the same as a foreign-key violation. (The foreign key
might in theory still be satisfied, but RESTRICT prevents the action
anyway.) I fixed that.
2. Added some tests to illustrate all of this (similar to above). I
used case-insensitive collations, which I think is easiest to
understand, but there is nothing special about that.
3. Some documentation updates to explain some of the differences between
NO ACTION and RESTRICT better.
From 1e6034fb09b1aab0eddf24b720ff040758db59fb Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 19 Nov 2024 17:11:50 +0100
Subject: [PATCH 1/3] Fix error code for referential action RESTRICT
According to the SQL standard, if the referential action RESTRICT is
triggered, it has its own error code. We previously didn't use that,
we just used the error code for foreign key violation. But RESTRICT
is not necessarily an actual foreign key violation. The foreign key
might still be satisfied in theory afterwards, but the RESTRICT
setting prevents the action even then. So it's a separate kind of
error condition.
---
src/backend/utils/adt/ri_triggers.c | 32 ++++++++++++---
src/test/regress/expected/foreign_key.out | 16 ++++----
.../regress/expected/without_overlaps.out | 40 +++++++++----------
3 files changed, 55 insertions(+), 33 deletions(-)
diff --git a/src/backend/utils/adt/ri_triggers.c
b/src/backend/utils/adt/ri_triggers.c
index 91792cb2a47..3185f48afa6 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -228,6 +228,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
RI_QueryKey *qkey,
SPIPlanPtr qplan,
Relation fk_rel,
Relation pk_rel,
TupleTableSlot
*oldslot, TupleTableSlot *newslot,
+ bool is_restrict,
bool detectNewRows, int
expect_OK);
static void ri_ExtractValues(Relation rel, TupleTableSlot *slot,
const
RI_ConstraintInfo *riinfo, bool rel_is_pk,
@@ -235,7 +236,7 @@ static void ri_ExtractValues(Relation rel, TupleTableSlot
*slot,
static void ri_ReportViolation(const RI_ConstraintInfo *riinfo,
Relation pk_rel,
Relation fk_rel,
TupleTableSlot
*violatorslot, TupleDesc tupdesc,
- int queryno, bool
partgone) pg_attribute_noreturn();
+ int queryno, bool
is_restrict, bool partgone) pg_attribute_noreturn();
/*
@@ -449,6 +450,7 @@ RI_FKey_check(TriggerData *trigdata)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
NULL, newslot,
+ false,
pk_rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE,
SPI_OK_SELECT);
@@ -613,6 +615,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
result = ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ false,
true, /* treat like
update */
SPI_OK_SELECT);
@@ -800,6 +803,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ !is_no_action,
true, /* must detect new rows
*/
SPI_OK_SELECT);
@@ -901,6 +905,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ false,
true, /* must detect new rows
*/
SPI_OK_DELETE);
@@ -1017,6 +1022,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, newslot,
+ false,
true, /* must detect new rows
*/
SPI_OK_UPDATE);
@@ -1244,6 +1250,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int
tgkind)
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
oldslot, NULL,
+ false,
true, /* must detect new rows
*/
SPI_OK_UPDATE);
@@ -1690,7 +1697,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel,
Relation pk_rel)
ri_ReportViolation(&fake_riinfo,
pk_rel, fk_rel,
slot, tupdesc,
- RI_PLAN_CHECK_LOOKUPPK,
false);
+ RI_PLAN_CHECK_LOOKUPPK,
false, false);
ExecDropSingleTupleTableSlot(slot);
}
@@ -1906,7 +1913,7 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation
fk_rel, Relation pk_rel)
fake_riinfo.pk_attnums[i] = i + 1;
ri_ReportViolation(&fake_riinfo, pk_rel, fk_rel,
- slot, tupdesc, 0, true);
+ slot, tupdesc, 0, false,
true);
}
if (SPI_finish() != SPI_OK_FINISH)
@@ -2387,6 +2394,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
RI_QueryKey *qkey, SPIPlanPtr qplan,
Relation fk_rel, Relation pk_rel,
TupleTableSlot *oldslot, TupleTableSlot
*newslot,
+ bool is_restrict,
bool detectNewRows, int expect_OK)
{
Relation query_rel,
@@ -2511,7 +2519,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
pk_rel, fk_rel,
newslot ? newslot : oldslot,
NULL,
- qkey->constr_queryno, false);
+ qkey->constr_queryno,
is_restrict, false);
return SPI_processed != 0;
}
@@ -2552,7 +2560,7 @@ static void
ri_ReportViolation(const RI_ConstraintInfo *riinfo,
Relation pk_rel, Relation fk_rel,
TupleTableSlot *violatorslot, TupleDesc
tupdesc,
- int queryno, bool partgone)
+ int queryno, bool is_restrict, bool partgone)
{
StringInfoData key_names;
StringInfoData key_values;
@@ -2682,6 +2690,20 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
errdetail("Key is not present in table
\"%s\".",
RelationGetRelationName(pk_rel)),
errtableconstraint(fk_rel,
NameStr(riinfo->conname))));
+ else if (is_restrict)
+ ereport(ERROR,
+ (errcode(ERRCODE_RESTRICT_VIOLATION),
+ errmsg("update or delete on table \"%s\"
violates RESTRICT setting of foreign key constraint \"%s\" on table \"%s\"",
+ RelationGetRelationName(pk_rel),
+ NameStr(riinfo->conname),
+
RelationGetRelationName(fk_rel)),
+ has_perm ?
+ errdetail("Key (%s)=(%s) is referenced from
table \"%s\".",
+ key_names.data,
key_values.data,
+
RelationGetRelationName(fk_rel)) :
+ errdetail("Key is referenced from table
\"%s\".",
+
RelationGetRelationName(fk_rel)),
+ errtableconstraint(fk_rel,
NameStr(riinfo->conname))));
else
ereport(ERROR,
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
diff --git a/src/test/regress/expected/foreign_key.out
b/src/test/regress/expected/foreign_key.out
index a5165270c2d..3f459f70ac1 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1439,11 +1439,11 @@ insert into pp values(11);
update pp set f1=f1+1;
insert into cc values(13);
update pp set f1=f1+1; -- fail
-ERROR: update or delete on table "pp" violates foreign key constraint
"cc_f1_fkey" on table "cc"
-DETAIL: Key (f1)=(13) is still referenced from table "cc".
+ERROR: update or delete on table "pp" violates RESTRICT setting of foreign
key constraint "cc_f1_fkey" on table "cc"
+DETAIL: Key (f1)=(13) is referenced from table "cc".
delete from pp where f1 = 13; -- fail
-ERROR: update or delete on table "pp" violates foreign key constraint
"cc_f1_fkey" on table "cc"
-DETAIL: Key (f1)=(13) is still referenced from table "cc".
+ERROR: update or delete on table "pp" violates RESTRICT setting of foreign
key constraint "cc_f1_fkey" on table "cc"
+DETAIL: Key (f1)=(13) is referenced from table "cc".
drop table pp, cc;
--
-- Test interaction of foreign-key optimization with rules (bug #14219)
@@ -2664,11 +2664,11 @@ ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk ON
UPDATE RESTRICT ON DELETE RE
CREATE TABLE fk_d PARTITION OF fk DEFAULT;
INSERT INTO fk VALUES (20), (30);
DELETE FROM pk WHERE a = 20;
-ERROR: update or delete on table "pk11" violates foreign key constraint
"fk_a_fkey2" on table "fk"
-DETAIL: Key (a)=(20) is still referenced from table "fk".
+ERROR: update or delete on table "pk11" violates RESTRICT setting of foreign
key constraint "fk_a_fkey2" on table "fk"
+DETAIL: Key (a)=(20) is referenced from table "fk".
UPDATE pk SET a = 90 WHERE a = 30;
-ERROR: update or delete on table "pk" violates foreign key constraint
"fk_a_fkey" on table "fk"
-DETAIL: Key (a)=(30) is still referenced from table "fk".
+ERROR: update or delete on table "pk" violates RESTRICT setting of foreign
key constraint "fk_a_fkey" on table "fk"
+DETAIL: Key (a)=(30) is referenced from table "fk".
SELECT tableoid::regclass, * FROM fk;
tableoid | a
----------+----
diff --git a/src/test/regress/expected/without_overlaps.out
b/src/test/regress/expected/without_overlaps.out
index d6cb65e9a63..a621c2e9bbc 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1743,8 +1743,8 @@ UPDATE temporal_rng
SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN
daterange('2018-01-01', '2018-01-05')
WHEN lower(valid_at) = '2018-02-01' THEN
daterange('2018-01-05', '2018-03-01') END
WHERE id = '[6,7)';
-ERROR: update or delete on table "temporal_rng" violates foreign key
constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
-DETAIL: Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is still
referenced from table "temporal_fk_rng2rng".
+ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of
foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is referenced
from table "temporal_fk_rng2rng".
-- a PK update that fails because both are referenced (even before commit):
BEGIN;
ALTER TABLE temporal_fk_rng2rng
@@ -1752,14 +1752,14 @@ BEGIN;
DEFERRABLE INITIALLY DEFERRED;
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR: update or delete on table "temporal_rng" violates foreign key
constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
-DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still
referenced from table "temporal_fk_rng2rng".
+ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of
foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced
from table "temporal_fk_rng2rng".
ROLLBACK;
-- changing the scalar part fails:
UPDATE temporal_rng SET id = '[7,8)'
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR: update or delete on table "temporal_rng" violates foreign key
constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
-DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still
referenced from table "temporal_fk_rng2rng".
+ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of
foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced
from table "temporal_fk_rng2rng".
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
@@ -1824,8 +1824,8 @@ BEGIN;
ALTER CONSTRAINT temporal_fk_rng2rng_fk
DEFERRABLE INITIALLY DEFERRED;
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at =
daterange('2018-01-01', '2018-02-01');
-ERROR: update or delete on table "temporal_rng" violates foreign key
constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
-DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still
referenced from table "temporal_fk_rng2rng".
+ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of
foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced
from table "temporal_fk_rng2rng".
ROLLBACK;
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
@@ -2227,8 +2227,8 @@ UPDATE temporal_mltrng
SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN
datemultirange(daterange('2018-01-01', '2018-01-05'))
WHEN lower(valid_at) = '2018-02-01' THEN
datemultirange(daterange('2018-01-05', '2018-03-01')) END
WHERE id = '[6,7)';
-ERROR: update or delete on table "temporal_mltrng" violates foreign key
constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
-DETAIL: Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is still
referenced from table "temporal_fk_mltrng2mltrng".
+ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting
of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table
"temporal_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is referenced
from table "temporal_fk_mltrng2mltrng".
-- a PK update that fails because both are referenced (even before commit):
BEGIN;
ALTER TABLE temporal_fk_mltrng2mltrng
@@ -2236,14 +2236,14 @@ BEGIN;
DEFERRABLE INITIALLY DEFERRED;
UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01',
'2016-02-01'))
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01',
'2018-02-01'));
-ERROR: update or delete on table "temporal_mltrng" violates foreign key
constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
-DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still
referenced from table "temporal_fk_mltrng2mltrng".
+ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting
of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table
"temporal_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced
from table "temporal_fk_mltrng2mltrng".
ROLLBACK;
-- changing the scalar part fails:
UPDATE temporal_mltrng SET id = '[7,8)'
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01',
'2018-02-01'));
-ERROR: update or delete on table "temporal_mltrng" violates foreign key
constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
-DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still
referenced from table "temporal_fk_mltrng2mltrng".
+ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting
of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table
"temporal_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced
from table "temporal_fk_mltrng2mltrng".
--
-- test FK referenced deletes NO ACTION
--
@@ -2301,8 +2301,8 @@ BEGIN;
ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
DEFERRABLE INITIALLY DEFERRED;
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at =
datemultirange(daterange('2018-01-01', '2018-02-01'));
-ERROR: update or delete on table "temporal_mltrng" violates foreign key
constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
-DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still
referenced from table "temporal_fk_mltrng2mltrng".
+ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting
of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table
"temporal_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced
from table "temporal_fk_mltrng2mltrng".
ROLLBACK;
--
-- FK between partitioned tables: ranges
@@ -2416,8 +2416,8 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id,
valid_at, parent_id) VALUES ('[
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at =
daterange('2018-02-01', '2018-03-01');
-- should fail:
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at =
daterange('2018-01-01', '2018-02-01');
-ERROR: update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table
"temporal_partitioned_fk_rng2rng"
-DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still
referenced from table "temporal_partitioned_fk_rng2rng".
+ERROR: update or delete on table "tp1" violates RESTRICT setting of foreign
key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on
table "temporal_partitioned_fk_rng2rng"
+DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced
from table "temporal_partitioned_fk_rng2rng".
--
-- partitioned FK referenced updates CASCADE
--
@@ -2572,8 +2572,8 @@ INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id,
valid_at, parent_id) VALU
DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at =
datemultirange(daterange('2018-02-01', '2018-03-01'));
-- should fail:
DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at =
datemultirange(daterange('2018-01-01', '2018-02-01'));
-ERROR: update or delete on table "tp1" violates foreign key constraint
"temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1" on table
"temporal_partitioned_fk_mltrng2mltrng"
-DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still
referenced from table "temporal_partitioned_fk_mltrng2mltrng".
+ERROR: update or delete on table "tp1" violates RESTRICT setting of foreign
key constraint "temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1"
on table "temporal_partitioned_fk_mltrng2mltrng"
+DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced
from table "temporal_partitioned_fk_mltrng2mltrng".
--
-- partitioned FK referenced updates CASCADE
--
base-commit: c1c09007e219ae68d1f8428a54baf68ccc1f8683
--
2.47.0
From 9156d35ede18d63264a9629ebeec409d635d8178 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 19 Nov 2024 17:15:28 +0100
Subject: [PATCH 2/3] Add tests for foreign keys with case-insensitive
collations
Some of the behaviors of the different referential actions, such as
the difference between NO ACTION and RESTRICT are best illustrated
using a case-insensitive collation. So add some tests for that.
(What is actually being tested here is the behavior with values that
are "distinct" (binary different) but compare as equal. Another way
to do that would be with positive and negative zeroes with float
types. But this way seems nicer and more flexible.)
---
.../regress/expected/collate.icu.utf8.out | 62 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 30 +++++++++
2 files changed, 92 insertions(+)
diff --git a/src/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index de17f7db6ce..fa08a454944 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1849,6 +1849,68 @@ CREATE TABLE test11pk (x text COLLATE case_insensitive
PRIMARY KEY);
CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x)
ON UPDATE CASCADE ON DELETE CASCADE); -- error
ERROR: foreign key constraint "test11fk_x_fkey" cannot be implemented
DETAIL: Key columns "x" of the referencing table and "x" of the referenced
table have incompatible collations: "case_sensitive" and "case_insensitive".
If either collation is nondeterministic, then both collations have to be the
same.
+-- foreign key actions
+-- Some of the behaviors are most easily visible with a
+-- case-insensitive collation.
+CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES
test12pk (x) ON UPDATE NO ACTION);
+INSERT INTO test12pk VALUES ('abc');
+INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
+UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok
+SELECT * FROM test12pk;
+ x
+-----
+ ABC
+(1 row)
+
+SELECT * FROM test12fk; -- no updates here
+ a | b
+---+-----
+ 1 | abc
+ 2 | ABC
+(2 rows)
+
+DROP TABLE test12pk, test12fk;
+CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES
test12pk (x) ON UPDATE RESTRICT);
+INSERT INTO test12pk VALUES ('abc');
+INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
+UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- restrict violation
+ERROR: update or delete on table "test12pk" violates RESTRICT setting of
foreign key constraint "test12fk_b_fkey" on table "test12fk"
+DETAIL: Key (x)=(abc) is referenced from table "test12fk".
+SELECT * FROM test12pk;
+ x
+-----
+ abc
+(1 row)
+
+SELECT * FROM test12fk;
+ a | b
+---+-----
+ 1 | abc
+ 2 | ABC
+(2 rows)
+
+DROP TABLE test12pk, test12fk;
+CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES
test12pk (x) ON UPDATE CASCADE);
+INSERT INTO test12pk VALUES ('abc');
+INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
+UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok
+SELECT * FROM test12pk;
+ x
+-----
+ ABC
+(1 row)
+
+SELECT * FROM test12fk; -- was updated
+ a | b
+---+-----
+ 1 | ABC
+ 2 | ABC
+(2 rows)
+
+DROP TABLE test12pk, test12fk;
-- partitioning
CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST
(b);
CREATE TABLE test20_1 PARTITION OF test20 FOR VALUES IN ('abc');
diff --git a/src/test/regress/sql/collate.icu.utf8.sql
b/src/test/regress/sql/collate.icu.utf8.sql
index 0c9491c260e..512b57bddc9 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -694,6 +694,36 @@ CREATE TABLE test10fk (x text COLLATE case_insensitive
REFERENCES test10pk (x) O
CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x)
ON UPDATE CASCADE ON DELETE CASCADE); -- error
+-- foreign key actions
+-- Some of the behaviors are most easily visible with a
+-- case-insensitive collation.
+CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES
test12pk (x) ON UPDATE NO ACTION);
+INSERT INTO test12pk VALUES ('abc');
+INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
+UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok
+SELECT * FROM test12pk;
+SELECT * FROM test12fk; -- no updates here
+DROP TABLE test12pk, test12fk;
+
+CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES
test12pk (x) ON UPDATE RESTRICT);
+INSERT INTO test12pk VALUES ('abc');
+INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
+UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- restrict violation
+SELECT * FROM test12pk;
+SELECT * FROM test12fk;
+DROP TABLE test12pk, test12fk;
+
+CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES
test12pk (x) ON UPDATE CASCADE);
+INSERT INTO test12pk VALUES ('abc');
+INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
+UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok
+SELECT * FROM test12pk;
+SELECT * FROM test12fk; -- was updated
+DROP TABLE test12pk, test12fk;
+
-- partitioning
CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST
(b);
CREATE TABLE test20_1 PARTITION OF test20 FOR VALUES IN ('abc');
--
2.47.0
From 64476388bb806352b96b64d13df5718af6ac8415 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 19 Nov 2024 17:18:39 +0100
Subject: [PATCH 3/3] doc: Improve description of referential actions
Some of the differences between NO ACTION and RESTRICT were not
explained fully.
---
doc/src/sgml/ddl.sgml | 41 ++++++++++++++++++++++++------
doc/src/sgml/ref/create_table.sgml | 20 ++++++++-------
2 files changed, 44 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 3c56610d2ac..203d6ce0c30 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1233,16 +1233,32 @@ <title>Foreign Keys</title>
</para>
<para>
- Restricting and cascading deletes are the two most common options.
- <literal>RESTRICT</literal> prevents deletion of 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 behavior if you do not specify anything.
- (The essential difference between these two choices is that
- <literal>NO ACTION</literal> allows the check to be deferred until
- later in the transaction, whereas <literal>RESTRICT</literal> does not.)
+ The default <literal>ON DELETE</literal> action is <literal>ON DELETE NO
+ ACTION</literal>; this does not need to be specified. This means that the
+ deletion in the referenced table is allowed to proceed. But the
+ foreign-key constraint is still required to be satisfied, so this
+ operation will usually result in an error. But checking of foreign-key
+ constraints can also be deferred to later in the transaction (not covered
+ in this chapter). In that case, the <literal>NO ACTION</literal> setting
+ would allow other commands to <quote>fix</quote> the situation before the
+ constraint is checked, for example by inserting another suitable row into
+ the referenced table or by deleting the now-dangling rows from the
+ referencing table.
+ </para>
+
+ <para>
+ <literal>RESTRICT</literal> is a stricter setting than <literal>NO
+ ACTION</literal>. It prevents deletion of a referenced row.
+ <literal>RESTRICT</literal> does not allow the check to be deferred until
+ later in the transaction.
+ </para>
+
+ <para>
<literal>CASCADE</literal> specifies that when a referenced row is deleted,
row(s) referencing it should be automatically deleted as well.
+ </para>
+
+ <para>
There are two other options:
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
These cause the referencing column(s) in the referencing row(s)
@@ -1312,6 +1328,15 @@ <title>Foreign Keys</title>
NULL</literal> and <literal>SET DEFAULT</literal>.
In this case, <literal>CASCADE</literal> means that the updated values of
the
referenced column(s) should be copied into the referencing row(s).
+ There is also a noticeable difference between <literal>ON UPDATE NO
+ ACTION</literal> (the default) and <literal>NO UPDATE RESTRICT</literal>.
+ The former will allow the update to proceed and the foreign-key constraint
+ will be checked against the state after the update. The latter will
+ prevent the update to run even if the state after the update would still
+ satisfy the constraint. This prevents updating a referenced row to a
+ value that is distinct but compares as equal (for example, a character
+ string with a different case variant, if a character string type with a
+ case-insensitive collation is used).
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 1a1adc5ae87..70fa929caa4 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1248,17 +1248,16 @@ <title>Parameters</title>
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 other than the
- <literal>NO ACTION</literal> check cannot be deferred, even if
- the constraint is declared deferrable. There are the following possible
- actions for each clause:
+ changed, no action is done. Referential actions are executed as part of
+ the data changing command, even if the constraint is deferred. There
+ are the following possible actions for each clause:
<variablelist>
<varlistentry id="sql-createtable-parms-references-refact-no-action">
<term><literal>NO ACTION</literal></term>
<listitem>
<para>
- Produce an error indicating that the deletion or update
+ Produce an error if the deletion or update
would create a foreign key constraint violation.
If the constraint is deferred, this
error will be produced at constraint check time if there still
@@ -1271,10 +1270,13 @@ <title>Parameters</title>
<term><literal>RESTRICT</literal></term>
<listitem>
<para>
- 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.
+ Produce an error if a row to be deleted or updated matches a row in
+ the referencing table. This prevents the action even if the state
+ after the action would not violate the foreign key constraint. In
+ particular, it prevents updates of referenced rows to values that
+ are distinct but compare as equal. (But it does not prevent
+ <quote>no-op</quote> updates that update a column to the same
+ value.)
</para>
</listitem>
</varlistentry>
--
2.47.0
