Skip to content

Commit e8c9fd5

Browse files
tglsfdctglsfdc
committed
Allow ON UPDATE/DELETE SET DEFAULT plans to be cached.
Once upon a time, somebody was worried that cached RI plans wouldn't get remade with new default values after ALTER TABLE ... SET DEFAULT, so they didn't allow caching of plans for ON UPDATE/DELETE SET DEFAULT actions. That time is long gone, though (and even at the time I doubt this was the greatest hazard posed by ALTER TABLE...). So allow these triggers to cache their plans just like the others. The cache_plan argument to ri_PlanCheck is now vestigial, since there are no callers that don't pass "true"; but I left it alone in case there is any future need for it.
1 parent 03a5ba2 commit e8c9fd5

File tree

3 files changed

+65
-12
lines changed

3 files changed

+65
-12
lines changed

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

+12-12
Original file line numberDiff line numberDiff line change
@@ -2155,12 +2155,12 @@ RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
21552155
elog(ERROR, "SPI_connect failed");
21562156

21572157
/*
2158-
* Prepare a plan for the set default delete operation.
2159-
* Unfortunately we need to do it on every invocation because the
2160-
* default value could potentially change between calls.
2158+
* Fetch or prepare a saved plan for the set default delete
2159+
* operation
21612160
*/
21622161
ri_BuildQueryKey(&qkey, &riinfo, RI_PLAN_SETDEFAULT_DEL_DOUPDATE);
21632162

2163+
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
21642164
{
21652165
StringInfoData querybuf;
21662166
StringInfoData qualbuf;
@@ -2207,9 +2207,9 @@ RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
22072207
}
22082208
appendStringInfoString(&querybuf, qualbuf.data);
22092209

2210-
/* Prepare the plan, don't save it */
2210+
/* Prepare and save the plan */
22112211
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
2212-
&qkey, fk_rel, pk_rel, false);
2212+
&qkey, fk_rel, pk_rel, true);
22132213
}
22142214

22152215
/*
@@ -2239,7 +2239,7 @@ RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
22392239
return PointerGetDatum(NULL);
22402240

22412241
/*
2242-
* Handle MATCH PARTIAL set null delete.
2242+
* Handle MATCH PARTIAL set default delete.
22432243
*/
22442244
case FKCONSTR_MATCH_PARTIAL:
22452245
ereport(ERROR,
@@ -2348,12 +2348,12 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
23482348
elog(ERROR, "SPI_connect failed");
23492349

23502350
/*
2351-
* Prepare a plan for the set default delete operation.
2352-
* Unfortunately we need to do it on every invocation because the
2353-
* default value could potentially change between calls.
2351+
* Fetch or prepare a saved plan for the set default update
2352+
* operation
23542353
*/
23552354
ri_BuildQueryKey(&qkey, &riinfo, RI_PLAN_SETDEFAULT_UPD_DOUPDATE);
23562355

2356+
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
23572357
{
23582358
StringInfoData querybuf;
23592359
StringInfoData qualbuf;
@@ -2400,9 +2400,9 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
24002400
}
24012401
appendStringInfoString(&querybuf, qualbuf.data);
24022402

2403-
/* Prepare the plan, don't save it */
2403+
/* Prepare and save the plan */
24042404
qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
2405-
&qkey, fk_rel, pk_rel, false);
2405+
&qkey, fk_rel, pk_rel, true);
24062406
}
24072407

24082408
/*
@@ -2432,7 +2432,7 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
24322432
return PointerGetDatum(NULL);
24332433

24342434
/*
2435-
* Handle MATCH PARTIAL set null delete.
2435+
* Handle MATCH PARTIAL set default update.
24362436
*/
24372437
case FKCONSTR_MATCH_PARTIAL:
24382438
ereport(ERROR,

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

+36
Original file line numberDiff line numberDiff line change
@@ -1319,3 +1319,39 @@ begin;
13191319
(2 rows)
13201320

13211321
commit;
1322+
--
1323+
-- Test that SET DEFAULT actions recognize updates to default values
1324+
--
1325+
create temp table defp (f1 int primary key);
1326+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "defp_pkey" for table "defp"
1327+
create temp table defc (f1 int default 0
1328+
references defp on delete set default);
1329+
insert into defp values (0), (1), (2);
1330+
insert into defc values (2);
1331+
select * from defc;
1332+
f1
1333+
----
1334+
2
1335+
(1 row)
1336+
1337+
delete from defp where f1 = 2;
1338+
select * from defc;
1339+
f1
1340+
----
1341+
0
1342+
(1 row)
1343+
1344+
delete from defp where f1 = 0; -- fail
1345+
ERROR: update or delete on table "defp" violates foreign key constraint "defc_f1_fkey" on table "defc"
1346+
DETAIL: Key (f1)=(0) is still referenced from table "defc".
1347+
alter table defc alter column f1 set default 1;
1348+
delete from defp where f1 = 0;
1349+
select * from defc;
1350+
f1
1351+
----
1352+
1
1353+
(1 row)
1354+
1355+
delete from defp where f1 = 1; -- fail
1356+
ERROR: update or delete on table "defp" violates foreign key constraint "defc_f1_fkey" on table "defc"
1357+
DETAIL: Key (f1)=(1) is still referenced from table "defc".

‎src/test/regress/sql/foreign_key.sql

+17
Original file line numberDiff line numberDiff line change
@@ -943,3 +943,20 @@ begin;
943943
update selfref set a = 456 where a = 123;
944944
select a, b from selfref;
945945
commit;
946+
947+
--
948+
-- Test that SET DEFAULT actions recognize updates to default values
949+
--
950+
create temp table defp (f1 int primary key);
951+
create temp table defc (f1 int default 0
952+
references defp on delete set default);
953+
insert into defp values (0), (1), (2);
954+
insert into defc values (2);
955+
select * from defc;
956+
delete from defp where f1 = 2;
957+
select * from defc;
958+
delete from defp where f1 = 0; -- fail
959+
alter table defc alter column f1 set default 1;
960+
delete from defp where f1 = 0;
961+
select * from defc;
962+
delete from defp where f1 = 1; -- fail

0 commit comments

Comments
 (0)