/*------------------------------------------------------------------------- * * ri_triggers . c * * Generic trigger procedures for referential integrity constraint * checks . * * Note about memory management : the private hashtables kept here live * across query and transaction boundaries , in fact they live as long as * the backend does . This works because the hashtable structures * themselves are allocated by dynahash . c in its permanent DynaHashCxt , * and the SPI plans they point to are saved using SPI_keepplan ( ) . * There is not currently any provision for throwing away a no - longer - needed * plan - - - consider improving this someday . * * * Portions Copyright ( c ) 1996 - 2025 , PostgreSQL Global Development Group * * src / backend / utils / adt / ri_triggers . c * * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #include "postgres.h" #include "access/htup_details.h" #include "access/sysattr.h" #include "access/table.h" #include "access/tableam.h" #include "access/xact.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint.h" #include "catalog/pg_proc.h" #include "commands/trigger.h" #include "executor/executor.h" #include "executor/spi.h" #include "lib/ilist.h" #include "miscadmin.h" #include "parser/parse_coerce.h" #include "parser/parse_relation.h" #include "utils/acl.h" #include "utils/builtins.h" #include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/guc.h" #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/rls.h" #include "utils/ruleutils.h" #include "utils/snapmgr.h" #include "utils/syscache.h" /* * Local definitions #define RI_MAX_NUMKEYS INDEX_MAX_KEYS#define RI_INIT_CONSTRAINTHASHSIZE 64 #define RI_INIT_QUERYHASHSIZE (RI_INIT_CONSTRAINTHASHSIZE * 4 )#define RI_KEYS_ALL_NULL 0 #define RI_KEYS_SOME_NULL 1 #define RI_KEYS_NONE_NULL 2 /* RI query type codes */ /* these queries are executed against the PK (referenced) table: */ #define RI_PLAN_CHECK_LOOKUPPK 1 #define RI_PLAN_CHECK_LOOKUPPK_FROM_PK 2 #define RI_PLAN_LAST_ON_PK RI_PLAN_CHECK_LOOKUPPK_FROM_PK/* these queries are executed against the FK (referencing) table: */ #define RI_PLAN_CASCADE_ONDELETE 3 #define RI_PLAN_CASCADE_ONUPDATE 4 #define RI_PLAN_NO_ACTION 5 /* For RESTRICT, the same plan can be used for both ON DELETE and ON UPDATE triggers. */ #define RI_PLAN_RESTRICT 6 #define RI_PLAN_SETNULL_ONDELETE 7 #define RI_PLAN_SETNULL_ONUPDATE 8 #define RI_PLAN_SETDEFAULT_ONDELETE 9 #define RI_PLAN_SETDEFAULT_ONUPDATE 10 #define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2 +3 )#define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2 )#define RIAttName(rel, attnum) NameStr(*attnumAttName(rel, attnum))#define RIAttType(rel, attnum) attnumTypeId(rel, attnum)#define RIAttCollation(rel, attnum) attnumCollationId(rel, attnum)#define RI_TRIGTYPE_INSERT 1 #define RI_TRIGTYPE_UPDATE 2 #define RI_TRIGTYPE_DELETE 3 /* * RI_ConstraintInfo * * Information extracted from an FK pg_constraint entry . This is cached in * ri_constraint_cache . * * Note that pf / pp / ff_eq_oprs may hold the overlaps operator instead of equals * for the PERIOD part of a temporal foreign key . typedef struct RI_ConstraintInfo/* OID of pg_constraint entry (hash key) */ bool valid; /* successfully initialized? */ /* OID of topmost ancestor constraint; /* hash value of constraint_id */ /* hash value of constraint_root_id */ /* name of the FK constraint */ /* referenced relation */ /* referencing relation */ char confupdtype; /* foreign key's ON UPDATE action */ char confdeltype; /* foreign key's ON DELETE action */ int ndelsetcols; /* number of columns referenced in ON DELETE /* attnums of cols to set on char confmatchtype; /* foreign key's match type */ bool hasperiod; /* if the foreign key uses PERIOD */ int nkeys; /* number of key columns */ /* attnums of referenced cols */ /* attnums of referencing cols */ /* equality operators (PK = FK) */ /* equality operators (PK = PK) */ /* equality operators (FK = FK) */ /* anyrange <@ anyrange */ /* fkattr <@ range_agg(pkattr) */ /* anyrange * anyrange */ /* Link in list of valid entries */ /* * RI_QueryKey * * The key identifying a prepared SPI plan in our query hashtable typedef struct RI_QueryKey/* OID of pg_constraint entry */ /* query type ID, see RI_PLAN_XXX above */ /* * RI_QueryHashEntry typedef struct RI_QueryHashEntry/* * RI_CompareKey * * The key identifying an entry showing how to compare two values typedef struct RI_CompareKey/* the equality operator to apply */ typeid ; /* the data type to apply it to */ /* * RI_CompareHashEntry typedef struct RI_CompareHashEntrybool valid; /* successfully initialized? */ /* call info for equality fn */ /* in case we must coerce input */ /* * Local data static HTAB *ri_constraint_cache = NULL;static HTAB *ri_query_cache = NULL;static HTAB *ri_compare_cache = NULL;static dclist_head ri_constraint_cache_valid_list;/* * Local function prototypes static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,const RI_ConstraintInfo *riinfo);static Datum ri_restrict(TriggerData *trigdata, bool is_no_action);static Datum ri_set(TriggerData *trigdata, bool is_set_null, int tgkind);static void quoteOneName(char *buffer, const char *name);static void quoteRelationName(char *buffer, Relation rel);static void ri_GenerateQual(StringInfo buf,const char *sep,const char *leftop, Oid leftoptype,const char *rightop, Oid rightoptype);static void ri_GenerateQualCollation(StringInfo buf, Oid collation);static int ri_NullCheck(TupleDesc tupDesc, TupleTableSlot *slot,const RI_ConstraintInfo *riinfo, bool rel_is_pk);static void ri_BuildQueryKey(RI_QueryKey *key,const RI_ConstraintInfo *riinfo,static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,const RI_ConstraintInfo *riinfo, bool rel_is_pk);static bool ri_CompareWithCast(Oid eq_opr, Oid typeid , Oid collid,static void ri_InitHashTables(void );static void InvalidateConstraintCacheCallBack(Datum arg, int cacheid, uint32 hashvalue);static SPIPlanPtr ri_FetchPreparedPlan(RI_QueryKey *key);static void ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan);static RI_CompareHashEntry *ri_HashCompareOp(Oid eq_opr, Oid typeid );static void ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname,int tgkind);static const RI_ConstraintInfo *ri_FetchConstraintInfo(Trigger *trigger,bool rel_is_pk);static const RI_ConstraintInfo *ri_LoadConstraintInfo(Oid constraintOid);static Oid get_ri_constraint_root(Oid constrOid);static SPIPlanPtr ri_PlanCheck(const char *querystr, int nargs, Oid *argtypes,static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,bool is_restrict,bool detectNewRows, int expect_OK);static void ri_ExtractValues(Relation rel, TupleTableSlot *slot,const RI_ConstraintInfo *riinfo, bool rel_is_pk,char *nulls);static void ri_ReportViolation(const RI_ConstraintInfo *riinfo,int queryno, bool is_restrict, bool partgone);/* * RI_FKey_check - * * Check foreign key existence ( combined for INSERT and UPDATE ) . static Datumconst RI_ConstraintInfo *riinfo;false );if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))else /* * We should not even consider checking the row if it is no longer valid , * since it was either deleted ( so the deferred check should be skipped ) * or updated ( in which case only the latest version of the row should be * checked ) . Test its liveness according to SnapshotSelf . We need pin * and lock on the buffer to call HeapTupleSatisfiesVisibility . Caller * should be holding pin , but not lock . if (!table_tuple_satisfies_snapshot(trigdata->tg_relation, newslot, SnapshotSelf))return PointerGetDatum(NULL);/* * Get the relation descriptors of the FK and PK tables . * * pk_rel is opened in RowShareLock mode since that ' s what our eventual * SELECT FOR KEY SHARE will get on it . switch (ri_NullCheck(RelationGetDescr(fk_rel), newslot, riinfo, false ))case RI_KEYS_ALL_NULL:/* * No further check needed - an all - NULL key passes every type of * foreign key constraint . return PointerGetDatum(NULL);case RI_KEYS_SOME_NULL:/* * This is the only case that differs between the three kinds of * MATCH . switch (riinfo->confmatchtype)case FKCONSTR_MATCH_FULL:/* * Not allowed - MATCH FULL says either all or none of the * attributes can be NULLs "insert or update on table \" %s\" violates foreign key constraint \" %s\"" ,"MATCH FULL does not allow mixing of null and nonnull key values." ),return PointerGetDatum(NULL);case FKCONSTR_MATCH_SIMPLE:/* * MATCH SIMPLE - if ANY column is null , the key passes * the constraint . return PointerGetDatum(NULL);#ifdef NOT_USEDcase FKCONSTR_MATCH_PARTIAL:/* * MATCH PARTIAL - all non - null columns must match . ( not * implemented , can be done by modifying the query below * to only include non - null columns , or by writing a * special version here ) break ;#endif case RI_KEYS_NONE_NULL:/* * Have a full qualified key - continue below for all three kinds * of MATCH . break ;/* Fetch or prepare a saved plan for the real check */ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)char pkrelname[MAX_QUOTED_REL_NAME_LEN];char attname[MAX_QUOTED_NAME_LEN];char paramname[16 ];const char *querysep;const char *pk_only;/* ---------- * The query string built is * SELECT 1 FROM [ ONLY ] < pktable > x WHERE pkatt1 = $ 1 [ AND . . . ] * FOR KEY SHARE OF x * The type id ' s for the $ parameters are those of the * corresponding FK attributes . * * But for temporal FKs we need to make sure * the FK ' s range is completely covered . * So we use this query instead : * SELECT 1 * FROM ( * SELECT pkperiodatt AS r * FROM [ ONLY ] pktable x * WHERE pkatt1 = $ 1 [ AND . . . ] * AND pkperiodatt & & $ n * FOR KEY SHARE OF x * ) x1 * HAVING $ n < @ range_agg ( x1 . r ) * Note if FOR KEY SHARE ever allows GROUP BY and HAVING * we can make this a bit simpler . * - - - - - - - - - - "" : "ONLY " ;if (riinfo->hasperiod)1 ]));"SELECT 1 FROM (SELECT %s AS r FROM %s%s x" ,else "SELECT 1 FROM %s%s x" ,"WHERE" ;for (int i = 0 ; i < riinfo->nkeys; i++)"$%d" , i + 1 );"AND" ;" FOR KEY SHARE OF x" );if (riinfo->hasperiod)1 ]);") x1 HAVING " );"$%d" , riinfo->nkeys);"" ,"pg_catalog.range_agg" , ANYMULTIRANGEOID);"(x1.r)" );/* Prepare and save the plan */ /* * Now check that foreign key exists in PK table * * XXX detectNewRows must be true when a partitioned table is on the * referenced side . The reason is that our snapshot must be fresh in * order for the hack in find_inheritance_children ( ) to work . false ,if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );return PointerGetDatum(NULL);/* * RI_FKey_check_ins - * * Check foreign key existence at insert event on FK table . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_check_ins" , RI_TRIGTYPE_INSERT);/* Share code with UPDATE case. */ return RI_FKey_check((TriggerData *) fcinfo->context);/* * RI_FKey_check_upd - * * Check foreign key existence at update event on FK table . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_check_upd" , RI_TRIGTYPE_UPDATE);/* Share code with INSERT case. */ return RI_FKey_check((TriggerData *) fcinfo->context);/* * ri_Check_Pk_Match * * Check to see if another PK row has been created that provides the same * key values as the " oldslot " that ' s been modified or deleted in our trigger * event . Returns true if a match is found in the PK table . * * We assume the caller checked that the oldslot contains no NULL key values , * since otherwise a match is impossible . static bool const RI_ConstraintInfo *riinfo)bool result;/* Only called for non-null rows */ true ) == RI_KEYS_NONE_NULL);/* * Fetch or prepare a saved plan for checking PK table with values coming * from a PK row if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)char pkrelname[MAX_QUOTED_REL_NAME_LEN];char attname[MAX_QUOTED_NAME_LEN];char paramname[16 ];const char *querysep;const char *pk_only;/* ---------- * The query string built is * SELECT 1 FROM [ ONLY ] < pktable > x WHERE pkatt1 = $ 1 [ AND . . . ] * FOR KEY SHARE OF x * The type id ' s for the $ parameters are those of the * PK attributes themselves . * * But for temporal FKs we need to make sure * the old PK ' s range is completely covered . * So we use this query instead : * SELECT 1 * FROM ( * SELECT pkperiodatt AS r * FROM [ ONLY ] pktable x * WHERE pkatt1 = $ 1 [ AND . . . ] * AND pkperiodatt & & $ n * FOR KEY SHARE OF x * ) x1 * HAVING $ n < @ range_agg ( x1 . r ) * Note if FOR KEY SHARE ever allows GROUP BY and HAVING * we can make this a bit simpler . * - - - - - - - - - - "" : "ONLY " ;if (riinfo->hasperiod)1 ]));"SELECT 1 FROM (SELECT %s AS r FROM %s%s x" ,else "SELECT 1 FROM %s%s x" ,"WHERE" ;for (int i = 0 ; i < riinfo->nkeys; i++)"$%d" , i + 1 );"AND" ;" FOR KEY SHARE OF x" );if (riinfo->hasperiod)1 ]);") x1 HAVING " );"$%d" , riinfo->nkeys);"" ,"pg_catalog.range_agg" , ANYMULTIRANGEOID);"(x1.r)" );/* Prepare and save the plan */ /* * We have a plan now . Run it . false ,true , /* treat like update */ if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );return result;/* * RI_FKey_noaction_del - * * Give an error and roll back the current transaction if the * delete has resulted in a violation of the given referential * integrity constraint . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_noaction_del" , RI_TRIGTYPE_DELETE);/* Share code with RESTRICT/UPDATE cases. */ return ri_restrict((TriggerData *) fcinfo->context, true );/* * RI_FKey_restrict_del - * * Restrict delete from PK table to rows unreferenced by foreign key . * * The SQL standard intends that this referential action occur exactly when * the delete is performed , rather than after . This appears to be * the only difference between " NO ACTION " and " RESTRICT " . In Postgres * we still implement this as an AFTER trigger , but it ' s non - deferrable . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_restrict_del" , RI_TRIGTYPE_DELETE);/* Share code with NO ACTION/UPDATE cases. */ return ri_restrict((TriggerData *) fcinfo->context, false );/* * RI_FKey_noaction_upd - * * Give an error and roll back the current transaction if the * update has resulted in a violation of the given referential * integrity constraint . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_noaction_upd" , RI_TRIGTYPE_UPDATE);/* Share code with RESTRICT/DELETE cases. */ return ri_restrict((TriggerData *) fcinfo->context, true );/* * RI_FKey_restrict_upd - * * Restrict update of PK to rows unreferenced by foreign key . * * The SQL standard intends that this referential action occur exactly when * the update is performed , rather than after . This appears to be * the only difference between " NO ACTION " and " RESTRICT " . In Postgres * we still implement this as an AFTER trigger , but it ' s non - deferrable . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_restrict_upd" , RI_TRIGTYPE_UPDATE);/* Share code with NO ACTION/DELETE cases. */ return ri_restrict((TriggerData *) fcinfo->context, false );/* * ri_restrict - * * Common code for ON DELETE RESTRICT , ON DELETE NO ACTION , * ON UPDATE RESTRICT , and ON UPDATE NO ACTION . static Datumbool is_no_action)const RI_ConstraintInfo *riinfo;true );/* * Get the relation descriptors of the FK and PK tables and the old tuple . * * fk_rel is opened in RowShareLock mode since that ' s what our eventual * SELECT FOR KEY SHARE will get on it . /* * If another PK row now exists providing the old key values , we should * not do anything . However , this check should only be made in the NO * ACTION case ; in RESTRICT cases we don ' t wish to allow another row to be * substituted . * * If the foreign key has PERIOD , we incorporate looking for replacement * rows in the main SQL query below , so we needn ' t do it here . if (is_no_action && !riinfo->hasperiod &&return PointerGetDatum(NULL);/* * Fetch or prepare a saved plan for the restrict lookup ( it ' s the same * query for delete and update cases ) if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)char pkrelname[MAX_QUOTED_REL_NAME_LEN];char fkrelname[MAX_QUOTED_REL_NAME_LEN];char attname[MAX_QUOTED_NAME_LEN];char periodattname[MAX_QUOTED_NAME_LEN];char paramname[16 ];const char *querysep;const char *fk_only;/* ---------- * The query string built is * SELECT 1 FROM [ ONLY ] < fktable > x WHERE $ 1 = fkatt1 [ AND . . . ] * FOR KEY SHARE OF x * The type id ' s for the $ parameters are those of the * corresponding PK attributes . * - - - - - - - - - - "" : "ONLY " ;"SELECT 1 FROM %s%s x" ,"WHERE" ;for (int i = 0 ; i < riinfo->nkeys; i++)"$%d" , i + 1 );"AND" ;/*---------- * For temporal foreign keys , a reference could still be valid if the * referenced range didn ' t change too much . Also if a referencing * range extends past the current PK row , we don ' t want to check that * part : some other PK row should fulfill it . We only want to check * the part matching the PK record we ' ve changed . Therefore to find * invalid records we do this : * * SELECT 1 FROM [ ONLY ] < fktable > x WHERE $ 1 = x . fkatt1 [ AND . . . ] * - - begin temporal * AND $ n & & x . fkperiod * AND NOT coalesce ( ( x . fkperiod * $ n ) < @ * ( SELECT range_agg ( r ) * FROM ( SELECT y . pkperiod r * FROM [ ONLY ] < pktable > y * WHERE $ 1 = y . pkatt1 [ AND . . . ] AND $ n & & y . pkperiod * FOR KEY SHARE OF y ) y2 ) , false ) * - - end temporal * FOR KEY SHARE OF x * * We need the coalesce in case the first subquery returns no rows . * We need the second subquery because FOR KEY SHARE doesn ' t support * aggregate queries . if (riinfo->hasperiod && is_no_action)1 ]);1 ]);char *pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?"" : "ONLY " ;1 ]));"$%d" , riinfo->nkeys);" AND NOT coalesce(" );/* Intersect the fk with the old pk range */ '(' );"" ,')' );/* Find the remaining history */ "(SELECT pg_catalog.range_agg(r) FROM " );1 ]));"(SELECT y.%s r FROM %s%s y" ,/* Restrict pk rows to what matches */ "WHERE" ;for (int i = 0 ; i < riinfo->nkeys; i++)"$%d" , i + 1 );"AND" ;" FOR KEY SHARE OF y) y2)" );"" ,/* end of coalesce: */ ", false)" );" FOR KEY SHARE OF x" );/* Prepare and save the plan */ /* * We have a plan now . Run it to check for existing references . true , /* must detect new rows */ if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );return PointerGetDatum(NULL);/* * RI_FKey_cascade_del - * * Cascaded delete foreign key references at delete event on PK table . const RI_ConstraintInfo *riinfo;/* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_cascade_del" , RI_TRIGTYPE_DELETE);true );/* * Get the relation descriptors of the FK and PK tables and the old tuple . * * fk_rel is opened in RowExclusiveLock mode since that ' s what our * eventual DELETE will get on it . /* Fetch or prepare a saved plan for the cascaded delete */ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)char fkrelname[MAX_QUOTED_REL_NAME_LEN];char attname[MAX_QUOTED_NAME_LEN];char paramname[16 ];const char *querysep;const char *fk_only;/* ---------- * The query string built is * DELETE FROM [ ONLY ] < fktable > WHERE $ 1 = fkatt1 [ AND . . . ] * The type id ' s for the $ parameters are those of the * corresponding PK attributes . * - - - - - - - - - - "" : "ONLY " ;"DELETE FROM %s%s" ,"WHERE" ;for (int i = 0 ; i < riinfo->nkeys; i++)"$%d" , i + 1 );"AND" ;/* Prepare and save the plan */ /* * We have a plan now . Build up the arguments from the key values in the * deleted PK tuple and delete the referencing rows false ,true , /* must detect new rows */ if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );return PointerGetDatum(NULL);/* * RI_FKey_cascade_upd - * * Cascaded update foreign key references at update event on PK table . const RI_ConstraintInfo *riinfo;/* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_cascade_upd" , RI_TRIGTYPE_UPDATE);true );/* * Get the relation descriptors of the FK and PK tables and the new and * old tuple . * * fk_rel is opened in RowExclusiveLock mode since that ' s what our * eventual UPDATE will get on it . /* Fetch or prepare a saved plan for the cascaded update */ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)char fkrelname[MAX_QUOTED_REL_NAME_LEN];char attname[MAX_QUOTED_NAME_LEN];char paramname[16 ];const char *querysep;const char *qualsep;2 ];const char *fk_only;/* ---------- * The query string built is * UPDATE [ ONLY ] < fktable > SET fkatt1 = $ 1 [ , . . . ] * WHERE $ n = fkatt1 [ AND . . . ] * The type id ' s for the $ parameters are those of the * corresponding PK attributes . Note that we are assuming * there is an assignment cast from the PK to the FK type ; * else the parser will fail . * - - - - - - - - - - "" : "ONLY " ;"UPDATE %s%s SET" ,"" ;"WHERE" ;for (int i = 0 , j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)"%s %s = $%d" ,1 );"$%d" , j + 1 );"," ;"AND" ;/* Prepare and save the plan */ 2 , queryoids,/* * We have a plan now . Run it to update the existing references . false ,true , /* must detect new rows */ if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );return PointerGetDatum(NULL);/* * RI_FKey_setnull_del - * * Set foreign key references to NULL values at delete event on PK table . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_setnull_del" , RI_TRIGTYPE_DELETE);/* Share code with UPDATE case */ return ri_set((TriggerData *) fcinfo->context, true , RI_TRIGTYPE_DELETE);/* * RI_FKey_setnull_upd - * * Set foreign key references to NULL at update event on PK table . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_setnull_upd" , RI_TRIGTYPE_UPDATE);/* Share code with DELETE case */ return ri_set((TriggerData *) fcinfo->context, true , RI_TRIGTYPE_UPDATE);/* * RI_FKey_setdefault_del - * * Set foreign key references to defaults at delete event on PK table . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_setdefault_del" , RI_TRIGTYPE_DELETE);/* Share code with UPDATE case */ return ri_set((TriggerData *) fcinfo->context, false , RI_TRIGTYPE_DELETE);/* * RI_FKey_setdefault_upd - * * Set foreign key references to defaults at update event on PK table . /* Check that this is a valid trigger call on the right time and event. */ "RI_FKey_setdefault_upd" , RI_TRIGTYPE_UPDATE);/* Share code with DELETE case */ return ri_set((TriggerData *) fcinfo->context, false , RI_TRIGTYPE_UPDATE);/* * ri_set - * * Common code for ON DELETE SET NULL , ON DELETE SET DEFAULT , ON UPDATE SET * NULL , and ON UPDATE SET DEFAULT . static Datumbool is_set_null, int tgkind)const RI_ConstraintInfo *riinfo;true );/* * Get the relation descriptors of the FK and PK tables and the old tuple . * * fk_rel is opened in RowExclusiveLock mode since that ' s what our * eventual UPDATE will get on it . /* * Fetch or prepare a saved plan for the trigger . switch (tgkind)case RI_TRIGTYPE_UPDATE:break ;case RI_TRIGTYPE_DELETE:break ;default :"invalid tgkind passed to ri_set" );if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)char fkrelname[MAX_QUOTED_REL_NAME_LEN];char attname[MAX_QUOTED_NAME_LEN];char paramname[16 ];const char *querysep;const char *qualsep;const char *fk_only;int num_cols_to_set;const int16 *set_cols;switch (tgkind)case RI_TRIGTYPE_UPDATE:break ;case RI_TRIGTYPE_DELETE:/* * If confdelsetcols are present , then we only update the * columns specified in that array , otherwise we update all * the referencing columns . if (riinfo->ndelsetcols != 0 )else break ;default :"invalid tgkind passed to ri_set" );/* ---------- * The query string built is * UPDATE [ ONLY ] < fktable > SET fkatt1 = { NULL | DEFAULT } [ , . . . ] * WHERE $ 1 = fkatt1 [ AND . . . ] * The type id ' s for the $ parameters are those of the * corresponding PK attributes . * - - - - - - - - - - "" : "ONLY " ;"UPDATE %s%s SET" ,/* * Add assignment clauses "" ;for (int i = 0 ; i < num_cols_to_set; i++)"%s %s = %s" ,"NULL" : "DEFAULT" );"," ;/* * Add WHERE clause "WHERE" ;for (int i = 0 ; i < riinfo->nkeys; i++)"$%d" , i + 1 );"AND" ;/* Prepare and save the plan */ /* * We have a plan now . Run it to update the existing references . false ,true , /* must detect new rows */ if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );if (is_set_null)return PointerGetDatum(NULL);else /* * If we just deleted or updated the PK row whose key was equal to the * FK columns ' default values , and a referencing row exists in the FK * table , we would have updated that row to the same values it already * had - - - and RI_FKey_fk_upd_check_required would hence believe no * check is necessary . So we need to do another lookup now and in * case a reference still exists , abort the operation . That is * already implemented in the NO ACTION trigger , so just run it . ( This * recheck is only needed in the SET DEFAULT case , since CASCADE would * remove such rows in case of a DELETE operation or would change the * FK key values in case of an UPDATE , while SET NULL is certain to * result in rows that satisfy the FK constraint . ) return ri_restrict(trigdata, true );/* * RI_FKey_pk_upd_check_required - * * Check if we really need to fire the RI trigger for an update or delete to a PK * relation . This is called by the AFTER trigger queue manager to see if * it can skip queuing an instance of an RI trigger . Returns true if the * trigger must be fired , false if we can prove the constraint will still * be satisfied . * * newslot will be NULL if this is called for a delete . bool const RI_ConstraintInfo *riinfo;true );/* * If any old key value is NULL , the row could not have been referenced by * an FK row , so no check is needed . if (ri_NullCheck(RelationGetDescr(pk_rel), oldslot, riinfo, true ) != RI_KEYS_NONE_NULL)return false ;/* If all old and new key values are equal, no check is needed */ if (newslot && ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true ))return false ;/* Else we need to fire the trigger. */ return true ;/* * RI_FKey_fk_upd_check_required - * * Check if we really need to fire the RI trigger for an update to an FK * relation . This is called by the AFTER trigger queue manager to see if * it can skip queuing an instance of an RI trigger . Returns true if the * trigger must be fired , false if we can prove the constraint will still * be satisfied . bool const RI_ConstraintInfo *riinfo;int ri_nullcheck;/* * AfterTriggerSaveEvent ( ) handles things such that this function is never * called for partitioned tables . false );false );/* * If all new key values are NULL , the row satisfies the constraint , so no * check is needed . if (ri_nullcheck == RI_KEYS_ALL_NULL)return false ;/* * If some new key values are NULL , the behavior depends on the match * type . else if (ri_nullcheck == RI_KEYS_SOME_NULL)switch (riinfo->confmatchtype)case FKCONSTR_MATCH_SIMPLE:/* * If any new key value is NULL , the row must satisfy the * constraint , so no check is needed . return false ;case FKCONSTR_MATCH_PARTIAL:/* * Don ' t know , must run full check . break ;case FKCONSTR_MATCH_FULL:/* * If some new key values are NULL , the row fails the * constraint . We must not throw error here , because the row * might get invalidated before the constraint is to be * checked , but we should queue the event to apply the check * later . return true ;/* * Continues here for no new key values are NULL , or we couldn ' t decide * yet . /* * If the original row was inserted by our own transaction , we must fire * the trigger whether or not the keys are equal . This is because our * UPDATE will invalidate the INSERT so that the INSERT RI trigger will * not do anything ; so we had better do the UPDATE check . ( We could skip * this if we knew the INSERT trigger already fired , but there is no easy * way to know that . ) if (slot_is_current_xact_tuple(oldslot))return true ;/* If all old and new key values are equal, no check is needed */ if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false ))return false ;/* Else we need to fire the trigger. */ return true ;/* * RI_Initial_Check - * * Check an entire table for non - matching values using a single query . * This is not a trigger procedure , but is called during ALTER TABLE * ADD FOREIGN KEY to validate the initial table contents . * * We expect that the caller has made provision to prevent any problems * caused by concurrent actions . This could be either by locking rel and * pkrel at ShareRowExclusiveLock or higher , or by otherwise ensuring * that triggers implementing the checks are already active . * Hence , we do not need to lock individual rows for the check . * * If the check fails because the current user doesn ' t have permissions * to read both tables , return false to let our caller know that they will * need to do something else to check the constraint . bool const RI_ConstraintInfo *riinfo;char pkrelname[MAX_QUOTED_REL_NAME_LEN];char fkrelname[MAX_QUOTED_REL_NAME_LEN];char pkattname[MAX_QUOTED_NAME_LEN + 3 ];char fkattname[MAX_QUOTED_NAME_LEN + 3 ];const char *sep;const char *fk_only;const char *pk_only;int save_nestlevel;char workmembuf[32 ];int spi_result;false );/* * Check to make sure current user has enough permissions to do the test * query . ( If not , caller can fall back to the trigger method , which * works because it changes user IDs on the fly . ) * * XXX are there any other show - stopper conditions to check ? for (int i = 0 ; i < riinfo->nkeys; i++)int attno;if (!ExecCheckPermissions(rtes, perminfos, false ))return false ;/* * Also punt if RLS is enabled on either table unless this role has the * bypassrls right or is the table owner of the table ( s ) involved which * have RLS enabled . if (!has_bypassrls_privilege(GetUserId()) &&return false ;/*---------- * The query string built is : * SELECT fk . keycols FROM [ ONLY ] relname fk * LEFT OUTER JOIN [ ONLY ] pkrelname pk * ON ( pk . pkkeycol1 = fk . keycol1 [ AND . . . ] ) * WHERE pk . pkkeycol1 IS NULL AND * For MATCH SIMPLE : * ( fk . keycol1 IS NOT NULL [ AND . . . ] ) * For MATCH FULL : * ( fk . keycol1 IS NOT NULL [ OR . . . ] ) * * We attach COLLATE clauses to the operators when comparing columns * that have different collations . * - - - - - - - - - - "SELECT " );"" ;for (int i = 0 ; i < riinfo->nkeys; i++)"%sfk.%s" , sep, fkattname);", " ;"" : "ONLY " ;"" : "ONLY " ;" FROM %s%s fk LEFT OUTER JOIN %s%s pk ON" ,"pk." );"fk." );"(" ;for (int i = 0 ; i < riinfo->nkeys; i++)3 ,3 ,if (pk_coll != fk_coll)"AND" ;/* * It ' s sufficient to test any one pk attribute for null to detect a join * failure . 0 ]));") WHERE pk.%s IS NULL AND (" , pkattname);"" ;for (int i = 0 ; i < riinfo->nkeys; i++)"%sfk.%s IS NOT NULL" ,switch (riinfo->confmatchtype)case FKCONSTR_MATCH_SIMPLE:" AND " ;break ;case FKCONSTR_MATCH_FULL:" OR " ;break ;')' );/* * Temporarily increase work_mem so that the check query can be executed * more efficiently . It seems okay to do this because the query is simple * enough to not use a multiple of work_mem , and one typically would not * have many large foreign - key validations happening concurrently . So * this seems to meet the criteria for being considered a " maintenance " * operation , and accordingly we use maintenance_work_mem . However , we * must also set hash_mem_multiplier to 1 , since it is surely not okay to * let that get applied to the maintenance_work_mem value . * * We use the equivalent of a function SET option to allow the setting to * persist for exactly the duration of the check query . guc . c also takes * care of undoing the setting on error . sizeof (workmembuf), "%d" , maintenance_work_mem);void ) set_config_option("work_mem" , workmembuf,true , 0 , false );void ) set_config_option("hash_mem_multiplier" , "1" ,true , 0 , false );/* * Generate the plan . We don ' t need to cache it , and there are no * arguments to the plan . 0 , NULL);if (qplan == NULL)"SPI_prepare returned %s for %s" ,/* * Run the plan . For safety we force a current snapshot to be used . ( In * transaction - snapshot mode , this arguably violates transaction isolation * rules , but we really haven ' t got much choice . ) We don ' t need to * register the snapshot , because SPI_execute_snapshot will see to it . We * need at most one tuple returned , so pass limit = 1 . true , false , 1 );/* Check result */ if (spi_result != SPI_OK_SELECT)"SPI_execute_snapshot returned %s" , SPI_result_code_string(spi_result));/* Did we find a tuple violating the constraint? */ if (SPI_processed > 0 )0 ];/* * The columns to look at in the result tuple are 1 . . N , not whatever * they are in the fk_rel . Hack up riinfo so that the subroutines * called here will behave properly . * * In addition to this , we have to pass the correct tupdesc to * ri_ReportViolation , overriding its normal habit of using the pk_rel * or fk_rel ' s tupdesc . sizeof (RI_ConstraintInfo));for (int i = 0 ; i < fake_riinfo.nkeys; i++)1 ;/* * If it ' s MATCH FULL , and there are any nulls in the FK keys , * complain about that rather than the lack of a match . MATCH FULL * disallows partially - null FK rows . if (fake_riinfo.confmatchtype == FKCONSTR_MATCH_FULL &&false ) != RI_KEYS_NONE_NULL)"insert or update on table \" %s\" violates foreign key constraint \" %s\"" ,"MATCH FULL does not allow mixing of null and nonnull key values." ),/* * We tell ri_ReportViolation we were doing the RI_PLAN_CHECK_LOOKUPPK * query , which isn ' t true , but will cause it to use * fake_riinfo . fk_attnums as we need . false , false );if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );/* * Restore work_mem and hash_mem_multiplier . true , save_nestlevel);return true ;/* * RI_PartitionRemove_Check - * * Verify no referencing values exist , when a partition is detached on * the referenced side of a foreign key constraint . void const RI_ConstraintInfo *riinfo;char *constraintDef;char pkrelname[MAX_QUOTED_REL_NAME_LEN];char fkrelname[MAX_QUOTED_REL_NAME_LEN];char pkattname[MAX_QUOTED_NAME_LEN + 3 ];char fkattname[MAX_QUOTED_NAME_LEN + 3 ];const char *sep;const char *fk_only;int save_nestlevel;char workmembuf[32 ];int spi_result;int i;false );/* * We don ' t check permissions before displaying the error message , on the * assumption that the user detaching the partition must have enough * privileges to examine the table contents anyhow . /*---------- * The query string built is : * SELECT fk . keycols FROM [ ONLY ] relname fk * JOIN pkrelname pk * ON ( pk . pkkeycol1 = fk . keycol1 [ AND . . . ] ) * WHERE ( < partition constraint > ) AND * For MATCH SIMPLE : * ( fk . keycol1 IS NOT NULL [ AND . . . ] ) * For MATCH FULL : * ( fk . keycol1 IS NOT NULL [ OR . . . ] ) * * We attach COLLATE clauses to the operators when comparing columns * that have different collations . * - - - - - - - - - - "SELECT " );"" ;for (i = 0 ; i < riinfo->nkeys; i++)"%sfk.%s" , sep, fkattname);", " ;"" : "ONLY " ;" FROM %s%s fk JOIN %s pk ON" ,"pk." );"fk." );"(" ;for (i = 0 ; i < riinfo->nkeys; i++)3 ,3 ,if (pk_coll != fk_coll)"AND" ;/* * Start the WHERE clause with the partition constraint ( except if this is * the default partition and there ' s no other partition , because the * partition constraint is the empty string in that case . ) "pk" );if (constraintDef && constraintDef[0 ] != '\0' )") WHERE %s AND (" ,else ") WHERE (" );"" ;for (i = 0 ; i < riinfo->nkeys; i++)"%sfk.%s IS NOT NULL" ,switch (riinfo->confmatchtype)case FKCONSTR_MATCH_SIMPLE:" AND " ;break ;case FKCONSTR_MATCH_FULL:" OR " ;break ;')' );/* * Temporarily increase work_mem so that the check query can be executed * more efficiently . It seems okay to do this because the query is simple * enough to not use a multiple of work_mem , and one typically would not * have many large foreign - key validations happening concurrently . So * this seems to meet the criteria for being considered a " maintenance " * operation , and accordingly we use maintenance_work_mem . However , we * must also set hash_mem_multiplier to 1 , since it is surely not okay to * let that get applied to the maintenance_work_mem value . * * We use the equivalent of a function SET option to allow the setting to * persist for exactly the duration of the check query . guc . c also takes * care of undoing the setting on error . sizeof (workmembuf), "%d" , maintenance_work_mem);void ) set_config_option("work_mem" , workmembuf,true , 0 , false );void ) set_config_option("hash_mem_multiplier" , "1" ,true , 0 , false );/* * Generate the plan . We don ' t need to cache it , and there are no * arguments to the plan . 0 , NULL);if (qplan == NULL)"SPI_prepare returned %s for %s" ,/* * Run the plan . For safety we force a current snapshot to be used . ( In * transaction - snapshot mode , this arguably violates transaction isolation * rules , but we really haven ' t got much choice . ) We don ' t need to * register the snapshot , because SPI_execute_snapshot will see to it . We * need at most one tuple returned , so pass limit = 1 . true , false , 1 );/* Check result */ if (spi_result != SPI_OK_SELECT)"SPI_execute_snapshot returned %s" , SPI_result_code_string(spi_result));/* Did we find a tuple that would violate the constraint? */ if (SPI_processed > 0 )0 ];/* * The columns to look at in the result tuple are 1 . . N , not whatever * they are in the fk_rel . Hack up riinfo so that ri_ReportViolation * will behave properly . * * In addition to this , we have to pass the correct tupdesc to * ri_ReportViolation , overriding its normal habit of using the pk_rel * or fk_rel ' s tupdesc . sizeof (RI_ConstraintInfo));for (i = 0 ; i < fake_riinfo.nkeys; i++)1 ;0 , false , true );if (SPI_finish() != SPI_OK_FINISH)"SPI_finish failed" );/* * Restore work_mem and hash_mem_multiplier . true , save_nestlevel);/* ---------- * Local functions below * - - - - - - - - - - /* * quoteOneName - - - safely quote a single SQL name * * buffer must be MAX_QUOTED_NAME_LEN long ( includes room for \ 0 ) static void char *buffer, const char *name)/* Rather than trying to be smart, just always quote it. */ '"' ;while (*name)if (*name == '"' )'"' ;'"' ;'\0' ;/* * quoteRelationName - - - safely quote a fully qualified relation name * * buffer must be MAX_QUOTED_REL_NAME_LEN long ( includes room for \ 0 ) static void char *buffer, Relation rel)'.' ;/* * ri_GenerateQual - - - generate a WHERE clause equating two variables * * This basically appends " sep leftop op rightop " to buf , adding casts * and schema qualification as needed to ensure that the parser will select * the operator we specify . leftop and rightop should be parenthesized * if they aren ' t variables or parameters . static void const char *sep,const char *leftop, Oid leftoptype,const char *rightop, Oid rightoptype)" %s " , sep);/* * ri_GenerateQualCollation - - - add a COLLATE spec to a WHERE clause * * We only have to use this function when directly comparing the referencing * and referenced columns , if they are of different collations ; else the * parser will fail to resolve the collation to use . We don ' t need to use * this function for RI queries that compare a variable to a $ n parameter . * Since parameter symbols always have default collation , the effect will be * to use the variable ' s collation . * * Note that we require that the collations of the referencing and the * referenced column have the same notion of equality : Either they have to * both be deterministic or else they both have to be the same . ( See also * ATAddForeignKeyConstraint ( ) . ) static void char *collname;char onename[MAX_QUOTED_NAME_LEN];/* Nothing to do if it's a noncollatable data type */ if (!OidIsValid(collation))return ;if (!HeapTupleIsValid(tp))"cache lookup failed for collation %u" , collation);/* * We qualify the name always , for simplicity and to ensure the query is * not search - path - dependent . " COLLATE %s" , onename);".%s" , onename);/* ---------- * ri_BuildQueryKey - * * Construct a hashtable key for a prepared SPI plan of an FK constraint . * * key : output argument , * key is filled in based on the other arguments * riinfo : info derived from pg_constraint entry * constr_queryno : an internal number identifying the query type * ( see RI_PLAN_XXX constants at head of file ) * - - - - - - - - - - static void const RI_ConstraintInfo *riinfo,/* * Inherited constraints with a common ancestor can share ri_query_cache * entries for all query types except RI_PLAN_CHECK_LOOKUPPK_FROM_PK . * Except in that case , the query processes the other table involved in * the FK constraint ( i . e . , not the table on which the trigger has been * fired ) , and so it will be the same for all members of the inheritance * tree . So we may use the root constraint ' s OID in the hash key , rather * than the constraint ' s own OID . This avoids creating duplicate SPI * plans , saving lots of work and memory when there are many partitions * with similar FK constraints . * * ( Note that we must still have a separate RI_ConstraintInfo for each * constraint , because partitions can have different column orders , * resulting in different pk_attnums [ ] or fk_attnums [ ] array contents . ) * * We assume struct RI_QueryKey contains no padding bytes , else we ' d need * to use memset to clear them . if (constr_queryno != RI_PLAN_CHECK_LOOKUPPK_FROM_PK)else /* * Check that RI trigger function was called in expected context static void const char *funcname, int tgkind)if (!CALLED_AS_TRIGGER(fcinfo))"function \" %s\" was not called by trigger manager" , funcname)));/* * Check proper event if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||"function \" %s\" must be fired AFTER ROW" , funcname)));switch (tgkind)case RI_TRIGTYPE_INSERT:if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))"function \" %s\" must be fired for INSERT" , funcname)));break ;case RI_TRIGTYPE_UPDATE:if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))"function \" %s\" must be fired for UPDATE" , funcname)));break ;case RI_TRIGTYPE_DELETE:if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))"function \" %s\" must be fired for DELETE" , funcname)));break ;/* * Fetch the RI_ConstraintInfo struct for the trigger ' s FK constraint . static const RI_ConstraintInfo *bool rel_is_pk)const RI_ConstraintInfo *riinfo;/* * Check that the FK constraint ' s OID is available ; it might not be if * we ' ve been invoked via an ordinary trigger or an old - style " constraint * trigger " . if (!OidIsValid(constraintOid))"no pg_constraint entry for trigger \" %s\" on table \" %s\"" ,"Remove this referential integrity trigger and its mates, then do ALTER TABLE ADD CONSTRAINT." )));/* Find or create a hashtable entry for the constraint */ /* Do some easy cross-checks against the trigger call data */ if (rel_is_pk)if (riinfo->fk_relid != trigger->tgconstrrelid ||"wrong pg_constraint entry for trigger \" %s\" on table \" %s\"" ,else if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||"wrong pg_constraint entry for trigger \" %s\" on table \" %s\"" ,if (riinfo->confmatchtype != FKCONSTR_MATCH_FULL &&"unrecognized confmatchtype: %d" ,if (riinfo->confmatchtype == FKCONSTR_MATCH_PARTIAL)"MATCH PARTIAL not yet implemented" )));return riinfo;/* * Fetch or create the RI_ConstraintInfo struct for an FK constraint . static const RI_ConstraintInfo *bool found;/* * On the first call initialize the hashtable if (!ri_constraint_cache)/* * Find or create a hash entry . If we find a valid one , just return it . if (!found)false ;else if (riinfo->valid)return riinfo;/* * Fetch the pg_constraint row so we can fill in the entry . if (!HeapTupleIsValid(tup)) /* should not happen */ "cache lookup failed for constraint %u" , constraintOid);if (conForm->contype != CONSTRAINT_FOREIGN) /* should not happen */ "constraint %u is not a foreign key constraint" ,/* And extract data */ if (OidIsValid(conForm->conparentid))else sizeof (NameData));/* * For temporal FKs , get the operators and functions we need . We ask the * opclass of the PK element for these . This all gets cached ( as does the * generated plan ) , so there ' s no performance issue . if (riinfo->hasperiod)/* * For efficient processing of invalidation messages below , we keep a * doubly - linked count list of all currently valid entries . true ;return riinfo;/* * get_ri_constraint_root * Returns the OID of the constraint ' s root parent static Oidfor (;;)if (!HeapTupleIsValid(tuple))"cache lookup failed for constraint %u" , constrOid);if (!OidIsValid(constrParentOid))break ; /* we reached the root constraint */ return constrOid;/* * Callback for pg_constraint inval events * * While most syscache callbacks just flush all their entries , pg_constraint * gets enough update traffic that it ' s probably worth being smarter . * Invalidate any ri_constraint_cache entry associated with the syscache * entry with the specified hash value , or all entries if hashvalue = = 0 . * * Note : at the time a cache invalidation message is processed there may be * active references to the cache . Because of this we never remove entries * from the cache , but only mark them invalid , which is harmless to active * uses . ( Any query using an entry should hold a lock sufficient to keep that * data from changing under it - - - but we may get cache flushes anyway . ) static void int cacheid, uint32 hashvalue)/* * If the list of currently valid entries gets excessively large , we mark * them all invalid so we can empty the list . This arrangement avoids * O ( N ^ 2 ) behavior in situations where a session touches many foreign keys * and also does many ALTER TABLEs , such as a restore from pg_dump . if (dclist_count(&ri_constraint_cache_valid_list) > 1000 )0 ; /* pretend it's a cache reset */ /* * We must invalidate not only entries directly matching the given * hash value , but also child entries , in case the invalidation * affects a root constraint . if (hashvalue == 0 ||false ;/* Remove invalidated entries from the list, too */ /* * Prepare execution plan for a query to enforce an RI restriction static SPIPlanPtrconst char *querystr, int nargs, Oid *argtypes,int save_sec_context;/* * Use the query type code to determine whether the query is run against * the PK or FK table ; we ' ll do the check as that table ' s owner if (qkey->constr_queryno <= RI_PLAN_LAST_ON_PK)else /* Switch to proper UID to perform check as */ /* Create the plan */ if (qplan == NULL)"SPI_prepare returned %s for %s" , SPI_result_code_string(SPI_result), querystr);/* Restore UID and security context */ /* Save the plan */ return qplan;/* * Perform a query to enforce an RI restriction static bool const RI_ConstraintInfo *riinfo,bool is_restrict,bool detectNewRows, int expect_OK)bool source_is_pk;int limit;int spi_result;int save_sec_context;2 ];char nulls[RI_MAX_NUMKEYS * 2 ];/* * Use the query type code to determine whether the query is run against * the PK or FK table ; we ' ll do the check as that table ' s owner if (qkey->constr_queryno <= RI_PLAN_LAST_ON_PK)else /* * The values for the query are taken from the table on which the trigger * is called - it is normally the other one with respect to query_rel . An * exception is ri_Check_Pk_Match ( ) , which uses the PK table for both ( and * sets queryno to RI_PLAN_CHECK_LOOKUPPK_FROM_PK ) . We might eventually * need some less klugy way to determine this . if (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK)false ;else true ;/* Extract the parameters to be passed into the query */ if (newslot)if (oldslot)else /* * In READ COMMITTED mode , we just need to use an up - to - date regular * snapshot , and we will see all rows that could be interesting . But in * transaction - snapshot mode , we can ' t change the transaction snapshot . If * the caller passes detectNewRows = = false then it ' s okay to do the query * with the transaction snapshot ; otherwise we use a current snapshot , and * tell the executor to error out if it finds any rows under the current * snapshot that wouldn ' t be visible per the transaction snapshot . Note * that SPI_execute_snapshot will register the snapshots , so we don ' t need * to bother here . if (IsolationUsesXactSnapshot() && detectNewRows)/* be sure all my own work is visible */ else /* the default SPI behavior is okay */ /* * If this is a select query ( e . g . , for a ' no action ' or ' restrict ' * trigger ) , we only need to see if there is a single row in the table , * matching the key . Otherwise , limit = 0 - because we want the query to * affect ALL the matching rows . 1 : 0 ;/* Switch to proper UID to perform check as */ /* Finally we can run the query. */ false , false , limit);/* Restore UID and security context */ /* Check result */ if (spi_result < 0 )"SPI_execute_snapshot returned %s" , SPI_result_code_string(spi_result));if (expect_OK >= 0 && spi_result != expect_OK)"referential integrity query on \" %s\" from constraint \" %s\" on \" %s\" gave unexpected result" ,"This is most likely due to a rule having rewritten the query." )));/* XXX wouldn't it be clearer to do this part at the caller? */ if (qkey->constr_queryno != RI_PLAN_CHECK_LOOKUPPK_FROM_PK &&0 ) == (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK))false );return SPI_processed != 0 ;/* * Extract fields from a tuple into Datum / nulls arrays static void const RI_ConstraintInfo *riinfo, bool rel_is_pk,char *nulls)const int16 *attnums;bool isnull;if (rel_is_pk)else for (int i = 0 ; i < riinfo->nkeys; i++)'n' : ' ' ;/* * Produce an error report * * If the failed constraint was on insert / update to the FK table , * we want the key names and values extracted from there , and the error * message to look like ' key blah is not present in PK ' . * Otherwise , the attr names and values come from the PK table and the * message looks like ' key blah is still referenced from FK ' . static void const RI_ConstraintInfo *riinfo,int queryno, bool is_restrict, bool partgone)bool onfk;const int16 *attnums;bool has_perm = true ;/* * Determine which relation to complain about . If tupdesc wasn ' t passed * by caller , assume the violator tuple came from there . if (onfk)if (tupdesc == NULL)else if (tupdesc == NULL)/* * Check permissions - if the user does not have access to view the data in * any of the key columns then we don ' t include the errdetail ( ) below . * * Check if RLS is enabled on the relation first . If so , we don ' t return * any specifics to avoid leaking data . * * Check table - level permissions next and , failing that , column - level * privileges . * * When a partition at the referenced side is being detached / dropped , we * needn ' t check , since the user must be the table owner anyway . if (partgone)true ;else if (check_enable_rls(rel_oid, InvalidOid, true ) != RLS_ENABLED)if (aclresult != ACLCHECK_OK)/* Try for column-level permissions */ for (int idx = 0 ; idx < riinfo->nkeys; idx++)/* No access to the key */ if (aclresult != ACLCHECK_OK)false ;break ;else false ;if (has_perm)/* Get printable versions of the keys involved */ for (int idx = 0 ; idx < riinfo->nkeys; idx++)int fnum = attnums[idx];1 );char *name,bool isnull;if (!isnull)bool typisvarlena;else "null" ;if (idx > 0 )", " );", " );if (partgone)"removing partition \" %s\" violates foreign key constraint \" %s\"" ,"Key (%s)=(%s) is still referenced from table \" %s\"." ,else if (onfk)"insert or update on table \" %s\" violates foreign key constraint \" %s\"" ,"Key (%s)=(%s) is not present in table \" %s\"." ,"Key is not present in table \" %s\"." ,else if (is_restrict)"update or delete on table \" %s\" violates RESTRICT setting of foreign key constraint \" %s\" on table \" %s\"" ,"Key (%s)=(%s) is referenced from table \" %s\"." ,"Key is referenced from table \" %s\"." ,else "update or delete on table \" %s\" violates foreign key constraint \" %s\" on table \" %s\"" ,"Key (%s)=(%s) is still referenced from table \" %s\"." ,"Key is still referenced from table \" %s\"." ,/* * ri_NullCheck - * * Determine the NULL state of all key values in a tuple * * Returns one of RI_KEYS_ALL_NULL , RI_KEYS_NONE_NULL or RI_KEYS_SOME_NULL . static int const RI_ConstraintInfo *riinfo, bool rel_is_pk)const int16 *attnums;bool allnull = true ;bool nonenull = true ;if (rel_is_pk)else for (int i = 0 ; i < riinfo->nkeys; i++)if (slot_attisnull(slot, attnums[i]))false ;else false ;if (allnull)return RI_KEYS_ALL_NULL;if (nonenull)return RI_KEYS_NONE_NULL;return RI_KEYS_SOME_NULL;/* * ri_InitHashTables - * * Initialize our internal hash tables . static void void )sizeof (Oid);sizeof (RI_ConstraintInfo);"RI constraint cache" ,/* Arrange to flush cache on pg_constraint changes */ 0 );sizeof (RI_QueryKey);sizeof (RI_QueryHashEntry);"RI query cache" ,sizeof (RI_CompareKey);sizeof (RI_CompareHashEntry);"RI compare cache" ,/* * ri_FetchPreparedPlan - * * Lookup for a query key in our private hash table of prepared * and saved SPI execution plans . Return the plan if found or NULL . static SPIPlanPtr/* * On the first call initialize the hashtable if (!ri_query_cache)/* * Lookup for the key if (entry == NULL)return NULL;/* * Check whether the plan is still valid . If it isn ' t , we don ' t want to * simply rely on plancache . c to regenerate it ; rather we should start * from scratch and rebuild the query text too . This is to cover cases * such as table / column renames . We depend on the plancache machinery to * detect possible invalidations , though . * * CAUTION : this check is only trustworthy if the caller has already * locked both FK and PK rels . if (plan && SPI_plan_is_valid(plan))return plan;/* * Otherwise we might as well flush the cached plan now , to free a little * memory space before we make a new one . if (plan)return NULL;/* * ri_HashPreparedPlan - * * Add another plan to our private SPI query plan hashtable . static void bool found;/* * On the first call initialize the hashtable if (!ri_query_cache)/* * Add the new plan . We might be overwriting an entry previously found * invalid by ri_FetchPreparedPlan . /* * ri_KeysEqual - * * Check if all key values in OLD and NEW are " equivalent " : * For normal FKs we check for equality . * For temporal FKs we check that the PK side is a superset of its old value , * or the FK side is a subset of its old value . * * Note : at some point we might wish to redefine this as checking for * " IS NOT DISTINCT " rather than " = " , that is , allow two nulls to be * considered equal . Currently there is no need since all callers have * previously found at least one of the rows to contain no nulls . static bool const RI_ConstraintInfo *riinfo, bool rel_is_pk)const int16 *attnums;if (rel_is_pk)else /* XXX: could be worthwhile to fetch all necessary attrs at once */ for (int i = 0 ; i < riinfo->nkeys; i++)bool isnull;/* * Get one attribute ' s oldvalue . If it is NULL - they ' re not equal . if (isnull)return false ;/* * Get one attribute ' s newvalue . If it is NULL - they ' re not equal . if (isnull)return false ;if (rel_is_pk)/* * If we are looking at the PK table , then do a bytewise * comparison . We must propagate PK changes if the value is * changed to one that " looks " different but would compare as * equal using the equality operator . This only makes a * difference for ON UPDATE CASCADE , but for consistency we treat * all changes to the PK the same . - 1 );if (!datum_image_eq(oldvalue, newvalue, att->attbyval, att->attlen))return false ;else /* * When comparing the PERIOD columns we can skip the check * whenever the referencing column stayed equal or shrank , so test * with the contained - by operator instead . if (riinfo->hasperiod && i == riinfo->nkeys - 1 )else /* * For the FK table , compare with the appropriate equality * operator . Changes that compare equal will still satisfy the * constraint after the update . if (!ri_CompareWithCast(eq_opr, RIAttType(rel, attnums[i]), RIAttCollation(rel, attnums[i]),return false ;return true ;/* * ri_CompareWithCast - * * Call the appropriate comparison operator for two values . * Normally this is equality , but for the PERIOD part of foreign keys * it is ContainedBy , so the order of lhs vs rhs is significant . * See below for how the collation is applied . * * NB : we have already checked that neither value is null . static bool typeid , Oid collid,typeid );/* Do we need to cast the values? */ if (OidIsValid(entry->cast_func_finfo.fn_oid))1 ), /* typmod */ false )); /* implicit coercion */ 1 ), /* typmod */ false )); /* implicit coercion */ /* * Apply the comparison operator . * * Note : This function is part of a call stack that determines whether an * update to a row is significant enough that it needs checking or action * on the other side of a foreign - key constraint . Therefore , the * comparison here would need to be done with the collation of the * other * * table . For simplicity ( e . g . , we might not even have the other table * open ) , we ' ll use our own collation . This is fine because we require * that both collations have the same notion of equality ( either they are * both deterministic or else they are both the same ) . * * With range / multirangetypes , the collation of the base type is stored as * part of the rangetype ( pg_range . rngcollation ) , and always used , so * there is no danger of inconsistency even using a non - equals operator . * But if we support arbitrary types with PERIOD , we should perhaps just * always force a re - check . return DatumGetBool(FunctionCall2Coll(&entry->eq_opr_finfo, collid, lhs, rhs));/* * ri_HashCompareOp - * * See if we know how to compare two values , and create a new hash entry * if not . static RI_CompareHashEntry *typeid )bool found;/* * On the first call initialize the hashtable if (!ri_compare_cache)/* * Find or create a hash entry . Note we ' re assuming RI_CompareKey * contains no struct padding . typeid = typeid ;if (!found)false ;/* * If not already initialized , do so . Since we ' ll keep this hash entry * for the life of the backend , put any subsidiary info for the function * cache structs into TopMemoryContext . if (!entry->valid)/* We always need to know how to call the equality operator */ /* * If we chose to use a cast from FK to PK type , we may have to apply * the cast function to get to the operator ' s input type . * * XXX eventually it would be good to support array - coercion cases * here and in ri_CompareWithCast ( ) . At the moment there is no point * because cases involving nonidentical array types will be rejected * at constraint creation time . * * XXX perhaps also consider supporting CoerceViaIO ? No need at the * moment since that will never be generated for implicit coercions . if (typeid == lefttype)/* simplest case */ else typeid ,if (pathtype != COERCION_PATH_FUNC &&/* * The declared input type of the eq_opr might be a * polymorphic type such as ANYARRAY or ANYENUM , or other * special cases such as RECORD ; find_coercion_pathway * currently doesn ' t subsume these special cases . if (!IsBinaryCoercible(typeid , lefttype))"no conversion function from %s to %s" ,typeid ),if (OidIsValid(castfunc))else true ;return entry;/* * Given a trigger function OID , determine whether it is an RI trigger , * and if so whether it is attached to PK or FK relation . int switch (tgfoid)case F_RI_FKEY_CASCADE_DEL:case F_RI_FKEY_CASCADE_UPD:case F_RI_FKEY_RESTRICT_DEL:case F_RI_FKEY_RESTRICT_UPD:case F_RI_FKEY_SETNULL_DEL:case F_RI_FKEY_SETNULL_UPD:case F_RI_FKEY_SETDEFAULT_DEL:case F_RI_FKEY_SETDEFAULT_UPD:case F_RI_FKEY_NOACTION_DEL:case F_RI_FKEY_NOACTION_UPD:return RI_TRIGGER_PK;case F_RI_FKEY_CHECK_INS:case F_RI_FKEY_CHECK_UPD:return RI_TRIGGER_FK;return RI_TRIGGER_NONE;
Messung V0.5 in Prozent C=94 H=94 G=93
¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen0.96Angebot
(Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können 2026-08-08)
¤ *Eine klare Vorstellung vom Zielzustand
