Skip to content

Commit 086c84b

Browse files
peterepetere
committed
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. Discussion: https://www.postgresql.org/message-id/ea5b2777-266a-46fa-852f-6fca6ec480ad@eisentraut.org
1 parent 2f69645 commit 086c84b

File tree

4 files changed

+57
-35
lines changed

4 files changed

+57
-35
lines changed

‎src/backend/utils/adt/ri_triggers.c

+27-5
Original file line numberDiff line numberDiff line change
@@ -228,14 +228,15 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
228228
RI_QueryKey *qkey, SPIPlanPtr qplan,
229229
Relation fk_rel, Relation pk_rel,
230230
TupleTableSlot *oldslot, TupleTableSlot *newslot,
231+
bool is_restrict,
231232
bool detectNewRows, int expect_OK);
232233
static void ri_ExtractValues(Relation rel, TupleTableSlot *slot,
233234
const RI_ConstraintInfo *riinfo, bool rel_is_pk,
234235
Datum *vals, char *nulls);
235236
static void ri_ReportViolation(const RI_ConstraintInfo *riinfo,
236237
Relation pk_rel, Relation fk_rel,
237238
TupleTableSlot *violatorslot, TupleDesc tupdesc,
238-
int queryno, bool partgone) pg_attribute_noreturn();
239+
int queryno, bool is_restrict, bool partgone) pg_attribute_noreturn();
239240

240241

241242
/*
@@ -449,6 +450,7 @@ RI_FKey_check(TriggerData *trigdata)
449450
ri_PerformCheck(riinfo, &qkey, qplan,
450451
fk_rel, pk_rel,
451452
NULL, newslot,
453+
false,
452454
pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE,
453455
SPI_OK_SELECT);
454456

@@ -613,6 +615,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
613615
result = ri_PerformCheck(riinfo, &qkey, qplan,
614616
fk_rel, pk_rel,
615617
oldslot, NULL,
618+
false,
616619
true, /* treat like update */
617620
SPI_OK_SELECT);
618621

@@ -800,6 +803,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
800803
ri_PerformCheck(riinfo, &qkey, qplan,
801804
fk_rel, pk_rel,
802805
oldslot, NULL,
806+
!is_no_action,
803807
true, /* must detect new rows */
804808
SPI_OK_SELECT);
805809

@@ -901,6 +905,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
901905
ri_PerformCheck(riinfo, &qkey, qplan,
902906
fk_rel, pk_rel,
903907
oldslot, NULL,
908+
false,
904909
true, /* must detect new rows */
905910
SPI_OK_DELETE);
906911

@@ -1017,6 +1022,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
10171022
ri_PerformCheck(riinfo, &qkey, qplan,
10181023
fk_rel, pk_rel,
10191024
oldslot, newslot,
1025+
false,
10201026
true, /* must detect new rows */
10211027
SPI_OK_UPDATE);
10221028

@@ -1244,6 +1250,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
12441250
ri_PerformCheck(riinfo, &qkey, qplan,
12451251
fk_rel, pk_rel,
12461252
oldslot, NULL,
1253+
false,
12471254
true, /* must detect new rows */
12481255
SPI_OK_UPDATE);
12491256

@@ -1690,7 +1697,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
16901697
ri_ReportViolation(&fake_riinfo,
16911698
pk_rel, fk_rel,
16921699
slot, tupdesc,
1693-
RI_PLAN_CHECK_LOOKUPPK, false);
1700+
RI_PLAN_CHECK_LOOKUPPK, false, false);
16941701

16951702
ExecDropSingleTupleTableSlot(slot);
16961703
}
@@ -1906,7 +1913,7 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
19061913
fake_riinfo.pk_attnums[i] = i + 1;
19071914

19081915
ri_ReportViolation(&fake_riinfo, pk_rel, fk_rel,
1909-
slot, tupdesc, 0, true);
1916+
slot, tupdesc, 0, false, true);
19101917
}
19111918

19121919
if (SPI_finish() != SPI_OK_FINISH)
@@ -2387,6 +2394,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
23872394
RI_QueryKey *qkey, SPIPlanPtr qplan,
23882395
Relation fk_rel, Relation pk_rel,
23892396
TupleTableSlot *oldslot, TupleTableSlot *newslot,
2397+
bool is_restrict,
23902398
bool detectNewRows, int expect_OK)
23912399
{
23922400
Relation query_rel,
@@ -2511,7 +2519,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
25112519
pk_rel, fk_rel,
25122520
newslot ? newslot : oldslot,
25132521
NULL,
2514-
qkey->constr_queryno, false);
2522+
qkey->constr_queryno, is_restrict, false);
25152523

25162524
return SPI_processed != 0;
25172525
}
@@ -2552,7 +2560,7 @@ static void
25522560
ri_ReportViolation(const RI_ConstraintInfo *riinfo,
25532561
Relation pk_rel, Relation fk_rel,
25542562
TupleTableSlot *violatorslot, TupleDesc tupdesc,
2555-
int queryno, bool partgone)
2563+
int queryno, bool is_restrict, bool partgone)
25562564
{
25572565
StringInfoData key_names;
25582566
StringInfoData key_values;
@@ -2682,6 +2690,20 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
26822690
errdetail("Key is not present in table \"%s\".",
26832691
RelationGetRelationName(pk_rel)),
26842692
errtableconstraint(fk_rel, NameStr(riinfo->conname))));
2693+
else if (is_restrict)
2694+
ereport(ERROR,
2695+
(errcode(ERRCODE_RESTRICT_VIOLATION),
2696+
errmsg("update or delete on table \"%s\" violates RESTRICT setting of foreign key constraint \"%s\" on table \"%s\"",
2697+
RelationGetRelationName(pk_rel),
2698+
NameStr(riinfo->conname),
2699+
RelationGetRelationName(fk_rel)),
2700+
has_perm ?
2701+
errdetail("Key (%s)=(%s) is referenced from table \"%s\".",
2702+
key_names.data, key_values.data,
2703+
RelationGetRelationName(fk_rel)) :
2704+
errdetail("Key is referenced from table \"%s\".",
2705+
RelationGetRelationName(fk_rel)),
2706+
errtableconstraint(fk_rel, NameStr(riinfo->conname))));
26852707
else
26862708
ereport(ERROR,
26872709
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),

‎src/test/regress/expected/collate.icu.utf8.out

+2-2
Original file line numberDiff line numberDiff line change
@@ -2051,8 +2051,8 @@ CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12p
20512051
INSERT INTO test12pk VALUES ('abc');
20522052
INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC');
20532053
UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- restrict violation
2054-
ERROR: update or delete on table "test12pk" violates foreign key constraint "test12fk_b_fkey" on table "test12fk"
2055-
DETAIL: Key (x)=(abc) is still referenced from table "test12fk".
2054+
ERROR: update or delete on table "test12pk" violates RESTRICT setting of foreign key constraint "test12fk_b_fkey" on table "test12fk"
2055+
DETAIL: Key (x)=(abc) is referenced from table "test12fk".
20562056
SELECT * FROM test12pk;
20572057
x
20582058
-----

‎src/test/regress/expected/foreign_key.out

+8-8
Original file line numberDiff line numberDiff line change
@@ -1439,11 +1439,11 @@ insert into pp values(11);
14391439
update pp set f1=f1+1;
14401440
insert into cc values(13);
14411441
update pp set f1=f1+1; -- fail
1442-
ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"
1443-
DETAIL: Key (f1)=(13) is still referenced from table "cc".
1442+
ERROR: update or delete on table "pp" violates RESTRICT setting of foreign key constraint "cc_f1_fkey" on table "cc"
1443+
DETAIL: Key (f1)=(13) is referenced from table "cc".
14441444
delete from pp where f1 = 13; -- fail
1445-
ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"
1446-
DETAIL: Key (f1)=(13) is still referenced from table "cc".
1445+
ERROR: update or delete on table "pp" violates RESTRICT setting of foreign key constraint "cc_f1_fkey" on table "cc"
1446+
DETAIL: Key (f1)=(13) is referenced from table "cc".
14471447
drop table pp, cc;
14481448
--
14491449
-- 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
26642664
CREATE TABLE fk_d PARTITION OF fk DEFAULT;
26652665
INSERT INTO fk VALUES (20), (30);
26662666
DELETE FROM pk WHERE a = 20;
2667-
ERROR: update or delete on table "pk11" violates foreign key constraint "fk_a_fkey2" on table "fk"
2668-
DETAIL: Key (a)=(20) is still referenced from table "fk".
2667+
ERROR: update or delete on table "pk11" violates RESTRICT setting of foreign key constraint "fk_a_fkey2" on table "fk"
2668+
DETAIL: Key (a)=(20) is referenced from table "fk".
26692669
UPDATE pk SET a = 90 WHERE a = 30;
2670-
ERROR: update or delete on table "pk" violates foreign key constraint "fk_a_fkey" on table "fk"
2671-
DETAIL: Key (a)=(30) is still referenced from table "fk".
2670+
ERROR: update or delete on table "pk" violates RESTRICT setting of foreign key constraint "fk_a_fkey" on table "fk"
2671+
DETAIL: Key (a)=(30) is referenced from table "fk".
26722672
SELECT tableoid::regclass, * FROM fk;
26732673
tableoid | a
26742674
----------+----

‎src/test/regress/expected/without_overlaps.out

+20-20
Original file line numberDiff line numberDiff line change
@@ -1759,23 +1759,23 @@ UPDATE temporal_rng
17591759
SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
17601760
WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
17611761
WHERE id = '[6,7)';
1762-
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1763-
DETAIL: Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
1762+
ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1763+
DETAIL: Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng".
17641764
-- a PK update that fails because both are referenced (even before commit):
17651765
BEGIN;
17661766
ALTER TABLE temporal_fk_rng2rng
17671767
ALTER CONSTRAINT temporal_fk_rng2rng_fk
17681768
DEFERRABLE INITIALLY DEFERRED;
17691769
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
17701770
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
1771-
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1772-
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
1771+
ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1772+
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng".
17731773
ROLLBACK;
17741774
-- changing the scalar part fails:
17751775
UPDATE temporal_rng SET id = '[7,8)'
17761776
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
1777-
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1778-
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
1777+
ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1778+
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng".
17791779
-- then delete the objecting FK record and the same PK update succeeds:
17801780
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
17811781
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
@@ -1840,8 +1840,8 @@ BEGIN;
18401840
ALTER CONSTRAINT temporal_fk_rng2rng_fk
18411841
DEFERRABLE INITIALLY DEFERRED;
18421842
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
1843-
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1844-
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
1843+
ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1844+
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng".
18451845
ROLLBACK;
18461846
-- then delete the objecting FK record and the same PK delete succeeds:
18471847
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
@@ -2243,23 +2243,23 @@ UPDATE temporal_mltrng
22432243
SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05'))
22442244
WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
22452245
WHERE id = '[6,7)';
2246-
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2247-
DETAIL: Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
2246+
ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2247+
DETAIL: Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
22482248
-- a PK update that fails because both are referenced (even before commit):
22492249
BEGIN;
22502250
ALTER TABLE temporal_fk_mltrng2mltrng
22512251
ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
22522252
DEFERRABLE INITIALLY DEFERRED;
22532253
UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01'))
22542254
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
2255-
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2256-
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
2255+
ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2256+
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
22572257
ROLLBACK;
22582258
-- changing the scalar part fails:
22592259
UPDATE temporal_mltrng SET id = '[7,8)'
22602260
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
2261-
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2262-
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
2261+
ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2262+
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
22632263
--
22642264
-- test FK referenced deletes NO ACTION
22652265
--
@@ -2317,8 +2317,8 @@ BEGIN;
23172317
ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
23182318
DEFERRABLE INITIALLY DEFERRED;
23192319
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
2320-
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2321-
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
2320+
ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2321+
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
23222322
ROLLBACK;
23232323
--
23242324
-- FK between partitioned tables: ranges
@@ -2432,8 +2432,8 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[
24322432
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
24332433
-- should fail:
24342434
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
2435-
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"
2436-
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
2435+
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"
2436+
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_partitioned_fk_rng2rng".
24372437
--
24382438
-- partitioned FK referenced updates CASCADE
24392439
--
@@ -2588,8 +2588,8 @@ INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALU
25882588
DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
25892589
-- should fail:
25902590
DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
2591-
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"
2592-
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_partitioned_fk_mltrng2mltrng".
2591+
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"
2592+
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_partitioned_fk_mltrng2mltrng".
25932593
--
25942594
-- partitioned FK referenced updates CASCADE
25952595
--

0 commit comments

Comments
 (0)