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