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