1 /* ----------
2 * ri_triggers.c
3 *
4 * Generic trigger procedures for referential integrity constraint
5 * checks.
6 *
7 * Note about memory management: the private hashtables kept here live
8 * across query and transaction boundaries, in fact they live as long as
9 * the backend does. This works because the hashtable structures
10 * themselves are allocated by dynahash.c in its permanent DynaHashCxt,
11 * and the SPI plans they point to are saved using SPI_keepplan().
12 * There is not currently any provision for throwing away a no-longer-needed
13 * plan --- consider improving this someday.
14 *
15 *
16 * Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
17 *
18 * src/backend/utils/adt/ri_triggers.c
19 *
20 * ----------
21 */
22
23
24 /* ----------
25 * Internal TODO:
26 *
27 * Add MATCH PARTIAL logic.
28 * ----------
29 */
30
31 #include "postgres.h"
32
33 #include "access/htup_details.h"
34 #include "access/sysattr.h"
35 #include "access/xact.h"
36 #include "catalog/pg_collation.h"
37 #include "catalog/pg_constraint.h"
38 #include "catalog/pg_operator.h"
39 #include "catalog/pg_type.h"
40 #include "commands/trigger.h"
41 #include "executor/executor.h"
42 #include "executor/spi.h"
43 #include "lib/ilist.h"
44 #include "parser/parse_coerce.h"
45 #include "parser/parse_relation.h"
46 #include "miscadmin.h"
47 #include "storage/bufmgr.h"
48 #include "utils/acl.h"
49 #include "utils/builtins.h"
50 #include "utils/fmgroids.h"
51 #include "utils/guc.h"
52 #include "utils/inval.h"
53 #include "utils/lsyscache.h"
54 #include "utils/memutils.h"
55 #include "utils/rel.h"
56 #include "utils/rls.h"
57 #include "utils/snapmgr.h"
58 #include "utils/syscache.h"
59 #include "utils/tqual.h"
60
61
62 /* ----------
63 * Local definitions
64 * ----------
65 */
66
67 #define RI_MAX_NUMKEYS INDEX_MAX_KEYS
68
69 #define RI_INIT_CONSTRAINTHASHSIZE 64
70 #define RI_INIT_QUERYHASHSIZE (RI_INIT_CONSTRAINTHASHSIZE * 4)
71
72 #define RI_KEYS_ALL_NULL 0
73 #define RI_KEYS_SOME_NULL 1
74 #define RI_KEYS_NONE_NULL 2
75
76 /* RI query type codes */
77 /* these queries are executed against the PK (referenced) table: */
78 #define RI_PLAN_CHECK_LOOKUPPK 1
79 #define RI_PLAN_CHECK_LOOKUPPK_FROM_PK 2
80 #define RI_PLAN_LAST_ON_PK RI_PLAN_CHECK_LOOKUPPK_FROM_PK
81 /* these queries are executed against the FK (referencing) table: */
82 #define RI_PLAN_CASCADE_DEL_DODELETE 3
83 #define RI_PLAN_CASCADE_UPD_DOUPDATE 4
84 #define RI_PLAN_RESTRICT_DEL_CHECKREF 5
85 #define RI_PLAN_RESTRICT_UPD_CHECKREF 6
86 #define RI_PLAN_SETNULL_DEL_DOUPDATE 7
87 #define RI_PLAN_SETNULL_UPD_DOUPDATE 8
88 #define RI_PLAN_SETDEFAULT_DEL_DOUPDATE 9
89 #define RI_PLAN_SETDEFAULT_UPD_DOUPDATE 10
90
91 #define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3)
92 #define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2)
93
94 #define RIAttName(rel, attnum) NameStr(*attnumAttName(rel, attnum))
95 #define RIAttType(rel, attnum) attnumTypeId(rel, attnum)
96 #define RIAttCollation(rel, attnum) attnumCollationId(rel, attnum)
97
98 #define RI_TRIGTYPE_INSERT 1
99 #define RI_TRIGTYPE_UPDATE 2
100 #define RI_TRIGTYPE_DELETE 3
101
102
103 /* ----------
104 * RI_ConstraintInfo
105 *
106 * Information extracted from an FK pg_constraint entry. This is cached in
107 * ri_constraint_cache.
108 * ----------
109 */
110 typedef struct RI_ConstraintInfo
111 {
112 Oid constraint_id; /* OID of pg_constraint entry (hash key) */
113 bool valid; /* successfully initialized? */
114 uint32 oidHashValue; /* hash value of pg_constraint OID */
115 NameData conname; /* name of the FK constraint */
116 Oid pk_relid; /* referenced relation */
117 Oid fk_relid; /* referencing relation */
118 char confupdtype; /* foreign key's ON UPDATE action */
119 char confdeltype; /* foreign key's ON DELETE action */
120 char confmatchtype; /* foreign key's match type */
121 int nkeys; /* number of key columns */
122 int16 pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */
123 int16 fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */
124 Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK =
125 * FK) */
126 Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK =
127 * PK) */
128 Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK =
129 * FK) */
130 dlist_node valid_link; /* Link in list of valid entries */
131 } RI_ConstraintInfo;
132
133
134 /* ----------
135 * RI_QueryKey
136 *
137 * The key identifying a prepared SPI plan in our query hashtable
138 * ----------
139 */
140 typedef struct RI_QueryKey
141 {
142 Oid constr_id; /* OID of pg_constraint entry */
143 int32 constr_queryno; /* query type ID, see RI_PLAN_XXX above */
144 } RI_QueryKey;
145
146
147 /* ----------
148 * RI_QueryHashEntry
149 * ----------
150 */
151 typedef struct RI_QueryHashEntry
152 {
153 RI_QueryKey key;
154 SPIPlanPtr plan;
155 } RI_QueryHashEntry;
156
157
158 /* ----------
159 * RI_CompareKey
160 *
161 * The key identifying an entry showing how to compare two values
162 * ----------
163 */
164 typedef struct RI_CompareKey
165 {
166 Oid eq_opr; /* the equality operator to apply */
167 Oid typeid; /* the data type to apply it to */
168 } RI_CompareKey;
169
170
171 /* ----------
172 * RI_CompareHashEntry
173 * ----------
174 */
175 typedef struct RI_CompareHashEntry
176 {
177 RI_CompareKey key;
178 bool valid; /* successfully initialized? */
179 FmgrInfo eq_opr_finfo; /* call info for equality fn */
180 FmgrInfo cast_func_finfo; /* in case we must coerce input */
181 } RI_CompareHashEntry;
182
183
184 /* ----------
185 * Local data
186 * ----------
187 */
188 static HTAB *ri_constraint_cache = NULL;
189 static HTAB *ri_query_cache = NULL;
190 static HTAB *ri_compare_cache = NULL;
191 static dlist_head ri_constraint_cache_valid_list;
192 static int ri_constraint_cache_valid_count = 0;
193
194
195 /* ----------
196 * Local function prototypes
197 * ----------
198 */
199 static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
200 HeapTuple old_row,
201 const RI_ConstraintInfo *riinfo);
202 static Datum ri_restrict_del(TriggerData *trigdata, bool is_no_action);
203 static Datum ri_restrict_upd(TriggerData *trigdata, bool is_no_action);
204 static void quoteOneName(char *buffer, const char *name);
205 static void quoteRelationName(char *buffer, Relation rel);
206 static void ri_GenerateQual(StringInfo buf,
207 const char *sep,
208 const char *leftop, Oid leftoptype,
209 Oid opoid,
210 const char *rightop, Oid rightoptype);
211 static void ri_GenerateQualCollation(StringInfo buf, Oid collation);
212 static int ri_NullCheck(HeapTuple tup,
213 const RI_ConstraintInfo *riinfo, bool rel_is_pk);
214 static void ri_BuildQueryKey(RI_QueryKey *key,
215 const RI_ConstraintInfo *riinfo,
216 int32 constr_queryno);
217 static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
218 const RI_ConstraintInfo *riinfo, bool rel_is_pk);
219 static bool ri_AttributesEqual(Oid eq_opr, Oid typeid,
220 Datum oldvalue, Datum newvalue);
221
222 static void ri_InitHashTables(void);
223 static void InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue);
224 static SPIPlanPtr ri_FetchPreparedPlan(RI_QueryKey *key);
225 static void ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan);
226 static RI_CompareHashEntry *ri_HashCompareOp(Oid eq_opr, Oid typeid);
227
228 static void ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname,
229 int tgkind);
230 static const RI_ConstraintInfo *ri_FetchConstraintInfo(Trigger *trigger,
231 Relation trig_rel, bool rel_is_pk);
232 static const RI_ConstraintInfo *ri_LoadConstraintInfo(Oid constraintOid);
233 static SPIPlanPtr ri_PlanCheck(const char *querystr, int nargs, Oid *argtypes,
234 RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel,
235 bool cache_plan);
236 static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
237 RI_QueryKey *qkey, SPIPlanPtr qplan,
238 Relation fk_rel, Relation pk_rel,
239 HeapTuple old_tuple, HeapTuple new_tuple,
240 bool detectNewRows, int expect_OK);
241 static void ri_ExtractValues(Relation rel, HeapTuple tup,
242 const RI_ConstraintInfo *riinfo, bool rel_is_pk,
243 Datum *vals, char *nulls);
244 static void ri_ReportViolation(const RI_ConstraintInfo *riinfo,
245 Relation pk_rel, Relation fk_rel,
246 HeapTuple violator, TupleDesc tupdesc,
247 int queryno, bool spi_err);
248
249
250 /* ----------
251 * RI_FKey_check -
252 *
253 * Check foreign key existence (combined for INSERT and UPDATE).
254 * ----------
255 */
256 static Datum
RI_FKey_check(TriggerData * trigdata)257 RI_FKey_check(TriggerData *trigdata)
258 {
259 const RI_ConstraintInfo *riinfo;
260 Relation fk_rel;
261 Relation pk_rel;
262 HeapTuple new_row;
263 Buffer new_row_buf;
264 RI_QueryKey qkey;
265 SPIPlanPtr qplan;
266 int i;
267
268 /*
269 * Get arguments.
270 */
271 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
272 trigdata->tg_relation, false);
273
274 if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
275 {
276 new_row = trigdata->tg_newtuple;
277 new_row_buf = trigdata->tg_newtuplebuf;
278 }
279 else
280 {
281 new_row = trigdata->tg_trigtuple;
282 new_row_buf = trigdata->tg_trigtuplebuf;
283 }
284
285 /*
286 * We should not even consider checking the row if it is no longer valid,
287 * since it was either deleted (so the deferred check should be skipped)
288 * or updated (in which case only the latest version of the row should be
289 * checked). Test its liveness according to SnapshotSelf. We need pin
290 * and lock on the buffer to call HeapTupleSatisfiesVisibility. Caller
291 * should be holding pin, but not lock.
292 */
293 LockBuffer(new_row_buf, BUFFER_LOCK_SHARE);
294 if (!HeapTupleSatisfiesVisibility(new_row, SnapshotSelf, new_row_buf))
295 {
296 LockBuffer(new_row_buf, BUFFER_LOCK_UNLOCK);
297 return PointerGetDatum(NULL);
298 }
299 LockBuffer(new_row_buf, BUFFER_LOCK_UNLOCK);
300
301 /*
302 * Get the relation descriptors of the FK and PK tables.
303 *
304 * pk_rel is opened in RowShareLock mode since that's what our eventual
305 * SELECT FOR KEY SHARE will get on it.
306 */
307 fk_rel = trigdata->tg_relation;
308 pk_rel = heap_open(riinfo->pk_relid, RowShareLock);
309
310 if (riinfo->confmatchtype == FKCONSTR_MATCH_PARTIAL)
311 ereport(ERROR,
312 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
313 errmsg("MATCH PARTIAL not yet implemented")));
314
315 switch (ri_NullCheck(new_row, riinfo, false))
316 {
317 case RI_KEYS_ALL_NULL:
318
319 /*
320 * No further check needed - an all-NULL key passes every type of
321 * foreign key constraint.
322 */
323 heap_close(pk_rel, RowShareLock);
324 return PointerGetDatum(NULL);
325
326 case RI_KEYS_SOME_NULL:
327
328 /*
329 * This is the only case that differs between the three kinds of
330 * MATCH.
331 */
332 switch (riinfo->confmatchtype)
333 {
334 case FKCONSTR_MATCH_FULL:
335
336 /*
337 * Not allowed - MATCH FULL says either all or none of the
338 * attributes can be NULLs
339 */
340 ereport(ERROR,
341 (errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
342 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
343 RelationGetRelationName(fk_rel),
344 NameStr(riinfo->conname)),
345 errdetail("MATCH FULL does not allow mixing of null and nonnull key values."),
346 errtableconstraint(fk_rel,
347 NameStr(riinfo->conname))));
348 heap_close(pk_rel, RowShareLock);
349 return PointerGetDatum(NULL);
350
351 case FKCONSTR_MATCH_SIMPLE:
352
353 /*
354 * MATCH SIMPLE - if ANY column is null, the key passes
355 * the constraint.
356 */
357 heap_close(pk_rel, RowShareLock);
358 return PointerGetDatum(NULL);
359
360 case FKCONSTR_MATCH_PARTIAL:
361
362 /*
363 * MATCH PARTIAL - all non-null columns must match. (not
364 * implemented, can be done by modifying the query below
365 * to only include non-null columns, or by writing a
366 * special version here)
367 */
368 ereport(ERROR,
369 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
370 errmsg("MATCH PARTIAL not yet implemented")));
371 heap_close(pk_rel, RowShareLock);
372 return PointerGetDatum(NULL);
373
374 default:
375 elog(ERROR, "unrecognized confmatchtype: %d",
376 riinfo->confmatchtype);
377 break;
378 }
379
380 case RI_KEYS_NONE_NULL:
381
382 /*
383 * Have a full qualified key - continue below for all three kinds
384 * of MATCH.
385 */
386 break;
387 }
388
389 if (SPI_connect() != SPI_OK_CONNECT)
390 elog(ERROR, "SPI_connect failed");
391
392 /*
393 * Fetch or prepare a saved plan for the real check
394 */
395 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_CHECK_LOOKUPPK);
396
397 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
398 {
399 StringInfoData querybuf;
400 char pkrelname[MAX_QUOTED_REL_NAME_LEN];
401 char attname[MAX_QUOTED_NAME_LEN];
402 char paramname[16];
403 const char *querysep;
404 Oid queryoids[RI_MAX_NUMKEYS];
405
406 /* ----------
407 * The query string built is
408 * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...]
409 * FOR KEY SHARE OF x
410 * The type id's for the $ parameters are those of the
411 * corresponding FK attributes.
412 * ----------
413 */
414 initStringInfo(&querybuf);
415 quoteRelationName(pkrelname, pk_rel);
416 appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);
417 querysep = "WHERE";
418 for (i = 0; i < riinfo->nkeys; i++)
419 {
420 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
421 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
422
423 quoteOneName(attname,
424 RIAttName(pk_rel, riinfo->pk_attnums[i]));
425 sprintf(paramname, "$%d", i + 1);
426 ri_GenerateQual(&querybuf, querysep,
427 attname, pk_type,
428 riinfo->pf_eq_oprs[i],
429 paramname, fk_type);
430 querysep = "AND";
431 queryoids[i] = fk_type;
432 }
433 appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
434
435 /* Prepare and save the plan */
436 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
437 &qkey, fk_rel, pk_rel, true);
438 }
439
440 /*
441 * Now check that foreign key exists in PK table
442 */
443 ri_PerformCheck(riinfo, &qkey, qplan,
444 fk_rel, pk_rel,
445 NULL, new_row,
446 false,
447 SPI_OK_SELECT);
448
449 if (SPI_finish() != SPI_OK_FINISH)
450 elog(ERROR, "SPI_finish failed");
451
452 heap_close(pk_rel, RowShareLock);
453
454 return PointerGetDatum(NULL);
455 }
456
457
458 /* ----------
459 * RI_FKey_check_ins -
460 *
461 * Check foreign key existence at insert event on FK table.
462 * ----------
463 */
464 Datum
RI_FKey_check_ins(PG_FUNCTION_ARGS)465 RI_FKey_check_ins(PG_FUNCTION_ARGS)
466 {
467 /*
468 * Check that this is a valid trigger call on the right time and event.
469 */
470 ri_CheckTrigger(fcinfo, "RI_FKey_check_ins", RI_TRIGTYPE_INSERT);
471
472 /*
473 * Share code with UPDATE case.
474 */
475 return RI_FKey_check((TriggerData *) fcinfo->context);
476 }
477
478
479 /* ----------
480 * RI_FKey_check_upd -
481 *
482 * Check foreign key existence at update event on FK table.
483 * ----------
484 */
485 Datum
RI_FKey_check_upd(PG_FUNCTION_ARGS)486 RI_FKey_check_upd(PG_FUNCTION_ARGS)
487 {
488 /*
489 * Check that this is a valid trigger call on the right time and event.
490 */
491 ri_CheckTrigger(fcinfo, "RI_FKey_check_upd", RI_TRIGTYPE_UPDATE);
492
493 /*
494 * Share code with INSERT case.
495 */
496 return RI_FKey_check((TriggerData *) fcinfo->context);
497 }
498
499
500 /* ----------
501 * ri_Check_Pk_Match
502 *
503 * Check to see if another PK row has been created that provides the same
504 * key values as the "old_row" that's been modified or deleted in our trigger
505 * event. Returns true if a match is found in the PK table.
506 *
507 * We assume the caller checked that the old_row contains no NULL key values,
508 * since otherwise a match is impossible.
509 * ----------
510 */
511 static bool
ri_Check_Pk_Match(Relation pk_rel,Relation fk_rel,HeapTuple old_row,const RI_ConstraintInfo * riinfo)512 ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
513 HeapTuple old_row,
514 const RI_ConstraintInfo *riinfo)
515 {
516 SPIPlanPtr qplan;
517 RI_QueryKey qkey;
518 int i;
519 bool result;
520
521 /* Only called for non-null rows */
522 Assert(ri_NullCheck(old_row, riinfo, true) == RI_KEYS_NONE_NULL);
523
524 if (SPI_connect() != SPI_OK_CONNECT)
525 elog(ERROR, "SPI_connect failed");
526
527 /*
528 * Fetch or prepare a saved plan for checking PK table with values coming
529 * from a PK row
530 */
531 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_CHECK_LOOKUPPK_FROM_PK);
532
533 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
534 {
535 StringInfoData querybuf;
536 char pkrelname[MAX_QUOTED_REL_NAME_LEN];
537 char attname[MAX_QUOTED_NAME_LEN];
538 char paramname[16];
539 const char *querysep;
540 Oid queryoids[RI_MAX_NUMKEYS];
541
542 /* ----------
543 * The query string built is
544 * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...]
545 * FOR KEY SHARE OF x
546 * The type id's for the $ parameters are those of the
547 * PK attributes themselves.
548 * ----------
549 */
550 initStringInfo(&querybuf);
551 quoteRelationName(pkrelname, pk_rel);
552 appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);
553 querysep = "WHERE";
554 for (i = 0; i < riinfo->nkeys; i++)
555 {
556 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
557
558 quoteOneName(attname,
559 RIAttName(pk_rel, riinfo->pk_attnums[i]));
560 sprintf(paramname, "$%d", i + 1);
561 ri_GenerateQual(&querybuf, querysep,
562 attname, pk_type,
563 riinfo->pp_eq_oprs[i],
564 paramname, pk_type);
565 querysep = "AND";
566 queryoids[i] = pk_type;
567 }
568 appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
569
570 /* Prepare and save the plan */
571 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
572 &qkey, fk_rel, pk_rel, true);
573 }
574
575 /*
576 * We have a plan now. Run it.
577 */
578 result = ri_PerformCheck(riinfo, &qkey, qplan,
579 fk_rel, pk_rel,
580 old_row, NULL,
581 true, /* treat like update */
582 SPI_OK_SELECT);
583
584 if (SPI_finish() != SPI_OK_FINISH)
585 elog(ERROR, "SPI_finish failed");
586
587 return result;
588 }
589
590
591 /* ----------
592 * RI_FKey_noaction_del -
593 *
594 * Give an error and roll back the current transaction if the
595 * delete has resulted in a violation of the given referential
596 * integrity constraint.
597 * ----------
598 */
599 Datum
RI_FKey_noaction_del(PG_FUNCTION_ARGS)600 RI_FKey_noaction_del(PG_FUNCTION_ARGS)
601 {
602 /*
603 * Check that this is a valid trigger call on the right time and event.
604 */
605 ri_CheckTrigger(fcinfo, "RI_FKey_noaction_del", RI_TRIGTYPE_DELETE);
606
607 /*
608 * Share code with RESTRICT case.
609 */
610 return ri_restrict_del((TriggerData *) fcinfo->context, true);
611 }
612
613 /* ----------
614 * RI_FKey_restrict_del -
615 *
616 * Restrict delete from PK table to rows unreferenced by foreign key.
617 *
618 * The SQL standard intends that this referential action occur exactly when
619 * the delete is performed, rather than after. This appears to be
620 * the only difference between "NO ACTION" and "RESTRICT". In Postgres
621 * we still implement this as an AFTER trigger, but it's non-deferrable.
622 * ----------
623 */
624 Datum
RI_FKey_restrict_del(PG_FUNCTION_ARGS)625 RI_FKey_restrict_del(PG_FUNCTION_ARGS)
626 {
627 /*
628 * Check that this is a valid trigger call on the right time and event.
629 */
630 ri_CheckTrigger(fcinfo, "RI_FKey_restrict_del", RI_TRIGTYPE_DELETE);
631
632 /*
633 * Share code with NO ACTION case.
634 */
635 return ri_restrict_del((TriggerData *) fcinfo->context, false);
636 }
637
638 /* ----------
639 * ri_restrict_del -
640 *
641 * Common code for ON DELETE RESTRICT and ON DELETE NO ACTION.
642 * ----------
643 */
644 static Datum
ri_restrict_del(TriggerData * trigdata,bool is_no_action)645 ri_restrict_del(TriggerData *trigdata, bool is_no_action)
646 {
647 const RI_ConstraintInfo *riinfo;
648 Relation fk_rel;
649 Relation pk_rel;
650 HeapTuple old_row;
651 RI_QueryKey qkey;
652 SPIPlanPtr qplan;
653 int i;
654
655 /*
656 * Get arguments.
657 */
658 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
659 trigdata->tg_relation, true);
660
661 /*
662 * Get the relation descriptors of the FK and PK tables and the old tuple.
663 *
664 * fk_rel is opened in RowShareLock mode since that's what our eventual
665 * SELECT FOR KEY SHARE will get on it.
666 */
667 fk_rel = heap_open(riinfo->fk_relid, RowShareLock);
668 pk_rel = trigdata->tg_relation;
669 old_row = trigdata->tg_trigtuple;
670
671 switch (riinfo->confmatchtype)
672 {
673 /* ----------
674 * SQL:2008 15.17 <Execution of referential actions>
675 * General rules 9) a) iv):
676 * MATCH SIMPLE/FULL
677 * ... ON DELETE RESTRICT
678 * ----------
679 */
680 case FKCONSTR_MATCH_SIMPLE:
681 case FKCONSTR_MATCH_FULL:
682 switch (ri_NullCheck(old_row, riinfo, true))
683 {
684 case RI_KEYS_ALL_NULL:
685 case RI_KEYS_SOME_NULL:
686
687 /*
688 * No check needed - there cannot be any reference to old
689 * key if it contains a NULL
690 */
691 heap_close(fk_rel, RowShareLock);
692 return PointerGetDatum(NULL);
693
694 case RI_KEYS_NONE_NULL:
695
696 /*
697 * Have a full qualified key - continue below
698 */
699 break;
700 }
701
702 /*
703 * If another PK row now exists providing the old key values, we
704 * should not do anything. However, this check should only be
705 * made in the NO ACTION case; in RESTRICT cases we don't wish to
706 * allow another row to be substituted.
707 */
708 if (is_no_action &&
709 ri_Check_Pk_Match(pk_rel, fk_rel, old_row, riinfo))
710 {
711 heap_close(fk_rel, RowShareLock);
712 return PointerGetDatum(NULL);
713 }
714
715 if (SPI_connect() != SPI_OK_CONNECT)
716 elog(ERROR, "SPI_connect failed");
717
718 /*
719 * Fetch or prepare a saved plan for the restrict delete lookup
720 */
721 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_RESTRICT_DEL_CHECKREF);
722
723 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
724 {
725 StringInfoData querybuf;
726 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
727 char attname[MAX_QUOTED_NAME_LEN];
728 char paramname[16];
729 const char *querysep;
730 Oid queryoids[RI_MAX_NUMKEYS];
731
732 /* ----------
733 * The query string built is
734 * SELECT 1 FROM ONLY <fktable> x WHERE $1 = fkatt1 [AND ...]
735 * FOR KEY SHARE OF x
736 * The type id's for the $ parameters are those of the
737 * corresponding PK attributes.
738 * ----------
739 */
740 initStringInfo(&querybuf);
741 quoteRelationName(fkrelname, fk_rel);
742 appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
743 fkrelname);
744 querysep = "WHERE";
745 for (i = 0; i < riinfo->nkeys; i++)
746 {
747 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
748 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
749
750 quoteOneName(attname,
751 RIAttName(fk_rel, riinfo->fk_attnums[i]));
752 sprintf(paramname, "$%d", i + 1);
753 ri_GenerateQual(&querybuf, querysep,
754 paramname, pk_type,
755 riinfo->pf_eq_oprs[i],
756 attname, fk_type);
757 querysep = "AND";
758 queryoids[i] = pk_type;
759 }
760 appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
761
762 /* Prepare and save the plan */
763 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
764 &qkey, fk_rel, pk_rel, true);
765 }
766
767 /*
768 * We have a plan now. Run it to check for existing references.
769 */
770 ri_PerformCheck(riinfo, &qkey, qplan,
771 fk_rel, pk_rel,
772 old_row, NULL,
773 true, /* must detect new rows */
774 SPI_OK_SELECT);
775
776 if (SPI_finish() != SPI_OK_FINISH)
777 elog(ERROR, "SPI_finish failed");
778
779 heap_close(fk_rel, RowShareLock);
780
781 return PointerGetDatum(NULL);
782
783 /*
784 * Handle MATCH PARTIAL restrict delete.
785 */
786 case FKCONSTR_MATCH_PARTIAL:
787 ereport(ERROR,
788 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
789 errmsg("MATCH PARTIAL not yet implemented")));
790 return PointerGetDatum(NULL);
791
792 default:
793 elog(ERROR, "unrecognized confmatchtype: %d",
794 riinfo->confmatchtype);
795 break;
796 }
797
798 /* Never reached */
799 return PointerGetDatum(NULL);
800 }
801
802
803 /* ----------
804 * RI_FKey_noaction_upd -
805 *
806 * Give an error and roll back the current transaction if the
807 * update has resulted in a violation of the given referential
808 * integrity constraint.
809 * ----------
810 */
811 Datum
RI_FKey_noaction_upd(PG_FUNCTION_ARGS)812 RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
813 {
814 /*
815 * Check that this is a valid trigger call on the right time and event.
816 */
817 ri_CheckTrigger(fcinfo, "RI_FKey_noaction_upd", RI_TRIGTYPE_UPDATE);
818
819 /*
820 * Share code with RESTRICT case.
821 */
822 return ri_restrict_upd((TriggerData *) fcinfo->context, true);
823 }
824
825 /* ----------
826 * RI_FKey_restrict_upd -
827 *
828 * Restrict update of PK to rows unreferenced by foreign key.
829 *
830 * The SQL standard intends that this referential action occur exactly when
831 * the update is performed, rather than after. This appears to be
832 * the only difference between "NO ACTION" and "RESTRICT". In Postgres
833 * we still implement this as an AFTER trigger, but it's non-deferrable.
834 * ----------
835 */
836 Datum
RI_FKey_restrict_upd(PG_FUNCTION_ARGS)837 RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
838 {
839 /*
840 * Check that this is a valid trigger call on the right time and event.
841 */
842 ri_CheckTrigger(fcinfo, "RI_FKey_restrict_upd", RI_TRIGTYPE_UPDATE);
843
844 /*
845 * Share code with NO ACTION case.
846 */
847 return ri_restrict_upd((TriggerData *) fcinfo->context, false);
848 }
849
850 /* ----------
851 * ri_restrict_upd -
852 *
853 * Common code for ON UPDATE RESTRICT and ON UPDATE NO ACTION.
854 * ----------
855 */
856 static Datum
ri_restrict_upd(TriggerData * trigdata,bool is_no_action)857 ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
858 {
859 const RI_ConstraintInfo *riinfo;
860 Relation fk_rel;
861 Relation pk_rel;
862 HeapTuple new_row;
863 HeapTuple old_row;
864 RI_QueryKey qkey;
865 SPIPlanPtr qplan;
866 int i;
867
868 /*
869 * Get arguments.
870 */
871 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
872 trigdata->tg_relation, true);
873
874 /*
875 * Get the relation descriptors of the FK and PK tables and the new and
876 * old tuple.
877 *
878 * fk_rel is opened in RowShareLock mode since that's what our eventual
879 * SELECT FOR KEY SHARE will get on it.
880 */
881 fk_rel = heap_open(riinfo->fk_relid, RowShareLock);
882 pk_rel = trigdata->tg_relation;
883 new_row = trigdata->tg_newtuple;
884 old_row = trigdata->tg_trigtuple;
885
886 switch (riinfo->confmatchtype)
887 {
888 /* ----------
889 * SQL:2008 15.17 <Execution of referential actions>
890 * General rules 10) a) iv):
891 * MATCH SIMPLE/FULL
892 * ... ON UPDATE RESTRICT
893 * ----------
894 */
895 case FKCONSTR_MATCH_SIMPLE:
896 case FKCONSTR_MATCH_FULL:
897 switch (ri_NullCheck(old_row, riinfo, true))
898 {
899 case RI_KEYS_ALL_NULL:
900 case RI_KEYS_SOME_NULL:
901
902 /*
903 * No check needed - there cannot be any reference to old
904 * key if it contains a NULL
905 */
906 heap_close(fk_rel, RowShareLock);
907 return PointerGetDatum(NULL);
908
909 case RI_KEYS_NONE_NULL:
910
911 /*
912 * Have a full qualified key - continue below
913 */
914 break;
915 }
916
917 /*
918 * No need to check anything if old and new keys are equal
919 */
920 if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
921 {
922 heap_close(fk_rel, RowShareLock);
923 return PointerGetDatum(NULL);
924 }
925
926 /*
927 * If another PK row now exists providing the old key values, we
928 * should not do anything. However, this check should only be
929 * made in the NO ACTION case; in RESTRICT cases we don't wish to
930 * allow another row to be substituted.
931 */
932 if (is_no_action &&
933 ri_Check_Pk_Match(pk_rel, fk_rel, old_row, riinfo))
934 {
935 heap_close(fk_rel, RowShareLock);
936 return PointerGetDatum(NULL);
937 }
938
939 if (SPI_connect() != SPI_OK_CONNECT)
940 elog(ERROR, "SPI_connect failed");
941
942 /*
943 * Fetch or prepare a saved plan for the restrict update lookup
944 */
945 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_RESTRICT_UPD_CHECKREF);
946
947 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
948 {
949 StringInfoData querybuf;
950 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
951 char attname[MAX_QUOTED_NAME_LEN];
952 char paramname[16];
953 const char *querysep;
954 Oid queryoids[RI_MAX_NUMKEYS];
955
956 /* ----------
957 * The query string built is
958 * SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
959 * The type id's for the $ parameters are those of the
960 * corresponding PK attributes.
961 * ----------
962 */
963 initStringInfo(&querybuf);
964 quoteRelationName(fkrelname, fk_rel);
965 appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
966 fkrelname);
967 querysep = "WHERE";
968 for (i = 0; i < riinfo->nkeys; i++)
969 {
970 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
971 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
972
973 quoteOneName(attname,
974 RIAttName(fk_rel, riinfo->fk_attnums[i]));
975 sprintf(paramname, "$%d", i + 1);
976 ri_GenerateQual(&querybuf, querysep,
977 paramname, pk_type,
978 riinfo->pf_eq_oprs[i],
979 attname, fk_type);
980 querysep = "AND";
981 queryoids[i] = pk_type;
982 }
983 appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
984
985 /* Prepare and save the plan */
986 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
987 &qkey, fk_rel, pk_rel, true);
988 }
989
990 /*
991 * We have a plan now. Run it to check for existing references.
992 */
993 ri_PerformCheck(riinfo, &qkey, qplan,
994 fk_rel, pk_rel,
995 old_row, NULL,
996 true, /* must detect new rows */
997 SPI_OK_SELECT);
998
999 if (SPI_finish() != SPI_OK_FINISH)
1000 elog(ERROR, "SPI_finish failed");
1001
1002 heap_close(fk_rel, RowShareLock);
1003
1004 return PointerGetDatum(NULL);
1005
1006 /*
1007 * Handle MATCH PARTIAL restrict update.
1008 */
1009 case FKCONSTR_MATCH_PARTIAL:
1010 ereport(ERROR,
1011 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1012 errmsg("MATCH PARTIAL not yet implemented")));
1013 return PointerGetDatum(NULL);
1014
1015 default:
1016 elog(ERROR, "unrecognized confmatchtype: %d",
1017 riinfo->confmatchtype);
1018 break;
1019 }
1020
1021 /* Never reached */
1022 return PointerGetDatum(NULL);
1023 }
1024
1025
1026 /* ----------
1027 * RI_FKey_cascade_del -
1028 *
1029 * Cascaded delete foreign key references at delete event on PK table.
1030 * ----------
1031 */
1032 Datum
RI_FKey_cascade_del(PG_FUNCTION_ARGS)1033 RI_FKey_cascade_del(PG_FUNCTION_ARGS)
1034 {
1035 TriggerData *trigdata = (TriggerData *) fcinfo->context;
1036 const RI_ConstraintInfo *riinfo;
1037 Relation fk_rel;
1038 Relation pk_rel;
1039 HeapTuple old_row;
1040 RI_QueryKey qkey;
1041 SPIPlanPtr qplan;
1042 int i;
1043
1044 /*
1045 * Check that this is a valid trigger call on the right time and event.
1046 */
1047 ri_CheckTrigger(fcinfo, "RI_FKey_cascade_del", RI_TRIGTYPE_DELETE);
1048
1049 /*
1050 * Get arguments.
1051 */
1052 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
1053 trigdata->tg_relation, true);
1054
1055 /*
1056 * Get the relation descriptors of the FK and PK tables and the old tuple.
1057 *
1058 * fk_rel is opened in RowExclusiveLock mode since that's what our
1059 * eventual DELETE will get on it.
1060 */
1061 fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
1062 pk_rel = trigdata->tg_relation;
1063 old_row = trigdata->tg_trigtuple;
1064
1065 switch (riinfo->confmatchtype)
1066 {
1067 /* ----------
1068 * SQL:2008 15.17 <Execution of referential actions>
1069 * General rules 9) a) i):
1070 * MATCH SIMPLE/FULL
1071 * ... ON DELETE CASCADE
1072 * ----------
1073 */
1074 case FKCONSTR_MATCH_SIMPLE:
1075 case FKCONSTR_MATCH_FULL:
1076 switch (ri_NullCheck(old_row, riinfo, true))
1077 {
1078 case RI_KEYS_ALL_NULL:
1079 case RI_KEYS_SOME_NULL:
1080
1081 /*
1082 * No check needed - there cannot be any reference to old
1083 * key if it contains a NULL
1084 */
1085 heap_close(fk_rel, RowExclusiveLock);
1086 return PointerGetDatum(NULL);
1087
1088 case RI_KEYS_NONE_NULL:
1089
1090 /*
1091 * Have a full qualified key - continue below
1092 */
1093 break;
1094 }
1095
1096 if (SPI_connect() != SPI_OK_CONNECT)
1097 elog(ERROR, "SPI_connect failed");
1098
1099 /*
1100 * Fetch or prepare a saved plan for the cascaded delete
1101 */
1102 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_CASCADE_DEL_DODELETE);
1103
1104 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1105 {
1106 StringInfoData querybuf;
1107 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1108 char attname[MAX_QUOTED_NAME_LEN];
1109 char paramname[16];
1110 const char *querysep;
1111 Oid queryoids[RI_MAX_NUMKEYS];
1112
1113 /* ----------
1114 * The query string built is
1115 * DELETE FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
1116 * The type id's for the $ parameters are those of the
1117 * corresponding PK attributes.
1118 * ----------
1119 */
1120 initStringInfo(&querybuf);
1121 quoteRelationName(fkrelname, fk_rel);
1122 appendStringInfo(&querybuf, "DELETE FROM ONLY %s", fkrelname);
1123 querysep = "WHERE";
1124 for (i = 0; i < riinfo->nkeys; i++)
1125 {
1126 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
1127 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
1128
1129 quoteOneName(attname,
1130 RIAttName(fk_rel, riinfo->fk_attnums[i]));
1131 sprintf(paramname, "$%d", i + 1);
1132 ri_GenerateQual(&querybuf, querysep,
1133 paramname, pk_type,
1134 riinfo->pf_eq_oprs[i],
1135 attname, fk_type);
1136 querysep = "AND";
1137 queryoids[i] = pk_type;
1138 }
1139
1140 /* Prepare and save the plan */
1141 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
1142 &qkey, fk_rel, pk_rel, true);
1143 }
1144
1145 /*
1146 * We have a plan now. Build up the arguments from the key values
1147 * in the deleted PK tuple and delete the referencing rows
1148 */
1149 ri_PerformCheck(riinfo, &qkey, qplan,
1150 fk_rel, pk_rel,
1151 old_row, NULL,
1152 true, /* must detect new rows */
1153 SPI_OK_DELETE);
1154
1155 if (SPI_finish() != SPI_OK_FINISH)
1156 elog(ERROR, "SPI_finish failed");
1157
1158 heap_close(fk_rel, RowExclusiveLock);
1159
1160 return PointerGetDatum(NULL);
1161
1162 /*
1163 * Handle MATCH PARTIAL cascaded delete.
1164 */
1165 case FKCONSTR_MATCH_PARTIAL:
1166 ereport(ERROR,
1167 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1168 errmsg("MATCH PARTIAL not yet implemented")));
1169 return PointerGetDatum(NULL);
1170
1171 default:
1172 elog(ERROR, "unrecognized confmatchtype: %d",
1173 riinfo->confmatchtype);
1174 break;
1175 }
1176
1177 /* Never reached */
1178 return PointerGetDatum(NULL);
1179 }
1180
1181
1182 /* ----------
1183 * RI_FKey_cascade_upd -
1184 *
1185 * Cascaded update foreign key references at update event on PK table.
1186 * ----------
1187 */
1188 Datum
RI_FKey_cascade_upd(PG_FUNCTION_ARGS)1189 RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
1190 {
1191 TriggerData *trigdata = (TriggerData *) fcinfo->context;
1192 const RI_ConstraintInfo *riinfo;
1193 Relation fk_rel;
1194 Relation pk_rel;
1195 HeapTuple new_row;
1196 HeapTuple old_row;
1197 RI_QueryKey qkey;
1198 SPIPlanPtr qplan;
1199 int i;
1200 int j;
1201
1202 /*
1203 * Check that this is a valid trigger call on the right time and event.
1204 */
1205 ri_CheckTrigger(fcinfo, "RI_FKey_cascade_upd", RI_TRIGTYPE_UPDATE);
1206
1207 /*
1208 * Get arguments.
1209 */
1210 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
1211 trigdata->tg_relation, true);
1212
1213 /*
1214 * Get the relation descriptors of the FK and PK tables and the new and
1215 * old tuple.
1216 *
1217 * fk_rel is opened in RowExclusiveLock mode since that's what our
1218 * eventual UPDATE will get on it.
1219 */
1220 fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
1221 pk_rel = trigdata->tg_relation;
1222 new_row = trigdata->tg_newtuple;
1223 old_row = trigdata->tg_trigtuple;
1224
1225 switch (riinfo->confmatchtype)
1226 {
1227 /* ----------
1228 * SQL:2008 15.17 <Execution of referential actions>
1229 * General rules 10) a) i):
1230 * MATCH SIMPLE/FULL
1231 * ... ON UPDATE CASCADE
1232 * ----------
1233 */
1234 case FKCONSTR_MATCH_SIMPLE:
1235 case FKCONSTR_MATCH_FULL:
1236 switch (ri_NullCheck(old_row, riinfo, true))
1237 {
1238 case RI_KEYS_ALL_NULL:
1239 case RI_KEYS_SOME_NULL:
1240
1241 /*
1242 * No check needed - there cannot be any reference to old
1243 * key if it contains a NULL
1244 */
1245 heap_close(fk_rel, RowExclusiveLock);
1246 return PointerGetDatum(NULL);
1247
1248 case RI_KEYS_NONE_NULL:
1249
1250 /*
1251 * Have a full qualified key - continue below
1252 */
1253 break;
1254 }
1255
1256 /*
1257 * No need to do anything if old and new keys are equal
1258 */
1259 if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
1260 {
1261 heap_close(fk_rel, RowExclusiveLock);
1262 return PointerGetDatum(NULL);
1263 }
1264
1265 if (SPI_connect() != SPI_OK_CONNECT)
1266 elog(ERROR, "SPI_connect failed");
1267
1268 /*
1269 * Fetch or prepare a saved plan for the cascaded update
1270 */
1271 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_CASCADE_UPD_DOUPDATE);
1272
1273 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1274 {
1275 StringInfoData querybuf;
1276 StringInfoData qualbuf;
1277 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1278 char attname[MAX_QUOTED_NAME_LEN];
1279 char paramname[16];
1280 const char *querysep;
1281 const char *qualsep;
1282 Oid queryoids[RI_MAX_NUMKEYS * 2];
1283
1284 /* ----------
1285 * The query string built is
1286 * UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]
1287 * WHERE $n = fkatt1 [AND ...]
1288 * The type id's for the $ parameters are those of the
1289 * corresponding PK attributes. Note that we are assuming
1290 * there is an assignment cast from the PK to the FK type;
1291 * else the parser will fail.
1292 * ----------
1293 */
1294 initStringInfo(&querybuf);
1295 initStringInfo(&qualbuf);
1296 quoteRelationName(fkrelname, fk_rel);
1297 appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
1298 querysep = "";
1299 qualsep = "WHERE";
1300 for (i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)
1301 {
1302 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
1303 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
1304
1305 quoteOneName(attname,
1306 RIAttName(fk_rel, riinfo->fk_attnums[i]));
1307 appendStringInfo(&querybuf,
1308 "%s %s = $%d",
1309 querysep, attname, i + 1);
1310 sprintf(paramname, "$%d", j + 1);
1311 ri_GenerateQual(&qualbuf, qualsep,
1312 paramname, pk_type,
1313 riinfo->pf_eq_oprs[i],
1314 attname, fk_type);
1315 querysep = ",";
1316 qualsep = "AND";
1317 queryoids[i] = pk_type;
1318 queryoids[j] = pk_type;
1319 }
1320 appendStringInfoString(&querybuf, qualbuf.data);
1321
1322 /* Prepare and save the plan */
1323 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys * 2, queryoids,
1324 &qkey, fk_rel, pk_rel, true);
1325 }
1326
1327 /*
1328 * We have a plan now. Run it to update the existing references.
1329 */
1330 ri_PerformCheck(riinfo, &qkey, qplan,
1331 fk_rel, pk_rel,
1332 old_row, new_row,
1333 true, /* must detect new rows */
1334 SPI_OK_UPDATE);
1335
1336 if (SPI_finish() != SPI_OK_FINISH)
1337 elog(ERROR, "SPI_finish failed");
1338
1339 heap_close(fk_rel, RowExclusiveLock);
1340
1341 return PointerGetDatum(NULL);
1342
1343 /*
1344 * Handle MATCH PARTIAL cascade update.
1345 */
1346 case FKCONSTR_MATCH_PARTIAL:
1347 ereport(ERROR,
1348 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1349 errmsg("MATCH PARTIAL not yet implemented")));
1350 return PointerGetDatum(NULL);
1351
1352 default:
1353 elog(ERROR, "unrecognized confmatchtype: %d",
1354 riinfo->confmatchtype);
1355 break;
1356 }
1357
1358 /* Never reached */
1359 return PointerGetDatum(NULL);
1360 }
1361
1362
1363 /* ----------
1364 * RI_FKey_setnull_del -
1365 *
1366 * Set foreign key references to NULL values at delete event on PK table.
1367 * ----------
1368 */
1369 Datum
RI_FKey_setnull_del(PG_FUNCTION_ARGS)1370 RI_FKey_setnull_del(PG_FUNCTION_ARGS)
1371 {
1372 TriggerData *trigdata = (TriggerData *) fcinfo->context;
1373 const RI_ConstraintInfo *riinfo;
1374 Relation fk_rel;
1375 Relation pk_rel;
1376 HeapTuple old_row;
1377 RI_QueryKey qkey;
1378 SPIPlanPtr qplan;
1379 int i;
1380
1381 /*
1382 * Check that this is a valid trigger call on the right time and event.
1383 */
1384 ri_CheckTrigger(fcinfo, "RI_FKey_setnull_del", RI_TRIGTYPE_DELETE);
1385
1386 /*
1387 * Get arguments.
1388 */
1389 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
1390 trigdata->tg_relation, true);
1391
1392 /*
1393 * Get the relation descriptors of the FK and PK tables and the old tuple.
1394 *
1395 * fk_rel is opened in RowExclusiveLock mode since that's what our
1396 * eventual UPDATE will get on it.
1397 */
1398 fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
1399 pk_rel = trigdata->tg_relation;
1400 old_row = trigdata->tg_trigtuple;
1401
1402 switch (riinfo->confmatchtype)
1403 {
1404 /* ----------
1405 * SQL:2008 15.17 <Execution of referential actions>
1406 * General rules 9) a) ii):
1407 * MATCH SIMPLE/FULL
1408 * ... ON DELETE SET NULL
1409 * ----------
1410 */
1411 case FKCONSTR_MATCH_SIMPLE:
1412 case FKCONSTR_MATCH_FULL:
1413 switch (ri_NullCheck(old_row, riinfo, true))
1414 {
1415 case RI_KEYS_ALL_NULL:
1416 case RI_KEYS_SOME_NULL:
1417
1418 /*
1419 * No check needed - there cannot be any reference to old
1420 * key if it contains a NULL
1421 */
1422 heap_close(fk_rel, RowExclusiveLock);
1423 return PointerGetDatum(NULL);
1424
1425 case RI_KEYS_NONE_NULL:
1426
1427 /*
1428 * Have a full qualified key - continue below
1429 */
1430 break;
1431 }
1432
1433 if (SPI_connect() != SPI_OK_CONNECT)
1434 elog(ERROR, "SPI_connect failed");
1435
1436 /*
1437 * Fetch or prepare a saved plan for the set null delete operation
1438 */
1439 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETNULL_DEL_DOUPDATE);
1440
1441 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1442 {
1443 StringInfoData querybuf;
1444 StringInfoData qualbuf;
1445 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1446 char attname[MAX_QUOTED_NAME_LEN];
1447 char paramname[16];
1448 const char *querysep;
1449 const char *qualsep;
1450 Oid queryoids[RI_MAX_NUMKEYS];
1451
1452 /* ----------
1453 * The query string built is
1454 * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
1455 * WHERE $1 = fkatt1 [AND ...]
1456 * The type id's for the $ parameters are those of the
1457 * corresponding PK attributes.
1458 * ----------
1459 */
1460 initStringInfo(&querybuf);
1461 initStringInfo(&qualbuf);
1462 quoteRelationName(fkrelname, fk_rel);
1463 appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
1464 querysep = "";
1465 qualsep = "WHERE";
1466 for (i = 0; i < riinfo->nkeys; i++)
1467 {
1468 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
1469 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
1470
1471 quoteOneName(attname,
1472 RIAttName(fk_rel, riinfo->fk_attnums[i]));
1473 appendStringInfo(&querybuf,
1474 "%s %s = NULL",
1475 querysep, attname);
1476 sprintf(paramname, "$%d", i + 1);
1477 ri_GenerateQual(&qualbuf, qualsep,
1478 paramname, pk_type,
1479 riinfo->pf_eq_oprs[i],
1480 attname, fk_type);
1481 querysep = ",";
1482 qualsep = "AND";
1483 queryoids[i] = pk_type;
1484 }
1485 appendStringInfoString(&querybuf, qualbuf.data);
1486
1487 /* Prepare and save the plan */
1488 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
1489 &qkey, fk_rel, pk_rel, true);
1490 }
1491
1492 /*
1493 * We have a plan now. Run it to check for existing references.
1494 */
1495 ri_PerformCheck(riinfo, &qkey, qplan,
1496 fk_rel, pk_rel,
1497 old_row, NULL,
1498 true, /* must detect new rows */
1499 SPI_OK_UPDATE);
1500
1501 if (SPI_finish() != SPI_OK_FINISH)
1502 elog(ERROR, "SPI_finish failed");
1503
1504 heap_close(fk_rel, RowExclusiveLock);
1505
1506 return PointerGetDatum(NULL);
1507
1508 /*
1509 * Handle MATCH PARTIAL set null delete.
1510 */
1511 case FKCONSTR_MATCH_PARTIAL:
1512 ereport(ERROR,
1513 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1514 errmsg("MATCH PARTIAL not yet implemented")));
1515 return PointerGetDatum(NULL);
1516
1517 default:
1518 elog(ERROR, "unrecognized confmatchtype: %d",
1519 riinfo->confmatchtype);
1520 break;
1521 }
1522
1523 /* Never reached */
1524 return PointerGetDatum(NULL);
1525 }
1526
1527
1528 /* ----------
1529 * RI_FKey_setnull_upd -
1530 *
1531 * Set foreign key references to NULL at update event on PK table.
1532 * ----------
1533 */
1534 Datum
RI_FKey_setnull_upd(PG_FUNCTION_ARGS)1535 RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
1536 {
1537 TriggerData *trigdata = (TriggerData *) fcinfo->context;
1538 const RI_ConstraintInfo *riinfo;
1539 Relation fk_rel;
1540 Relation pk_rel;
1541 HeapTuple new_row;
1542 HeapTuple old_row;
1543 RI_QueryKey qkey;
1544 SPIPlanPtr qplan;
1545 int i;
1546
1547 /*
1548 * Check that this is a valid trigger call on the right time and event.
1549 */
1550 ri_CheckTrigger(fcinfo, "RI_FKey_setnull_upd", RI_TRIGTYPE_UPDATE);
1551
1552 /*
1553 * Get arguments.
1554 */
1555 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
1556 trigdata->tg_relation, true);
1557
1558 /*
1559 * Get the relation descriptors of the FK and PK tables and the old tuple.
1560 *
1561 * fk_rel is opened in RowExclusiveLock mode since that's what our
1562 * eventual UPDATE will get on it.
1563 */
1564 fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
1565 pk_rel = trigdata->tg_relation;
1566 new_row = trigdata->tg_newtuple;
1567 old_row = trigdata->tg_trigtuple;
1568
1569 switch (riinfo->confmatchtype)
1570 {
1571 /* ----------
1572 * SQL:2008 15.17 <Execution of referential actions>
1573 * General rules 10) a) ii):
1574 * MATCH SIMPLE/FULL
1575 * ... ON UPDATE SET NULL
1576 * ----------
1577 */
1578 case FKCONSTR_MATCH_SIMPLE:
1579 case FKCONSTR_MATCH_FULL:
1580 switch (ri_NullCheck(old_row, riinfo, true))
1581 {
1582 case RI_KEYS_ALL_NULL:
1583 case RI_KEYS_SOME_NULL:
1584
1585 /*
1586 * No check needed - there cannot be any reference to old
1587 * key if it contains a NULL
1588 */
1589 heap_close(fk_rel, RowExclusiveLock);
1590 return PointerGetDatum(NULL);
1591
1592 case RI_KEYS_NONE_NULL:
1593
1594 /*
1595 * Have a full qualified key - continue below
1596 */
1597 break;
1598 }
1599
1600 /*
1601 * No need to do anything if old and new keys are equal
1602 */
1603 if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
1604 {
1605 heap_close(fk_rel, RowExclusiveLock);
1606 return PointerGetDatum(NULL);
1607 }
1608
1609 if (SPI_connect() != SPI_OK_CONNECT)
1610 elog(ERROR, "SPI_connect failed");
1611
1612 /*
1613 * Fetch or prepare a saved plan for the set null update operation
1614 */
1615 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETNULL_UPD_DOUPDATE);
1616
1617 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1618 {
1619 StringInfoData querybuf;
1620 StringInfoData qualbuf;
1621 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1622 char attname[MAX_QUOTED_NAME_LEN];
1623 char paramname[16];
1624 const char *querysep;
1625 const char *qualsep;
1626 Oid queryoids[RI_MAX_NUMKEYS];
1627
1628 /* ----------
1629 * The query string built is
1630 * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
1631 * WHERE $1 = fkatt1 [AND ...]
1632 * The type id's for the $ parameters are those of the
1633 * corresponding PK attributes.
1634 * ----------
1635 */
1636 initStringInfo(&querybuf);
1637 initStringInfo(&qualbuf);
1638 quoteRelationName(fkrelname, fk_rel);
1639 appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
1640 querysep = "";
1641 qualsep = "WHERE";
1642 for (i = 0; i < riinfo->nkeys; i++)
1643 {
1644 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
1645 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
1646
1647 quoteOneName(attname,
1648 RIAttName(fk_rel, riinfo->fk_attnums[i]));
1649 appendStringInfo(&querybuf,
1650 "%s %s = NULL",
1651 querysep, attname);
1652 sprintf(paramname, "$%d", i + 1);
1653 ri_GenerateQual(&qualbuf, qualsep,
1654 paramname, pk_type,
1655 riinfo->pf_eq_oprs[i],
1656 attname, fk_type);
1657 querysep = ",";
1658 qualsep = "AND";
1659 queryoids[i] = pk_type;
1660 }
1661 appendStringInfoString(&querybuf, qualbuf.data);
1662
1663 /* Prepare and save the plan */
1664 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
1665 &qkey, fk_rel, pk_rel, true);
1666 }
1667
1668 /*
1669 * We have a plan now. Run it to update the existing references.
1670 */
1671 ri_PerformCheck(riinfo, &qkey, qplan,
1672 fk_rel, pk_rel,
1673 old_row, NULL,
1674 true, /* must detect new rows */
1675 SPI_OK_UPDATE);
1676
1677 if (SPI_finish() != SPI_OK_FINISH)
1678 elog(ERROR, "SPI_finish failed");
1679
1680 heap_close(fk_rel, RowExclusiveLock);
1681
1682 return PointerGetDatum(NULL);
1683
1684 /*
1685 * Handle MATCH PARTIAL set null update.
1686 */
1687 case FKCONSTR_MATCH_PARTIAL:
1688 ereport(ERROR,
1689 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1690 errmsg("MATCH PARTIAL not yet implemented")));
1691 return PointerGetDatum(NULL);
1692
1693 default:
1694 elog(ERROR, "unrecognized confmatchtype: %d",
1695 riinfo->confmatchtype);
1696 break;
1697 }
1698
1699 /* Never reached */
1700 return PointerGetDatum(NULL);
1701 }
1702
1703
1704 /* ----------
1705 * RI_FKey_setdefault_del -
1706 *
1707 * Set foreign key references to defaults at delete event on PK table.
1708 * ----------
1709 */
1710 Datum
RI_FKey_setdefault_del(PG_FUNCTION_ARGS)1711 RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
1712 {
1713 TriggerData *trigdata = (TriggerData *) fcinfo->context;
1714 const RI_ConstraintInfo *riinfo;
1715 Relation fk_rel;
1716 Relation pk_rel;
1717 HeapTuple old_row;
1718 RI_QueryKey qkey;
1719 SPIPlanPtr qplan;
1720
1721 /*
1722 * Check that this is a valid trigger call on the right time and event.
1723 */
1724 ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_del", RI_TRIGTYPE_DELETE);
1725
1726 /*
1727 * Get arguments.
1728 */
1729 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
1730 trigdata->tg_relation, true);
1731
1732 /*
1733 * Get the relation descriptors of the FK and PK tables and the old tuple.
1734 *
1735 * fk_rel is opened in RowExclusiveLock mode since that's what our
1736 * eventual UPDATE will get on it.
1737 */
1738 fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
1739 pk_rel = trigdata->tg_relation;
1740 old_row = trigdata->tg_trigtuple;
1741
1742 switch (riinfo->confmatchtype)
1743 {
1744 /* ----------
1745 * SQL:2008 15.17 <Execution of referential actions>
1746 * General rules 9) a) iii):
1747 * MATCH SIMPLE/FULL
1748 * ... ON DELETE SET DEFAULT
1749 * ----------
1750 */
1751 case FKCONSTR_MATCH_SIMPLE:
1752 case FKCONSTR_MATCH_FULL:
1753 switch (ri_NullCheck(old_row, riinfo, true))
1754 {
1755 case RI_KEYS_ALL_NULL:
1756 case RI_KEYS_SOME_NULL:
1757
1758 /*
1759 * No check needed - there cannot be any reference to old
1760 * key if it contains a NULL
1761 */
1762 heap_close(fk_rel, RowExclusiveLock);
1763 return PointerGetDatum(NULL);
1764
1765 case RI_KEYS_NONE_NULL:
1766
1767 /*
1768 * Have a full qualified key - continue below
1769 */
1770 break;
1771 }
1772
1773 if (SPI_connect() != SPI_OK_CONNECT)
1774 elog(ERROR, "SPI_connect failed");
1775
1776 /*
1777 * Fetch or prepare a saved plan for the set default delete
1778 * operation
1779 */
1780 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETDEFAULT_DEL_DOUPDATE);
1781
1782 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1783 {
1784 StringInfoData querybuf;
1785 StringInfoData qualbuf;
1786 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1787 char attname[MAX_QUOTED_NAME_LEN];
1788 char paramname[16];
1789 const char *querysep;
1790 const char *qualsep;
1791 Oid queryoids[RI_MAX_NUMKEYS];
1792 int i;
1793
1794 /* ----------
1795 * The query string built is
1796 * UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]
1797 * WHERE $1 = fkatt1 [AND ...]
1798 * The type id's for the $ parameters are those of the
1799 * corresponding PK attributes.
1800 * ----------
1801 */
1802 initStringInfo(&querybuf);
1803 initStringInfo(&qualbuf);
1804 quoteRelationName(fkrelname, fk_rel);
1805 appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
1806 querysep = "";
1807 qualsep = "WHERE";
1808 for (i = 0; i < riinfo->nkeys; i++)
1809 {
1810 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
1811 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
1812
1813 quoteOneName(attname,
1814 RIAttName(fk_rel, riinfo->fk_attnums[i]));
1815 appendStringInfo(&querybuf,
1816 "%s %s = DEFAULT",
1817 querysep, attname);
1818 sprintf(paramname, "$%d", i + 1);
1819 ri_GenerateQual(&qualbuf, qualsep,
1820 paramname, pk_type,
1821 riinfo->pf_eq_oprs[i],
1822 attname, fk_type);
1823 querysep = ",";
1824 qualsep = "AND";
1825 queryoids[i] = pk_type;
1826 }
1827 appendStringInfoString(&querybuf, qualbuf.data);
1828
1829 /* Prepare and save the plan */
1830 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
1831 &qkey, fk_rel, pk_rel, true);
1832 }
1833
1834 /*
1835 * We have a plan now. Run it to update the existing references.
1836 */
1837 ri_PerformCheck(riinfo, &qkey, qplan,
1838 fk_rel, pk_rel,
1839 old_row, NULL,
1840 true, /* must detect new rows */
1841 SPI_OK_UPDATE);
1842
1843 if (SPI_finish() != SPI_OK_FINISH)
1844 elog(ERROR, "SPI_finish failed");
1845
1846 heap_close(fk_rel, RowExclusiveLock);
1847
1848 /*
1849 * If we just deleted the PK row whose key was equal to the FK
1850 * columns' default values, and a referencing row exists in the FK
1851 * table, we would have updated that row to the same values it
1852 * already had --- and RI_FKey_fk_upd_check_required would hence
1853 * believe no check is necessary. So we need to do another lookup
1854 * now and in case a reference still exists, abort the operation.
1855 * That is already implemented in the NO ACTION trigger, so just
1856 * run it. (This recheck is only needed in the SET DEFAULT case,
1857 * since CASCADE would remove such rows, while SET NULL is certain
1858 * to result in rows that satisfy the FK constraint.)
1859 */
1860 RI_FKey_noaction_del(fcinfo);
1861
1862 return PointerGetDatum(NULL);
1863
1864 /*
1865 * Handle MATCH PARTIAL set default delete.
1866 */
1867 case FKCONSTR_MATCH_PARTIAL:
1868 ereport(ERROR,
1869 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1870 errmsg("MATCH PARTIAL not yet implemented")));
1871 return PointerGetDatum(NULL);
1872
1873 default:
1874 elog(ERROR, "unrecognized confmatchtype: %d",
1875 riinfo->confmatchtype);
1876 break;
1877 }
1878
1879 /* Never reached */
1880 return PointerGetDatum(NULL);
1881 }
1882
1883
1884 /* ----------
1885 * RI_FKey_setdefault_upd -
1886 *
1887 * Set foreign key references to defaults at update event on PK table.
1888 * ----------
1889 */
1890 Datum
RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)1891 RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
1892 {
1893 TriggerData *trigdata = (TriggerData *) fcinfo->context;
1894 const RI_ConstraintInfo *riinfo;
1895 Relation fk_rel;
1896 Relation pk_rel;
1897 HeapTuple new_row;
1898 HeapTuple old_row;
1899 RI_QueryKey qkey;
1900 SPIPlanPtr qplan;
1901
1902 /*
1903 * Check that this is a valid trigger call on the right time and event.
1904 */
1905 ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_upd", RI_TRIGTYPE_UPDATE);
1906
1907 /*
1908 * Get arguments.
1909 */
1910 riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
1911 trigdata->tg_relation, true);
1912
1913 /*
1914 * Get the relation descriptors of the FK and PK tables and the old tuple.
1915 *
1916 * fk_rel is opened in RowExclusiveLock mode since that's what our
1917 * eventual UPDATE will get on it.
1918 */
1919 fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
1920 pk_rel = trigdata->tg_relation;
1921 new_row = trigdata->tg_newtuple;
1922 old_row = trigdata->tg_trigtuple;
1923
1924 switch (riinfo->confmatchtype)
1925 {
1926 /* ----------
1927 * SQL:2008 15.17 <Execution of referential actions>
1928 * General rules 10) a) iii):
1929 * MATCH SIMPLE/FULL
1930 * ... ON UPDATE SET DEFAULT
1931 * ----------
1932 */
1933 case FKCONSTR_MATCH_SIMPLE:
1934 case FKCONSTR_MATCH_FULL:
1935 switch (ri_NullCheck(old_row, riinfo, true))
1936 {
1937 case RI_KEYS_ALL_NULL:
1938 case RI_KEYS_SOME_NULL:
1939
1940 /*
1941 * No check needed - there cannot be any reference to old
1942 * key if it contains a NULL
1943 */
1944 heap_close(fk_rel, RowExclusiveLock);
1945 return PointerGetDatum(NULL);
1946
1947 case RI_KEYS_NONE_NULL:
1948
1949 /*
1950 * Have a full qualified key - continue below
1951 */
1952 break;
1953 }
1954
1955 /*
1956 * No need to do anything if old and new keys are equal
1957 */
1958 if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
1959 {
1960 heap_close(fk_rel, RowExclusiveLock);
1961 return PointerGetDatum(NULL);
1962 }
1963
1964 if (SPI_connect() != SPI_OK_CONNECT)
1965 elog(ERROR, "SPI_connect failed");
1966
1967 /*
1968 * Fetch or prepare a saved plan for the set default update
1969 * operation
1970 */
1971 ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETDEFAULT_UPD_DOUPDATE);
1972
1973 if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1974 {
1975 StringInfoData querybuf;
1976 StringInfoData qualbuf;
1977 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1978 char attname[MAX_QUOTED_NAME_LEN];
1979 char paramname[16];
1980 const char *querysep;
1981 const char *qualsep;
1982 Oid queryoids[RI_MAX_NUMKEYS];
1983 int i;
1984
1985 /* ----------
1986 * The query string built is
1987 * UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]
1988 * WHERE $1 = fkatt1 [AND ...]
1989 * The type id's for the $ parameters are those of the
1990 * corresponding PK attributes.
1991 * ----------
1992 */
1993 initStringInfo(&querybuf);
1994 initStringInfo(&qualbuf);
1995 quoteRelationName(fkrelname, fk_rel);
1996 appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
1997 querysep = "";
1998 qualsep = "WHERE";
1999 for (i = 0; i < riinfo->nkeys; i++)
2000 {
2001 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
2002 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
2003
2004 quoteOneName(attname,
2005 RIAttName(fk_rel, riinfo->fk_attnums[i]));
2006 appendStringInfo(&querybuf,
2007 "%s %s = DEFAULT",
2008 querysep, attname);
2009 sprintf(paramname, "$%d", i + 1);
2010 ri_GenerateQual(&qualbuf, qualsep,
2011 paramname, pk_type,
2012 riinfo->pf_eq_oprs[i],
2013 attname, fk_type);
2014 querysep = ",";
2015 qualsep = "AND";
2016 queryoids[i] = pk_type;
2017 }
2018 appendStringInfoString(&querybuf, qualbuf.data);
2019
2020 /* Prepare and save the plan */
2021 qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
2022 &qkey, fk_rel, pk_rel, true);
2023 }
2024
2025 /*
2026 * We have a plan now. Run it to update the existing references.
2027 */
2028 ri_PerformCheck(riinfo, &qkey, qplan,
2029 fk_rel, pk_rel,
2030 old_row, NULL,
2031 true, /* must detect new rows */
2032 SPI_OK_UPDATE);
2033
2034 if (SPI_finish() != SPI_OK_FINISH)
2035 elog(ERROR, "SPI_finish failed");
2036
2037 heap_close(fk_rel, RowExclusiveLock);
2038
2039 /*
2040 * If we just updated the PK row whose key was equal to the FK
2041 * columns' default values, and a referencing row exists in the FK
2042 * table, we would have updated that row to the same values it
2043 * already had --- and RI_FKey_fk_upd_check_required would hence
2044 * believe no check is necessary. So we need to do another lookup
2045 * now and in case a reference still exists, abort the operation.
2046 * That is already implemented in the NO ACTION trigger, so just
2047 * run it. (This recheck is only needed in the SET DEFAULT case,
2048 * since CASCADE must change the FK key values, while SET NULL is
2049 * certain to result in rows that satisfy the FK constraint.)
2050 */
2051 RI_FKey_noaction_upd(fcinfo);
2052
2053 return PointerGetDatum(NULL);
2054
2055 /*
2056 * Handle MATCH PARTIAL set default update.
2057 */
2058 case FKCONSTR_MATCH_PARTIAL:
2059 ereport(ERROR,
2060 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2061 errmsg("MATCH PARTIAL not yet implemented")));
2062 return PointerGetDatum(NULL);
2063
2064 default:
2065 elog(ERROR, "unrecognized confmatchtype: %d",
2066 riinfo->confmatchtype);
2067 break;
2068 }
2069
2070 /* Never reached */
2071 return PointerGetDatum(NULL);
2072 }
2073
2074
2075 /* ----------
2076 * RI_FKey_pk_upd_check_required -
2077 *
2078 * Check if we really need to fire the RI trigger for an update to a PK
2079 * relation. This is called by the AFTER trigger queue manager to see if
2080 * it can skip queuing an instance of an RI trigger. Returns TRUE if the
2081 * trigger must be fired, FALSE if we can prove the constraint will still
2082 * be satisfied.
2083 * ----------
2084 */
2085 bool
RI_FKey_pk_upd_check_required(Trigger * trigger,Relation pk_rel,HeapTuple old_row,HeapTuple new_row)2086 RI_FKey_pk_upd_check_required(Trigger *trigger, Relation pk_rel,
2087 HeapTuple old_row, HeapTuple new_row)
2088 {
2089 const RI_ConstraintInfo *riinfo;
2090
2091 /*
2092 * Get arguments.
2093 */
2094 riinfo = ri_FetchConstraintInfo(trigger, pk_rel, true);
2095
2096 switch (riinfo->confmatchtype)
2097 {
2098 case FKCONSTR_MATCH_SIMPLE:
2099 case FKCONSTR_MATCH_FULL:
2100
2101 /*
2102 * If any old key value is NULL, the row could not have been
2103 * referenced by an FK row, so no check is needed.
2104 */
2105 if (ri_NullCheck(old_row, riinfo, true) != RI_KEYS_NONE_NULL)
2106 return false;
2107
2108 /* If all old and new key values are equal, no check is needed */
2109 if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
2110 return false;
2111
2112 /* Else we need to fire the trigger. */
2113 return true;
2114
2115 /* Handle MATCH PARTIAL check. */
2116 case FKCONSTR_MATCH_PARTIAL:
2117 ereport(ERROR,
2118 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2119 errmsg("MATCH PARTIAL not yet implemented")));
2120 break;
2121
2122 default:
2123 elog(ERROR, "unrecognized confmatchtype: %d",
2124 riinfo->confmatchtype);
2125 break;
2126 }
2127
2128 /* Never reached */
2129 return false;
2130 }
2131
2132 /* ----------
2133 * RI_FKey_fk_upd_check_required -
2134 *
2135 * Check if we really need to fire the RI trigger for an update to an FK
2136 * relation. This is called by the AFTER trigger queue manager to see if
2137 * it can skip queuing an instance of an RI trigger. Returns TRUE if the
2138 * trigger must be fired, FALSE if we can prove the constraint will still
2139 * be satisfied.
2140 * ----------
2141 */
2142 bool
RI_FKey_fk_upd_check_required(Trigger * trigger,Relation fk_rel,HeapTuple old_row,HeapTuple new_row)2143 RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
2144 HeapTuple old_row, HeapTuple new_row)
2145 {
2146 const RI_ConstraintInfo *riinfo;
2147
2148 /*
2149 * Get arguments.
2150 */
2151 riinfo = ri_FetchConstraintInfo(trigger, fk_rel, false);
2152
2153 switch (riinfo->confmatchtype)
2154 {
2155 case FKCONSTR_MATCH_SIMPLE:
2156
2157 /*
2158 * If any new key value is NULL, the row must satisfy the
2159 * constraint, so no check is needed.
2160 */
2161 if (ri_NullCheck(new_row, riinfo, false) != RI_KEYS_NONE_NULL)
2162 return false;
2163
2164 /*
2165 * If the original row was inserted by our own transaction, we
2166 * must fire the trigger whether or not the keys are equal. This
2167 * is because our UPDATE will invalidate the INSERT so that the
2168 * INSERT RI trigger will not do anything; so we had better do the
2169 * UPDATE check. (We could skip this if we knew the INSERT
2170 * trigger already fired, but there is no easy way to know that.)
2171 */
2172 if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(old_row->t_data)))
2173 return true;
2174
2175 /* If all old and new key values are equal, no check is needed */
2176 if (ri_KeysEqual(fk_rel, old_row, new_row, riinfo, false))
2177 return false;
2178
2179 /* Else we need to fire the trigger. */
2180 return true;
2181
2182 case FKCONSTR_MATCH_FULL:
2183
2184 /*
2185 * If all new key values are NULL, the row must satisfy the
2186 * constraint, so no check is needed. On the other hand, if only
2187 * some of them are NULL, the row must fail the constraint. We
2188 * must not throw error here, because the row might get
2189 * invalidated before the constraint is to be checked, but we
2190 * should queue the event to apply the check later.
2191 */
2192 switch (ri_NullCheck(new_row, riinfo, false))
2193 {
2194 case RI_KEYS_ALL_NULL:
2195 return false;
2196 case RI_KEYS_SOME_NULL:
2197 return true;
2198 case RI_KEYS_NONE_NULL:
2199 break; /* continue with the check */
2200 }
2201
2202 /*
2203 * If the original row was inserted by our own transaction, we
2204 * must fire the trigger whether or not the keys are equal. This
2205 * is because our UPDATE will invalidate the INSERT so that the
2206 * INSERT RI trigger will not do anything; so we had better do the
2207 * UPDATE check. (We could skip this if we knew the INSERT
2208 * trigger already fired, but there is no easy way to know that.)
2209 */
2210 if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(old_row->t_data)))
2211 return true;
2212
2213 /* If all old and new key values are equal, no check is needed */
2214 if (ri_KeysEqual(fk_rel, old_row, new_row, riinfo, false))
2215 return false;
2216
2217 /* Else we need to fire the trigger. */
2218 return true;
2219
2220 /* Handle MATCH PARTIAL check. */
2221 case FKCONSTR_MATCH_PARTIAL:
2222 ereport(ERROR,
2223 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2224 errmsg("MATCH PARTIAL not yet implemented")));
2225 break;
2226
2227 default:
2228 elog(ERROR, "unrecognized confmatchtype: %d",
2229 riinfo->confmatchtype);
2230 break;
2231 }
2232
2233 /* Never reached */
2234 return false;
2235 }
2236
2237 /* ----------
2238 * RI_Initial_Check -
2239 *
2240 * Check an entire table for non-matching values using a single query.
2241 * This is not a trigger procedure, but is called during ALTER TABLE
2242 * ADD FOREIGN KEY to validate the initial table contents.
2243 *
2244 * We expect that the caller has made provision to prevent any problems
2245 * caused by concurrent actions. This could be either by locking rel and
2246 * pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring
2247 * that triggers implementing the checks are already active.
2248 * Hence, we do not need to lock individual rows for the check.
2249 *
2250 * If the check fails because the current user doesn't have permissions
2251 * to read both tables, return false to let our caller know that they will
2252 * need to do something else to check the constraint.
2253 * ----------
2254 */
2255 bool
RI_Initial_Check(Trigger * trigger,Relation fk_rel,Relation pk_rel)2256 RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
2257 {
2258 const RI_ConstraintInfo *riinfo;
2259 StringInfoData querybuf;
2260 char pkrelname[MAX_QUOTED_REL_NAME_LEN];
2261 char fkrelname[MAX_QUOTED_REL_NAME_LEN];
2262 char pkattname[MAX_QUOTED_NAME_LEN + 3];
2263 char fkattname[MAX_QUOTED_NAME_LEN + 3];
2264 RangeTblEntry *pkrte;
2265 RangeTblEntry *fkrte;
2266 const char *sep;
2267 int i;
2268 int save_nestlevel;
2269 char workmembuf[32];
2270 int spi_result;
2271 SPIPlanPtr qplan;
2272
2273 /* Fetch constraint info. */
2274 riinfo = ri_FetchConstraintInfo(trigger, fk_rel, false);
2275
2276 /*
2277 * Check to make sure current user has enough permissions to do the test
2278 * query. (If not, caller can fall back to the trigger method, which
2279 * works because it changes user IDs on the fly.)
2280 *
2281 * XXX are there any other show-stopper conditions to check?
2282 */
2283 pkrte = makeNode(RangeTblEntry);
2284 pkrte->rtekind = RTE_RELATION;
2285 pkrte->relid = RelationGetRelid(pk_rel);
2286 pkrte->relkind = pk_rel->rd_rel->relkind;
2287 pkrte->requiredPerms = ACL_SELECT;
2288
2289 fkrte = makeNode(RangeTblEntry);
2290 fkrte->rtekind = RTE_RELATION;
2291 fkrte->relid = RelationGetRelid(fk_rel);
2292 fkrte->relkind = fk_rel->rd_rel->relkind;
2293 fkrte->requiredPerms = ACL_SELECT;
2294
2295 for (i = 0; i < riinfo->nkeys; i++)
2296 {
2297 int attno;
2298
2299 attno = riinfo->pk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
2300 pkrte->selectedCols = bms_add_member(pkrte->selectedCols, attno);
2301
2302 attno = riinfo->fk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
2303 fkrte->selectedCols = bms_add_member(fkrte->selectedCols, attno);
2304 }
2305
2306 if (!ExecCheckRTPerms(list_make2(fkrte, pkrte), false))
2307 return false;
2308
2309 /*
2310 * Also punt if RLS is enabled on either table unless this role has the
2311 * bypassrls right or is the table owner of the table(s) involved which
2312 * have RLS enabled.
2313 */
2314 if (!has_bypassrls_privilege(GetUserId()) &&
2315 ((pk_rel->rd_rel->relrowsecurity &&
2316 !pg_class_ownercheck(pkrte->relid, GetUserId())) ||
2317 (fk_rel->rd_rel->relrowsecurity &&
2318 !pg_class_ownercheck(fkrte->relid, GetUserId()))))
2319 return false;
2320
2321 /*----------
2322 * The query string built is:
2323 * SELECT fk.keycols FROM ONLY relname fk
2324 * LEFT OUTER JOIN ONLY pkrelname pk
2325 * ON (pk.pkkeycol1=fk.keycol1 [AND ...])
2326 * WHERE pk.pkkeycol1 IS NULL AND
2327 * For MATCH SIMPLE:
2328 * (fk.keycol1 IS NOT NULL [AND ...])
2329 * For MATCH FULL:
2330 * (fk.keycol1 IS NOT NULL [OR ...])
2331 *
2332 * We attach COLLATE clauses to the operators when comparing columns
2333 * that have different collations.
2334 *----------
2335 */
2336 initStringInfo(&querybuf);
2337 appendStringInfoString(&querybuf, "SELECT ");
2338 sep = "";
2339 for (i = 0; i < riinfo->nkeys; i++)
2340 {
2341 quoteOneName(fkattname,
2342 RIAttName(fk_rel, riinfo->fk_attnums[i]));
2343 appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname);
2344 sep = ", ";
2345 }
2346
2347 quoteRelationName(pkrelname, pk_rel);
2348 quoteRelationName(fkrelname, fk_rel);
2349 appendStringInfo(&querybuf,
2350 " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON",
2351 fkrelname, pkrelname);
2352
2353 strcpy(pkattname, "pk.");
2354 strcpy(fkattname, "fk.");
2355 sep = "(";
2356 for (i = 0; i < riinfo->nkeys; i++)
2357 {
2358 Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
2359 Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
2360 Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
2361 Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
2362
2363 quoteOneName(pkattname + 3,
2364 RIAttName(pk_rel, riinfo->pk_attnums[i]));
2365 quoteOneName(fkattname + 3,
2366 RIAttName(fk_rel, riinfo->fk_attnums[i]));
2367 ri_GenerateQual(&querybuf, sep,
2368 pkattname, pk_type,
2369 riinfo->pf_eq_oprs[i],
2370 fkattname, fk_type);
2371 if (pk_coll != fk_coll)
2372 ri_GenerateQualCollation(&querybuf, pk_coll);
2373 sep = "AND";
2374 }
2375
2376 /*
2377 * It's sufficient to test any one pk attribute for null to detect a join
2378 * failure.
2379 */
2380 quoteOneName(pkattname, RIAttName(pk_rel, riinfo->pk_attnums[0]));
2381 appendStringInfo(&querybuf, ") WHERE pk.%s IS NULL AND (", pkattname);
2382
2383 sep = "";
2384 for (i = 0; i < riinfo->nkeys; i++)
2385 {
2386 quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i]));
2387 appendStringInfo(&querybuf,
2388 "%sfk.%s IS NOT NULL",
2389 sep, fkattname);
2390 switch (riinfo->confmatchtype)
2391 {
2392 case FKCONSTR_MATCH_SIMPLE:
2393 sep = " AND ";
2394 break;
2395 case FKCONSTR_MATCH_FULL:
2396 sep = " OR ";
2397 break;
2398 case FKCONSTR_MATCH_PARTIAL:
2399 ereport(ERROR,
2400 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2401 errmsg("MATCH PARTIAL not yet implemented")));
2402 break;
2403 default:
2404 elog(ERROR, "unrecognized confmatchtype: %d",
2405 riinfo->confmatchtype);
2406 break;
2407 }
2408 }
2409 appendStringInfoChar(&querybuf, ')');
2410
2411 /*
2412 * Temporarily increase work_mem so that the check query can be executed
2413 * more efficiently. It seems okay to do this because the query is simple
2414 * enough to not use a multiple of work_mem, and one typically would not
2415 * have many large foreign-key validations happening concurrently. So
2416 * this seems to meet the criteria for being considered a "maintenance"
2417 * operation, and accordingly we use maintenance_work_mem.
2418 *
2419 * We use the equivalent of a function SET option to allow the setting to
2420 * persist for exactly the duration of the check query. guc.c also takes
2421 * care of undoing the setting on error.
2422 */
2423 save_nestlevel = NewGUCNestLevel();
2424
2425 snprintf(workmembuf, sizeof(workmembuf), "%d", maintenance_work_mem);
2426 (void) set_config_option("work_mem", workmembuf,
2427 PGC_USERSET, PGC_S_SESSION,
2428 GUC_ACTION_SAVE, true, 0, false);
2429
2430 if (SPI_connect() != SPI_OK_CONNECT)
2431 elog(ERROR, "SPI_connect failed");
2432
2433 /*
2434 * Generate the plan. We don't need to cache it, and there are no
2435 * arguments to the plan.
2436 */
2437 qplan = SPI_prepare(querybuf.data, 0, NULL);
2438
2439 if (qplan == NULL)
2440 elog(ERROR, "SPI_prepare returned %d for %s",
2441 SPI_result, querybuf.data);
2442
2443 /*
2444 * Run the plan. For safety we force a current snapshot to be used. (In
2445 * transaction-snapshot mode, this arguably violates transaction isolation
2446 * rules, but we really haven't got much choice.) We don't need to
2447 * register the snapshot, because SPI_execute_snapshot will see to it. We
2448 * need at most one tuple returned, so pass limit = 1.
2449 */
2450 spi_result = SPI_execute_snapshot(qplan,
2451 NULL, NULL,
2452 GetLatestSnapshot(),
2453 InvalidSnapshot,
2454 true, false, 1);
2455
2456 /* Check result */
2457 if (spi_result != SPI_OK_SELECT)
2458 elog(ERROR, "SPI_execute_snapshot returned %d", spi_result);
2459
2460 /* Did we find a tuple violating the constraint? */
2461 if (SPI_processed > 0)
2462 {
2463 HeapTuple tuple = SPI_tuptable->vals[0];
2464 TupleDesc tupdesc = SPI_tuptable->tupdesc;
2465 RI_ConstraintInfo fake_riinfo;
2466
2467 /*
2468 * The columns to look at in the result tuple are 1..N, not whatever
2469 * they are in the fk_rel. Hack up riinfo so that the subroutines
2470 * called here will behave properly.
2471 *
2472 * In addition to this, we have to pass the correct tupdesc to
2473 * ri_ReportViolation, overriding its normal habit of using the pk_rel
2474 * or fk_rel's tupdesc.
2475 */
2476 memcpy(&fake_riinfo, riinfo, sizeof(RI_ConstraintInfo));
2477 for (i = 0; i < fake_riinfo.nkeys; i++)
2478 fake_riinfo.fk_attnums[i] = i + 1;
2479
2480 /*
2481 * If it's MATCH FULL, and there are any nulls in the FK keys,
2482 * complain about that rather than the lack of a match. MATCH FULL
2483 * disallows partially-null FK rows.
2484 */
2485 if (fake_riinfo.confmatchtype == FKCONSTR_MATCH_FULL &&
2486 ri_NullCheck(tuple, &fake_riinfo, false) != RI_KEYS_NONE_NULL)
2487 ereport(ERROR,
2488 (errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
2489 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
2490 RelationGetRelationName(fk_rel),
2491 NameStr(fake_riinfo.conname)),
2492 errdetail("MATCH FULL does not allow mixing of null and nonnull key values."),
2493 errtableconstraint(fk_rel,
2494 NameStr(fake_riinfo.conname))));
2495
2496 /*
2497 * We tell ri_ReportViolation we were doing the RI_PLAN_CHECK_LOOKUPPK
2498 * query, which isn't true, but will cause it to use
2499 * fake_riinfo.fk_attnums as we need.
2500 */
2501 ri_ReportViolation(&fake_riinfo,
2502 pk_rel, fk_rel,
2503 tuple, tupdesc,
2504 RI_PLAN_CHECK_LOOKUPPK, false);
2505 }
2506
2507 if (SPI_finish() != SPI_OK_FINISH)
2508 elog(ERROR, "SPI_finish failed");
2509
2510 /*
2511 * Restore work_mem.
2512 */
2513 AtEOXact_GUC(true, save_nestlevel);
2514
2515 return true;
2516 }
2517
2518
2519 /* ----------
2520 * Local functions below
2521 * ----------
2522 */
2523
2524
2525 /*
2526 * quoteOneName --- safely quote a single SQL name
2527 *
2528 * buffer must be MAX_QUOTED_NAME_LEN long (includes room for \0)
2529 */
2530 static void
quoteOneName(char * buffer,const char * name)2531 quoteOneName(char *buffer, const char *name)
2532 {
2533 /* Rather than trying to be smart, just always quote it. */
2534 *buffer++ = '"';
2535 while (*name)
2536 {
2537 if (*name == '"')
2538 *buffer++ = '"';
2539 *buffer++ = *name++;
2540 }
2541 *buffer++ = '"';
2542 *buffer = '\0';
2543 }
2544
2545 /*
2546 * quoteRelationName --- safely quote a fully qualified relation name
2547 *
2548 * buffer must be MAX_QUOTED_REL_NAME_LEN long (includes room for \0)
2549 */
2550 static void
quoteRelationName(char * buffer,Relation rel)2551 quoteRelationName(char *buffer, Relation rel)
2552 {
2553 quoteOneName(buffer, get_namespace_name(RelationGetNamespace(rel)));
2554 buffer += strlen(buffer);
2555 *buffer++ = '.';
2556 quoteOneName(buffer, RelationGetRelationName(rel));
2557 }
2558
2559 /*
2560 * ri_GenerateQual --- generate a WHERE clause equating two variables
2561 *
2562 * This basically appends " sep leftop op rightop" to buf, adding casts
2563 * and schema qualification as needed to ensure that the parser will select
2564 * the operator we specify. leftop and rightop should be parenthesized
2565 * if they aren't variables or parameters.
2566 */
2567 static void
ri_GenerateQual(StringInfo buf,const char * sep,const char * leftop,Oid leftoptype,Oid opoid,const char * rightop,Oid rightoptype)2568 ri_GenerateQual(StringInfo buf,
2569 const char *sep,
2570 const char *leftop, Oid leftoptype,
2571 Oid opoid,
2572 const char *rightop, Oid rightoptype)
2573 {
2574 appendStringInfo(buf, " %s ", sep);
2575 generate_operator_clause(buf, leftop, leftoptype, opoid,
2576 rightop, rightoptype);
2577 }
2578
2579 /*
2580 * ri_GenerateQualCollation --- add a COLLATE spec to a WHERE clause
2581 *
2582 * At present, we intentionally do not use this function for RI queries that
2583 * compare a variable to a $n parameter. Since parameter symbols always have
2584 * default collation, the effect will be to use the variable's collation.
2585 * Now that is only strictly correct when testing the referenced column, since
2586 * the SQL standard specifies that RI comparisons should use the referenced
2587 * column's collation. However, so long as all collations have the same
2588 * notion of equality (which they do, because texteq reduces to bitwise
2589 * equality), there's no visible semantic impact from using the referencing
2590 * column's collation when testing it, and this is a good thing to do because
2591 * it lets us use a normal index on the referencing column. However, we do
2592 * have to use this function when directly comparing the referencing and
2593 * referenced columns, if they are of different collations; else the parser
2594 * will fail to resolve the collation to use.
2595 */
2596 static void
ri_GenerateQualCollation(StringInfo buf,Oid collation)2597 ri_GenerateQualCollation(StringInfo buf, Oid collation)
2598 {
2599 HeapTuple tp;
2600 Form_pg_collation colltup;
2601 char *collname;
2602 char onename[MAX_QUOTED_NAME_LEN];
2603
2604 /* Nothing to do if it's a noncollatable data type */
2605 if (!OidIsValid(collation))
2606 return;
2607
2608 tp = SearchSysCache1(COLLOID, ObjectIdGetDatum(collation));
2609 if (!HeapTupleIsValid(tp))
2610 elog(ERROR, "cache lookup failed for collation %u", collation);
2611 colltup = (Form_pg_collation) GETSTRUCT(tp);
2612 collname = NameStr(colltup->collname);
2613
2614 /*
2615 * We qualify the name always, for simplicity and to ensure the query is
2616 * not search-path-dependent.
2617 */
2618 quoteOneName(onename, get_namespace_name(colltup->collnamespace));
2619 appendStringInfo(buf, " COLLATE %s", onename);
2620 quoteOneName(onename, collname);
2621 appendStringInfo(buf, ".%s", onename);
2622
2623 ReleaseSysCache(tp);
2624 }
2625
2626 /* ----------
2627 * ri_BuildQueryKey -
2628 *
2629 * Construct a hashtable key for a prepared SPI plan of an FK constraint.
2630 *
2631 * key: output argument, *key is filled in based on the other arguments
2632 * riinfo: info from pg_constraint entry
2633 * constr_queryno: an internal number identifying the query type
2634 * (see RI_PLAN_XXX constants at head of file)
2635 * ----------
2636 */
2637 static void
ri_BuildQueryKey(RI_QueryKey * key,const RI_ConstraintInfo * riinfo,int32 constr_queryno)2638 ri_BuildQueryKey(RI_QueryKey *key, const RI_ConstraintInfo *riinfo,
2639 int32 constr_queryno)
2640 {
2641 /*
2642 * We assume struct RI_QueryKey contains no padding bytes, else we'd need
2643 * to use memset to clear them.
2644 */
2645 key->constr_id = riinfo->constraint_id;
2646 key->constr_queryno = constr_queryno;
2647 }
2648
2649 /*
2650 * Check that RI trigger function was called in expected context
2651 */
2652 static void
ri_CheckTrigger(FunctionCallInfo fcinfo,const char * funcname,int tgkind)2653 ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname, int tgkind)
2654 {
2655 TriggerData *trigdata = (TriggerData *) fcinfo->context;
2656
2657 if (!CALLED_AS_TRIGGER(fcinfo))
2658 ereport(ERROR,
2659 (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2660 errmsg("function \"%s\" was not called by trigger manager", funcname)));
2661
2662 /*
2663 * Check proper event
2664 */
2665 if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
2666 !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
2667 ereport(ERROR,
2668 (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2669 errmsg("function \"%s\" must be fired AFTER ROW", funcname)));
2670
2671 switch (tgkind)
2672 {
2673 case RI_TRIGTYPE_INSERT:
2674 if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
2675 ereport(ERROR,
2676 (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2677 errmsg("function \"%s\" must be fired for INSERT", funcname)));
2678 break;
2679 case RI_TRIGTYPE_UPDATE:
2680 if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
2681 ereport(ERROR,
2682 (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2683 errmsg("function \"%s\" must be fired for UPDATE", funcname)));
2684 break;
2685 case RI_TRIGTYPE_DELETE:
2686 if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
2687 ereport(ERROR,
2688 (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2689 errmsg("function \"%s\" must be fired for DELETE", funcname)));
2690 break;
2691 }
2692 }
2693
2694
2695 /*
2696 * Fetch the RI_ConstraintInfo struct for the trigger's FK constraint.
2697 */
2698 static const RI_ConstraintInfo *
ri_FetchConstraintInfo(Trigger * trigger,Relation trig_rel,bool rel_is_pk)2699 ri_FetchConstraintInfo(Trigger *trigger, Relation trig_rel, bool rel_is_pk)
2700 {
2701 Oid constraintOid = trigger->tgconstraint;
2702 const RI_ConstraintInfo *riinfo;
2703
2704 /*
2705 * Check that the FK constraint's OID is available; it might not be if
2706 * we've been invoked via an ordinary trigger or an old-style "constraint
2707 * trigger".
2708 */
2709 if (!OidIsValid(constraintOid))
2710 ereport(ERROR,
2711 (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
2712 errmsg("no pg_constraint entry for trigger \"%s\" on table \"%s\"",
2713 trigger->tgname, RelationGetRelationName(trig_rel)),
2714 errhint("Remove this referential integrity trigger and its mates, then do ALTER TABLE ADD CONSTRAINT.")));
2715
2716 /* Find or create a hashtable entry for the constraint */
2717 riinfo = ri_LoadConstraintInfo(constraintOid);
2718
2719 /* Do some easy cross-checks against the trigger call data */
2720 if (rel_is_pk)
2721 {
2722 if (riinfo->fk_relid != trigger->tgconstrrelid ||
2723 riinfo->pk_relid != RelationGetRelid(trig_rel))
2724 elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",
2725 trigger->tgname, RelationGetRelationName(trig_rel));
2726 }
2727 else
2728 {
2729 if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||
2730 riinfo->pk_relid != trigger->tgconstrrelid)
2731 elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",
2732 trigger->tgname, RelationGetRelationName(trig_rel));
2733 }
2734
2735 return riinfo;
2736 }
2737
2738 /*
2739 * Fetch or create the RI_ConstraintInfo struct for an FK constraint.
2740 */
2741 static const RI_ConstraintInfo *
ri_LoadConstraintInfo(Oid constraintOid)2742 ri_LoadConstraintInfo(Oid constraintOid)
2743 {
2744 RI_ConstraintInfo *riinfo;
2745 bool found;
2746 HeapTuple tup;
2747 Form_pg_constraint conForm;
2748 Datum adatum;
2749 bool isNull;
2750 ArrayType *arr;
2751 int numkeys;
2752
2753 /*
2754 * On the first call initialize the hashtable
2755 */
2756 if (!ri_constraint_cache)
2757 ri_InitHashTables();
2758
2759 /*
2760 * Find or create a hash entry. If we find a valid one, just return it.
2761 */
2762 riinfo = (RI_ConstraintInfo *) hash_search(ri_constraint_cache,
2763 (void *) &constraintOid,
2764 HASH_ENTER, &found);
2765 if (!found)
2766 riinfo->valid = false;
2767 else if (riinfo->valid)
2768 return riinfo;
2769
2770 /*
2771 * Fetch the pg_constraint row so we can fill in the entry.
2772 */
2773 tup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constraintOid));
2774 if (!HeapTupleIsValid(tup)) /* should not happen */
2775 elog(ERROR, "cache lookup failed for constraint %u", constraintOid);
2776 conForm = (Form_pg_constraint) GETSTRUCT(tup);
2777
2778 if (conForm->contype != CONSTRAINT_FOREIGN) /* should not happen */
2779 elog(ERROR, "constraint %u is not a foreign key constraint",
2780 constraintOid);
2781
2782 /* And extract data */
2783 Assert(riinfo->constraint_id == constraintOid);
2784 riinfo->oidHashValue = GetSysCacheHashValue1(CONSTROID,
2785 ObjectIdGetDatum(constraintOid));
2786 memcpy(&riinfo->conname, &conForm->conname, sizeof(NameData));
2787 riinfo->pk_relid = conForm->confrelid;
2788 riinfo->fk_relid = conForm->conrelid;
2789 riinfo->confupdtype = conForm->confupdtype;
2790 riinfo->confdeltype = conForm->confdeltype;
2791 riinfo->confmatchtype = conForm->confmatchtype;
2792
2793 /*
2794 * We expect the arrays to be 1-D arrays of the right types; verify that.
2795 * We don't need to use deconstruct_array() since the array data is just
2796 * going to look like a C array of values.
2797 */
2798 adatum = SysCacheGetAttr(CONSTROID, tup,
2799 Anum_pg_constraint_conkey, &isNull);
2800 if (isNull)
2801 elog(ERROR, "null conkey for constraint %u", constraintOid);
2802 arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
2803 if (ARR_NDIM(arr) != 1 ||
2804 ARR_HASNULL(arr) ||
2805 ARR_ELEMTYPE(arr) != INT2OID)
2806 elog(ERROR, "conkey is not a 1-D smallint array");
2807 numkeys = ARR_DIMS(arr)[0];
2808 if (numkeys <= 0 || numkeys > RI_MAX_NUMKEYS)
2809 elog(ERROR, "foreign key constraint cannot have %d columns", numkeys);
2810 riinfo->nkeys = numkeys;
2811 memcpy(riinfo->fk_attnums, ARR_DATA_PTR(arr), numkeys * sizeof(int16));
2812 if ((Pointer) arr != DatumGetPointer(adatum))
2813 pfree(arr); /* free de-toasted copy, if any */
2814
2815 adatum = SysCacheGetAttr(CONSTROID, tup,
2816 Anum_pg_constraint_confkey, &isNull);
2817 if (isNull)
2818 elog(ERROR, "null confkey for constraint %u", constraintOid);
2819 arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
2820 if (ARR_NDIM(arr) != 1 ||
2821 ARR_DIMS(arr)[0] != numkeys ||
2822 ARR_HASNULL(arr) ||
2823 ARR_ELEMTYPE(arr) != INT2OID)
2824 elog(ERROR, "confkey is not a 1-D smallint array");
2825 memcpy(riinfo->pk_attnums, ARR_DATA_PTR(arr), numkeys * sizeof(int16));
2826 if ((Pointer) arr != DatumGetPointer(adatum))
2827 pfree(arr); /* free de-toasted copy, if any */
2828
2829 adatum = SysCacheGetAttr(CONSTROID, tup,
2830 Anum_pg_constraint_conpfeqop, &isNull);
2831 if (isNull)
2832 elog(ERROR, "null conpfeqop for constraint %u", constraintOid);
2833 arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
2834 /* see TryReuseForeignKey if you change the test below */
2835 if (ARR_NDIM(arr) != 1 ||
2836 ARR_DIMS(arr)[0] != numkeys ||
2837 ARR_HASNULL(arr) ||
2838 ARR_ELEMTYPE(arr) != OIDOID)
2839 elog(ERROR, "conpfeqop is not a 1-D Oid array");
2840 memcpy(riinfo->pf_eq_oprs, ARR_DATA_PTR(arr), numkeys * sizeof(Oid));
2841 if ((Pointer) arr != DatumGetPointer(adatum))
2842 pfree(arr); /* free de-toasted copy, if any */
2843
2844 adatum = SysCacheGetAttr(CONSTROID, tup,
2845 Anum_pg_constraint_conppeqop, &isNull);
2846 if (isNull)
2847 elog(ERROR, "null conppeqop for constraint %u", constraintOid);
2848 arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
2849 if (ARR_NDIM(arr) != 1 ||
2850 ARR_DIMS(arr)[0] != numkeys ||
2851 ARR_HASNULL(arr) ||
2852 ARR_ELEMTYPE(arr) != OIDOID)
2853 elog(ERROR, "conppeqop is not a 1-D Oid array");
2854 memcpy(riinfo->pp_eq_oprs, ARR_DATA_PTR(arr), numkeys * sizeof(Oid));
2855 if ((Pointer) arr != DatumGetPointer(adatum))
2856 pfree(arr); /* free de-toasted copy, if any */
2857
2858 adatum = SysCacheGetAttr(CONSTROID, tup,
2859 Anum_pg_constraint_conffeqop, &isNull);
2860 if (isNull)
2861 elog(ERROR, "null conffeqop for constraint %u", constraintOid);
2862 arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
2863 if (ARR_NDIM(arr) != 1 ||
2864 ARR_DIMS(arr)[0] != numkeys ||
2865 ARR_HASNULL(arr) ||
2866 ARR_ELEMTYPE(arr) != OIDOID)
2867 elog(ERROR, "conffeqop is not a 1-D Oid array");
2868 memcpy(riinfo->ff_eq_oprs, ARR_DATA_PTR(arr), numkeys * sizeof(Oid));
2869 if ((Pointer) arr != DatumGetPointer(adatum))
2870 pfree(arr); /* free de-toasted copy, if any */
2871
2872 ReleaseSysCache(tup);
2873
2874 /*
2875 * For efficient processing of invalidation messages below, we keep a
2876 * doubly-linked list, and a count, of all currently valid entries.
2877 */
2878 dlist_push_tail(&ri_constraint_cache_valid_list, &riinfo->valid_link);
2879 ri_constraint_cache_valid_count++;
2880
2881 riinfo->valid = true;
2882
2883 return riinfo;
2884 }
2885
2886 /*
2887 * Callback for pg_constraint inval events
2888 *
2889 * While most syscache callbacks just flush all their entries, pg_constraint
2890 * gets enough update traffic that it's probably worth being smarter.
2891 * Invalidate any ri_constraint_cache entry associated with the syscache
2892 * entry with the specified hash value, or all entries if hashvalue == 0.
2893 *
2894 * Note: at the time a cache invalidation message is processed there may be
2895 * active references to the cache. Because of this we never remove entries
2896 * from the cache, but only mark them invalid, which is harmless to active
2897 * uses. (Any query using an entry should hold a lock sufficient to keep that
2898 * data from changing under it --- but we may get cache flushes anyway.)
2899 */
2900 static void
InvalidateConstraintCacheCallBack(Datum arg,int cacheid,uint32 hashvalue)2901 InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue)
2902 {
2903 dlist_mutable_iter iter;
2904
2905 Assert(ri_constraint_cache != NULL);
2906
2907 /*
2908 * If the list of currently valid entries gets excessively large, we mark
2909 * them all invalid so we can empty the list. This arrangement avoids
2910 * O(N^2) behavior in situations where a session touches many foreign keys
2911 * and also does many ALTER TABLEs, such as a restore from pg_dump.
2912 */
2913 if (ri_constraint_cache_valid_count > 1000)
2914 hashvalue = 0; /* pretend it's a cache reset */
2915
2916 dlist_foreach_modify(iter, &ri_constraint_cache_valid_list)
2917 {
2918 RI_ConstraintInfo *riinfo = dlist_container(RI_ConstraintInfo,
2919 valid_link, iter.cur);
2920
2921 if (hashvalue == 0 || riinfo->oidHashValue == hashvalue)
2922 {
2923 riinfo->valid = false;
2924 /* Remove invalidated entries from the list, too */
2925 dlist_delete(iter.cur);
2926 ri_constraint_cache_valid_count--;
2927 }
2928 }
2929 }
2930
2931
2932 /*
2933 * Prepare execution plan for a query to enforce an RI restriction
2934 *
2935 * If cache_plan is true, the plan is saved into our plan hashtable
2936 * so that we don't need to plan it again.
2937 */
2938 static SPIPlanPtr
ri_PlanCheck(const char * querystr,int nargs,Oid * argtypes,RI_QueryKey * qkey,Relation fk_rel,Relation pk_rel,bool cache_plan)2939 ri_PlanCheck(const char *querystr, int nargs, Oid *argtypes,
2940 RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel,
2941 bool cache_plan)
2942 {
2943 SPIPlanPtr qplan;
2944 Relation query_rel;
2945 Oid save_userid;
2946 int save_sec_context;
2947
2948 /*
2949 * Use the query type code to determine whether the query is run against
2950 * the PK or FK table; we'll do the check as that table's owner
2951 */
2952 if (qkey->constr_queryno <= RI_PLAN_LAST_ON_PK)
2953 query_rel = pk_rel;
2954 else
2955 query_rel = fk_rel;
2956
2957 /* Switch to proper UID to perform check as */
2958 GetUserIdAndSecContext(&save_userid, &save_sec_context);
2959 SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner,
2960 save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
2961 SECURITY_NOFORCE_RLS);
2962
2963 /* Create the plan */
2964 qplan = SPI_prepare(querystr, nargs, argtypes);
2965
2966 if (qplan == NULL)
2967 elog(ERROR, "SPI_prepare returned %d for %s", SPI_result, querystr);
2968
2969 /* Restore UID and security context */
2970 SetUserIdAndSecContext(save_userid, save_sec_context);
2971
2972 /* Save the plan if requested */
2973 if (cache_plan)
2974 {
2975 SPI_keepplan(qplan);
2976 ri_HashPreparedPlan(qkey, qplan);
2977 }
2978
2979 return qplan;
2980 }
2981
2982 /*
2983 * Perform a query to enforce an RI restriction
2984 */
2985 static bool
ri_PerformCheck(const RI_ConstraintInfo * riinfo,RI_QueryKey * qkey,SPIPlanPtr qplan,Relation fk_rel,Relation pk_rel,HeapTuple old_tuple,HeapTuple new_tuple,bool detectNewRows,int expect_OK)2986 ri_PerformCheck(const RI_ConstraintInfo *riinfo,
2987 RI_QueryKey *qkey, SPIPlanPtr qplan,
2988 Relation fk_rel, Relation pk_rel,
2989 HeapTuple old_tuple, HeapTuple new_tuple,
2990 bool detectNewRows, int expect_OK)
2991 {
2992 Relation query_rel,
2993 source_rel;
2994 bool source_is_pk;
2995 Snapshot test_snapshot;
2996 Snapshot crosscheck_snapshot;
2997 int limit;
2998 int spi_result;
2999 Oid save_userid;
3000 int save_sec_context;
3001 Datum vals[RI_MAX_NUMKEYS * 2];
3002 char nulls[RI_MAX_NUMKEYS * 2];
3003
3004 /*
3005 * Use the query type code to determine whether the query is run against
3006 * the PK or FK table; we'll do the check as that table's owner
3007 */
3008 if (qkey->constr_queryno <= RI_PLAN_LAST_ON_PK)
3009 query_rel = pk_rel;
3010 else
3011 query_rel = fk_rel;
3012
3013 /*
3014 * The values for the query are taken from the table on which the trigger
3015 * is called - it is normally the other one with respect to query_rel. An
3016 * exception is ri_Check_Pk_Match(), which uses the PK table for both (and
3017 * sets queryno to RI_PLAN_CHECK_LOOKUPPK_FROM_PK). We might eventually
3018 * need some less klugy way to determine this.
3019 */
3020 if (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK)
3021 {
3022 source_rel = fk_rel;
3023 source_is_pk = false;
3024 }
3025 else
3026 {
3027 source_rel = pk_rel;
3028 source_is_pk = true;
3029 }
3030
3031 /* Extract the parameters to be passed into the query */
3032 if (new_tuple)
3033 {
3034 ri_ExtractValues(source_rel, new_tuple, riinfo, source_is_pk,
3035 vals, nulls);
3036 if (old_tuple)
3037 ri_ExtractValues(source_rel, old_tuple, riinfo, source_is_pk,
3038 vals + riinfo->nkeys, nulls + riinfo->nkeys);
3039 }
3040 else
3041 {
3042 ri_ExtractValues(source_rel, old_tuple, riinfo, source_is_pk,
3043 vals, nulls);
3044 }
3045
3046 /*
3047 * In READ COMMITTED mode, we just need to use an up-to-date regular
3048 * snapshot, and we will see all rows that could be interesting. But in
3049 * transaction-snapshot mode, we can't change the transaction snapshot. If
3050 * the caller passes detectNewRows == false then it's okay to do the query
3051 * with the transaction snapshot; otherwise we use a current snapshot, and
3052 * tell the executor to error out if it finds any rows under the current
3053 * snapshot that wouldn't be visible per the transaction snapshot. Note
3054 * that SPI_execute_snapshot will register the snapshots, so we don't need
3055 * to bother here.
3056 */
3057 if (IsolationUsesXactSnapshot() && detectNewRows)
3058 {
3059 CommandCounterIncrement(); /* be sure all my own work is visible */
3060 test_snapshot = GetLatestSnapshot();
3061 crosscheck_snapshot = GetTransactionSnapshot();
3062 }
3063 else
3064 {
3065 /* the default SPI behavior is okay */
3066 test_snapshot = InvalidSnapshot;
3067 crosscheck_snapshot = InvalidSnapshot;
3068 }
3069
3070 /*
3071 * If this is a select query (e.g., for a 'no action' or 'restrict'
3072 * trigger), we only need to see if there is a single row in the table,
3073 * matching the key. Otherwise, limit = 0 - because we want the query to
3074 * affect ALL the matching rows.
3075 */
3076 limit = (expect_OK == SPI_OK_SELECT) ? 1 : 0;
3077
3078 /* Switch to proper UID to perform check as */
3079 GetUserIdAndSecContext(&save_userid, &save_sec_context);
3080 SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner,
3081 save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
3082 SECURITY_NOFORCE_RLS);
3083
3084 /* Finally we can run the query. */
3085 spi_result = SPI_execute_snapshot(qplan,
3086 vals, nulls,
3087 test_snapshot, crosscheck_snapshot,
3088 false, false, limit);
3089
3090 /* Restore UID and security context */
3091 SetUserIdAndSecContext(save_userid, save_sec_context);
3092
3093 /* Check result */
3094 if (spi_result < 0)
3095 elog(ERROR, "SPI_execute_snapshot returned %d", spi_result);
3096
3097 if (expect_OK >= 0 && spi_result != expect_OK)
3098 ri_ReportViolation(riinfo,
3099 pk_rel, fk_rel,
3100 new_tuple ? new_tuple : old_tuple,
3101 NULL,
3102 qkey->constr_queryno, true);
3103
3104 /* XXX wouldn't it be clearer to do this part at the caller? */
3105 if (qkey->constr_queryno != RI_PLAN_CHECK_LOOKUPPK_FROM_PK &&
3106 expect_OK == SPI_OK_SELECT &&
3107 (SPI_processed == 0) == (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK))
3108 ri_ReportViolation(riinfo,
3109 pk_rel, fk_rel,
3110 new_tuple ? new_tuple : old_tuple,
3111 NULL,
3112 qkey->constr_queryno, false);
3113
3114 return SPI_processed != 0;
3115 }
3116
3117 /*
3118 * Extract fields from a tuple into Datum/nulls arrays
3119 */
3120 static void
ri_ExtractValues(Relation rel,HeapTuple tup,const RI_ConstraintInfo * riinfo,bool rel_is_pk,Datum * vals,char * nulls)3121 ri_ExtractValues(Relation rel, HeapTuple tup,
3122 const RI_ConstraintInfo *riinfo, bool rel_is_pk,
3123 Datum *vals, char *nulls)
3124 {
3125 TupleDesc tupdesc = rel->rd_att;
3126 const int16 *attnums;
3127 int i;
3128 bool isnull;
3129
3130 if (rel_is_pk)
3131 attnums = riinfo->pk_attnums;
3132 else
3133 attnums = riinfo->fk_attnums;
3134
3135 for (i = 0; i < riinfo->nkeys; i++)
3136 {
3137 vals[i] = heap_getattr(tup, attnums[i], tupdesc,
3138 &isnull);
3139 nulls[i] = isnull ? 'n' : ' ';
3140 }
3141 }
3142
3143 /*
3144 * Produce an error report
3145 *
3146 * If the failed constraint was on insert/update to the FK table,
3147 * we want the key names and values extracted from there, and the error
3148 * message to look like 'key blah is not present in PK'.
3149 * Otherwise, the attr names and values come from the PK table and the
3150 * message looks like 'key blah is still referenced from FK'.
3151 */
3152 static void
ri_ReportViolation(const RI_ConstraintInfo * riinfo,Relation pk_rel,Relation fk_rel,HeapTuple violator,TupleDesc tupdesc,int queryno,bool spi_err)3153 ri_ReportViolation(const RI_ConstraintInfo *riinfo,
3154 Relation pk_rel, Relation fk_rel,
3155 HeapTuple violator, TupleDesc tupdesc,
3156 int queryno, bool spi_err)
3157 {
3158 StringInfoData key_names;
3159 StringInfoData key_values;
3160 bool onfk;
3161 const int16 *attnums;
3162 int idx;
3163 Oid rel_oid;
3164 AclResult aclresult;
3165 bool has_perm = true;
3166
3167 if (spi_err)
3168 ereport(ERROR,
3169 (errcode(ERRCODE_INTERNAL_ERROR),
3170 errmsg("referential integrity query on \"%s\" from constraint \"%s\" on \"%s\" gave unexpected result",
3171 RelationGetRelationName(pk_rel),
3172 NameStr(riinfo->conname),
3173 RelationGetRelationName(fk_rel)),
3174 errhint("This is most likely due to a rule having rewritten the query.")));
3175
3176 /*
3177 * Determine which relation to complain about. If tupdesc wasn't passed
3178 * by caller, assume the violator tuple came from there.
3179 */
3180 onfk = (queryno == RI_PLAN_CHECK_LOOKUPPK);
3181 if (onfk)
3182 {
3183 attnums = riinfo->fk_attnums;
3184 rel_oid = fk_rel->rd_id;
3185 if (tupdesc == NULL)
3186 tupdesc = fk_rel->rd_att;
3187 }
3188 else
3189 {
3190 attnums = riinfo->pk_attnums;
3191 rel_oid = pk_rel->rd_id;
3192 if (tupdesc == NULL)
3193 tupdesc = pk_rel->rd_att;
3194 }
3195
3196 /*
3197 * Check permissions- if the user does not have access to view the data in
3198 * any of the key columns then we don't include the errdetail() below.
3199 *
3200 * Check if RLS is enabled on the relation first. If so, we don't return
3201 * any specifics to avoid leaking data.
3202 *
3203 * Check table-level permissions next and, failing that, column-level
3204 * privileges.
3205 */
3206
3207 if (check_enable_rls(rel_oid, InvalidOid, true) != RLS_ENABLED)
3208 {
3209 aclresult = pg_class_aclcheck(rel_oid, GetUserId(), ACL_SELECT);
3210 if (aclresult != ACLCHECK_OK)
3211 {
3212 /* Try for column-level permissions */
3213 for (idx = 0; idx < riinfo->nkeys; idx++)
3214 {
3215 aclresult = pg_attribute_aclcheck(rel_oid, attnums[idx],
3216 GetUserId(),
3217 ACL_SELECT);
3218
3219 /* No access to the key */
3220 if (aclresult != ACLCHECK_OK)
3221 {
3222 has_perm = false;
3223 break;
3224 }
3225 }
3226 }
3227 }
3228 else
3229 has_perm = false;
3230
3231 if (has_perm)
3232 {
3233 /* Get printable versions of the keys involved */
3234 initStringInfo(&key_names);
3235 initStringInfo(&key_values);
3236 for (idx = 0; idx < riinfo->nkeys; idx++)
3237 {
3238 int fnum = attnums[idx];
3239 char *name,
3240 *val;
3241
3242 name = SPI_fname(tupdesc, fnum);
3243 val = SPI_getvalue(violator, tupdesc, fnum);
3244 if (!val)
3245 val = "null";
3246
3247 if (idx > 0)
3248 {
3249 appendStringInfoString(&key_names, ", ");
3250 appendStringInfoString(&key_values, ", ");
3251 }
3252 appendStringInfoString(&key_names, name);
3253 appendStringInfoString(&key_values, val);
3254 }
3255 }
3256
3257 if (onfk)
3258 ereport(ERROR,
3259 (errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
3260 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
3261 RelationGetRelationName(fk_rel),
3262 NameStr(riinfo->conname)),
3263 has_perm ?
3264 errdetail("Key (%s)=(%s) is not present in table \"%s\".",
3265 key_names.data, key_values.data,
3266 RelationGetRelationName(pk_rel)) :
3267 errdetail("Key is not present in table \"%s\".",
3268 RelationGetRelationName(pk_rel)),
3269 errtableconstraint(fk_rel, NameStr(riinfo->conname))));
3270 else
3271 ereport(ERROR,
3272 (errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
3273 errmsg("update or delete on table \"%s\" violates foreign key constraint \"%s\" on table \"%s\"",
3274 RelationGetRelationName(pk_rel),
3275 NameStr(riinfo->conname),
3276 RelationGetRelationName(fk_rel)),
3277 has_perm ?
3278 errdetail("Key (%s)=(%s) is still referenced from table \"%s\".",
3279 key_names.data, key_values.data,
3280 RelationGetRelationName(fk_rel)) :
3281 errdetail("Key is still referenced from table \"%s\".",
3282 RelationGetRelationName(fk_rel)),
3283 errtableconstraint(fk_rel, NameStr(riinfo->conname))));
3284 }
3285
3286
3287 /* ----------
3288 * ri_NullCheck -
3289 *
3290 * Determine the NULL state of all key values in a tuple
3291 *
3292 * Returns one of RI_KEYS_ALL_NULL, RI_KEYS_NONE_NULL or RI_KEYS_SOME_NULL.
3293 * ----------
3294 */
3295 static int
ri_NullCheck(HeapTuple tup,const RI_ConstraintInfo * riinfo,bool rel_is_pk)3296 ri_NullCheck(HeapTuple tup,
3297 const RI_ConstraintInfo *riinfo, bool rel_is_pk)
3298 {
3299 const int16 *attnums;
3300 int i;
3301 bool allnull = true;
3302 bool nonenull = true;
3303
3304 if (rel_is_pk)
3305 attnums = riinfo->pk_attnums;
3306 else
3307 attnums = riinfo->fk_attnums;
3308
3309 for (i = 0; i < riinfo->nkeys; i++)
3310 {
3311 if (heap_attisnull(tup, attnums[i]))
3312 nonenull = false;
3313 else
3314 allnull = false;
3315 }
3316
3317 if (allnull)
3318 return RI_KEYS_ALL_NULL;
3319
3320 if (nonenull)
3321 return RI_KEYS_NONE_NULL;
3322
3323 return RI_KEYS_SOME_NULL;
3324 }
3325
3326
3327 /* ----------
3328 * ri_InitHashTables -
3329 *
3330 * Initialize our internal hash tables.
3331 * ----------
3332 */
3333 static void
ri_InitHashTables(void)3334 ri_InitHashTables(void)
3335 {
3336 HASHCTL ctl;
3337
3338 memset(&ctl, 0, sizeof(ctl));
3339 ctl.keysize = sizeof(Oid);
3340 ctl.entrysize = sizeof(RI_ConstraintInfo);
3341 ri_constraint_cache = hash_create("RI constraint cache",
3342 RI_INIT_CONSTRAINTHASHSIZE,
3343 &ctl, HASH_ELEM | HASH_BLOBS);
3344
3345 /* Arrange to flush cache on pg_constraint changes */
3346 CacheRegisterSyscacheCallback(CONSTROID,
3347 InvalidateConstraintCacheCallBack,
3348 (Datum) 0);
3349
3350 memset(&ctl, 0, sizeof(ctl));
3351 ctl.keysize = sizeof(RI_QueryKey);
3352 ctl.entrysize = sizeof(RI_QueryHashEntry);
3353 ri_query_cache = hash_create("RI query cache",
3354 RI_INIT_QUERYHASHSIZE,
3355 &ctl, HASH_ELEM | HASH_BLOBS);
3356
3357 memset(&ctl, 0, sizeof(ctl));
3358 ctl.keysize = sizeof(RI_CompareKey);
3359 ctl.entrysize = sizeof(RI_CompareHashEntry);
3360 ri_compare_cache = hash_create("RI compare cache",
3361 RI_INIT_QUERYHASHSIZE,
3362 &ctl, HASH_ELEM | HASH_BLOBS);
3363 }
3364
3365
3366 /* ----------
3367 * ri_FetchPreparedPlan -
3368 *
3369 * Lookup for a query key in our private hash table of prepared
3370 * and saved SPI execution plans. Return the plan if found or NULL.
3371 * ----------
3372 */
3373 static SPIPlanPtr
ri_FetchPreparedPlan(RI_QueryKey * key)3374 ri_FetchPreparedPlan(RI_QueryKey *key)
3375 {
3376 RI_QueryHashEntry *entry;
3377 SPIPlanPtr plan;
3378
3379 /*
3380 * On the first call initialize the hashtable
3381 */
3382 if (!ri_query_cache)
3383 ri_InitHashTables();
3384
3385 /*
3386 * Lookup for the key
3387 */
3388 entry = (RI_QueryHashEntry *) hash_search(ri_query_cache,
3389 (void *) key,
3390 HASH_FIND, NULL);
3391 if (entry == NULL)
3392 return NULL;
3393
3394 /*
3395 * Check whether the plan is still valid. If it isn't, we don't want to
3396 * simply rely on plancache.c to regenerate it; rather we should start
3397 * from scratch and rebuild the query text too. This is to cover cases
3398 * such as table/column renames. We depend on the plancache machinery to
3399 * detect possible invalidations, though.
3400 *
3401 * CAUTION: this check is only trustworthy if the caller has already
3402 * locked both FK and PK rels.
3403 */
3404 plan = entry->plan;
3405 if (plan && SPI_plan_is_valid(plan))
3406 return plan;
3407
3408 /*
3409 * Otherwise we might as well flush the cached plan now, to free a little
3410 * memory space before we make a new one.
3411 */
3412 entry->plan = NULL;
3413 if (plan)
3414 SPI_freeplan(plan);
3415
3416 return NULL;
3417 }
3418
3419
3420 /* ----------
3421 * ri_HashPreparedPlan -
3422 *
3423 * Add another plan to our private SPI query plan hashtable.
3424 * ----------
3425 */
3426 static void
ri_HashPreparedPlan(RI_QueryKey * key,SPIPlanPtr plan)3427 ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan)
3428 {
3429 RI_QueryHashEntry *entry;
3430 bool found;
3431
3432 /*
3433 * On the first call initialize the hashtable
3434 */
3435 if (!ri_query_cache)
3436 ri_InitHashTables();
3437
3438 /*
3439 * Add the new plan. We might be overwriting an entry previously found
3440 * invalid by ri_FetchPreparedPlan.
3441 */
3442 entry = (RI_QueryHashEntry *) hash_search(ri_query_cache,
3443 (void *) key,
3444 HASH_ENTER, &found);
3445 Assert(!found || entry->plan == NULL);
3446 entry->plan = plan;
3447 }
3448
3449
3450 /* ----------
3451 * ri_KeysEqual -
3452 *
3453 * Check if all key values in OLD and NEW are equal.
3454 *
3455 * Note: at some point we might wish to redefine this as checking for
3456 * "IS NOT DISTINCT" rather than "=", that is, allow two nulls to be
3457 * considered equal. Currently there is no need since all callers have
3458 * previously found at least one of the rows to contain no nulls.
3459 * ----------
3460 */
3461 static bool
ri_KeysEqual(Relation rel,HeapTuple oldtup,HeapTuple newtup,const RI_ConstraintInfo * riinfo,bool rel_is_pk)3462 ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
3463 const RI_ConstraintInfo *riinfo, bool rel_is_pk)
3464 {
3465 TupleDesc tupdesc = RelationGetDescr(rel);
3466 const int16 *attnums;
3467 const Oid *eq_oprs;
3468 int i;
3469
3470 if (rel_is_pk)
3471 {
3472 attnums = riinfo->pk_attnums;
3473 eq_oprs = riinfo->pp_eq_oprs;
3474 }
3475 else
3476 {
3477 attnums = riinfo->fk_attnums;
3478 eq_oprs = riinfo->ff_eq_oprs;
3479 }
3480
3481 for (i = 0; i < riinfo->nkeys; i++)
3482 {
3483 Datum oldvalue;
3484 Datum newvalue;
3485 bool isnull;
3486
3487 /*
3488 * Get one attribute's oldvalue. If it is NULL - they're not equal.
3489 */
3490 oldvalue = heap_getattr(oldtup, attnums[i], tupdesc, &isnull);
3491 if (isnull)
3492 return false;
3493
3494 /*
3495 * Get one attribute's newvalue. If it is NULL - they're not equal.
3496 */
3497 newvalue = heap_getattr(newtup, attnums[i], tupdesc, &isnull);
3498 if (isnull)
3499 return false;
3500
3501 /*
3502 * Compare them with the appropriate equality operator.
3503 */
3504 if (!ri_AttributesEqual(eq_oprs[i], RIAttType(rel, attnums[i]),
3505 oldvalue, newvalue))
3506 return false;
3507 }
3508
3509 return true;
3510 }
3511
3512
3513 /* ----------
3514 * ri_AttributesEqual -
3515 *
3516 * Call the appropriate equality comparison operator for two values.
3517 *
3518 * NB: we have already checked that neither value is null.
3519 * ----------
3520 */
3521 static bool
ri_AttributesEqual(Oid eq_opr,Oid typeid,Datum oldvalue,Datum newvalue)3522 ri_AttributesEqual(Oid eq_opr, Oid typeid,
3523 Datum oldvalue, Datum newvalue)
3524 {
3525 RI_CompareHashEntry *entry = ri_HashCompareOp(eq_opr, typeid);
3526
3527 /* Do we need to cast the values? */
3528 if (OidIsValid(entry->cast_func_finfo.fn_oid))
3529 {
3530 oldvalue = FunctionCall3(&entry->cast_func_finfo,
3531 oldvalue,
3532 Int32GetDatum(-1), /* typmod */
3533 BoolGetDatum(false)); /* implicit coercion */
3534 newvalue = FunctionCall3(&entry->cast_func_finfo,
3535 newvalue,
3536 Int32GetDatum(-1), /* typmod */
3537 BoolGetDatum(false)); /* implicit coercion */
3538 }
3539
3540 /*
3541 * Apply the comparison operator. We assume it doesn't care about
3542 * collations.
3543 */
3544 return DatumGetBool(FunctionCall2(&entry->eq_opr_finfo,
3545 oldvalue, newvalue));
3546 }
3547
3548 /* ----------
3549 * ri_HashCompareOp -
3550 *
3551 * See if we know how to compare two values, and create a new hash entry
3552 * if not.
3553 * ----------
3554 */
3555 static RI_CompareHashEntry *
ri_HashCompareOp(Oid eq_opr,Oid typeid)3556 ri_HashCompareOp(Oid eq_opr, Oid typeid)
3557 {
3558 RI_CompareKey key;
3559 RI_CompareHashEntry *entry;
3560 bool found;
3561
3562 /*
3563 * On the first call initialize the hashtable
3564 */
3565 if (!ri_compare_cache)
3566 ri_InitHashTables();
3567
3568 /*
3569 * Find or create a hash entry. Note we're assuming RI_CompareKey
3570 * contains no struct padding.
3571 */
3572 key.eq_opr = eq_opr;
3573 key.typeid = typeid;
3574 entry = (RI_CompareHashEntry *) hash_search(ri_compare_cache,
3575 (void *) &key,
3576 HASH_ENTER, &found);
3577 if (!found)
3578 entry->valid = false;
3579
3580 /*
3581 * If not already initialized, do so. Since we'll keep this hash entry
3582 * for the life of the backend, put any subsidiary info for the function
3583 * cache structs into TopMemoryContext.
3584 */
3585 if (!entry->valid)
3586 {
3587 Oid lefttype,
3588 righttype,
3589 castfunc;
3590 CoercionPathType pathtype;
3591
3592 /* We always need to know how to call the equality operator */
3593 fmgr_info_cxt(get_opcode(eq_opr), &entry->eq_opr_finfo,
3594 TopMemoryContext);
3595
3596 /*
3597 * If we chose to use a cast from FK to PK type, we may have to apply
3598 * the cast function to get to the operator's input type.
3599 *
3600 * XXX eventually it would be good to support array-coercion cases
3601 * here and in ri_AttributesEqual(). At the moment there is no point
3602 * because cases involving nonidentical array types will be rejected
3603 * at constraint creation time.
3604 *
3605 * XXX perhaps also consider supporting CoerceViaIO? No need at the
3606 * moment since that will never be generated for implicit coercions.
3607 */
3608 op_input_types(eq_opr, &lefttype, &righttype);
3609 Assert(lefttype == righttype);
3610 if (typeid == lefttype)
3611 castfunc = InvalidOid; /* simplest case */
3612 else
3613 {
3614 pathtype = find_coercion_pathway(lefttype, typeid,
3615 COERCION_IMPLICIT,
3616 &castfunc);
3617 if (pathtype != COERCION_PATH_FUNC &&
3618 pathtype != COERCION_PATH_RELABELTYPE)
3619 {
3620 /*
3621 * The declared input type of the eq_opr might be a
3622 * polymorphic type such as ANYARRAY or ANYENUM, or other
3623 * special cases such as RECORD; find_coercion_pathway
3624 * currently doesn't subsume these special cases.
3625 */
3626 if (!IsBinaryCoercible(typeid, lefttype))
3627 elog(ERROR, "no conversion function from %s to %s",
3628 format_type_be(typeid),
3629 format_type_be(lefttype));
3630 }
3631 }
3632 if (OidIsValid(castfunc))
3633 fmgr_info_cxt(castfunc, &entry->cast_func_finfo,
3634 TopMemoryContext);
3635 else
3636 entry->cast_func_finfo.fn_oid = InvalidOid;
3637 entry->valid = true;
3638 }
3639
3640 return entry;
3641 }
3642
3643
3644 /*
3645 * Given a trigger function OID, determine whether it is an RI trigger,
3646 * and if so whether it is attached to PK or FK relation.
3647 */
3648 int
RI_FKey_trigger_type(Oid tgfoid)3649 RI_FKey_trigger_type(Oid tgfoid)
3650 {
3651 switch (tgfoid)
3652 {
3653 case F_RI_FKEY_CASCADE_DEL:
3654 case F_RI_FKEY_CASCADE_UPD:
3655 case F_RI_FKEY_RESTRICT_DEL:
3656 case F_RI_FKEY_RESTRICT_UPD:
3657 case F_RI_FKEY_SETNULL_DEL:
3658 case F_RI_FKEY_SETNULL_UPD:
3659 case F_RI_FKEY_SETDEFAULT_DEL:
3660 case F_RI_FKEY_SETDEFAULT_UPD:
3661 case F_RI_FKEY_NOACTION_DEL:
3662 case F_RI_FKEY_NOACTION_UPD:
3663 return RI_TRIGGER_PK;
3664
3665 case F_RI_FKEY_CHECK_INS:
3666 case F_RI_FKEY_CHECK_UPD:
3667 return RI_TRIGGER_FK;
3668 }
3669
3670 return RI_TRIGGER_NONE;
3671 }
3672