4
* Generic trigger procedures for referential integrity constraint
7
* Note about memory management: the private hashtables kept here live
8
* across query and transaction boundaries, in fact they live as long as
9
* the backend does. This works because the hashtable structures
10
* themselves are allocated by dynahash.c in its permanent DynaHashCxt,
11
* and the parse/plan node trees they point to are copied into
12
* TopMemoryContext using SPI_saveplan(). This is pretty ugly, since there
13
* is no way to free a no-longer-needed plan tree, but then again we don't
14
* yet have any bookkeeping that would allow us to detect that a plan isn't
15
* needed anymore. Improve it someday.
18
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
20
* $PostgreSQL: pgsql/src/backend/utils/adt/ri_triggers.c,v 1.76 2004-12-31 22:01:22 pgsql Exp $
29
* Add MATCH PARTIAL logic.
35
#include "catalog/pg_operator.h"
36
#include "commands/trigger.h"
37
#include "executor/spi_priv.h"
38
#include "optimizer/planmain.h"
39
#include "parser/parse_oper.h"
40
#include "rewrite/rewriteHandler.h"
41
#include "utils/lsyscache.h"
42
#include "utils/typcache.h"
43
#include "utils/acl.h"
44
#include "utils/guc.h"
45
#include "miscadmin.h"
53
#define RI_INIT_QUERYHASHSIZE 128
55
#define RI_MATCH_TYPE_UNSPECIFIED 0
56
#define RI_MATCH_TYPE_FULL 1
57
#define RI_MATCH_TYPE_PARTIAL 2
59
#define RI_KEYS_ALL_NULL 0
60
#define RI_KEYS_SOME_NULL 1
61
#define RI_KEYS_NONE_NULL 2
63
/* queryno values must be distinct for the convenience of ri_PerformCheck */
64
#define RI_PLAN_CHECK_LOOKUPPK_NOCOLS 1
65
#define RI_PLAN_CHECK_LOOKUPPK 2
66
#define RI_PLAN_CASCADE_DEL_DODELETE 3
67
#define RI_PLAN_CASCADE_UPD_DOUPDATE 4
68
#define RI_PLAN_NOACTION_DEL_CHECKREF 5
69
#define RI_PLAN_NOACTION_UPD_CHECKREF 6
70
#define RI_PLAN_RESTRICT_DEL_CHECKREF 7
71
#define RI_PLAN_RESTRICT_UPD_CHECKREF 8
72
#define RI_PLAN_SETNULL_DEL_DOUPDATE 9
73
#define RI_PLAN_SETNULL_UPD_DOUPDATE 10
74
#define RI_PLAN_KEYEQUAL_UPD 11
76
#define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3)
77
#define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2)
79
#define RI_TRIGTYPE_INSERT 1
80
#define RI_TRIGTYPE_UPDATE 2
81
#define RI_TRIGTYPE_INUP 3
82
#define RI_TRIGTYPE_DELETE 4
88
* The key identifying a prepared SPI plan in our private hashtable
91
typedef struct RI_QueryKey
99
int16 keypair[RI_MAX_NUMKEYS][2];
107
typedef struct RI_QueryHashEntry
118
static HTAB *ri_query_cache = NULL;
122
* Local function prototypes
125
static void quoteOneName(char *buffer, const char *name);
126
static void quoteRelationName(char *buffer, Relation rel);
127
static int ri_DetermineMatchType(char *str);
128
static int ri_NullCheck(Relation rel, HeapTuple tup,
129
RI_QueryKey *key, int pairidx);
130
static void ri_BuildQueryKeyFull(RI_QueryKey *key, Oid constr_id,
131
int32 constr_queryno,
132
Relation fk_rel, Relation pk_rel,
133
int argc, char **argv);
134
static void ri_BuildQueryKeyPkCheck(RI_QueryKey *key, Oid constr_id,
135
int32 constr_queryno,
137
int argc, char **argv);
138
static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
139
RI_QueryKey *key, int pairidx);
140
static bool ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
141
RI_QueryKey *key, int pairidx);
142
static bool ri_OneKeyEqual(Relation rel, int column, HeapTuple oldtup,
143
HeapTuple newtup, RI_QueryKey *key, int pairidx);
144
static bool ri_AttributesEqual(Oid typeid, Datum oldvalue, Datum newvalue);
145
static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
147
Oid tgoid, int match_type,
148
int tgnargs, char **tgargs);
150
static void ri_InitHashTables(void);
151
static void *ri_FetchPreparedPlan(RI_QueryKey *key);
152
static void ri_HashPreparedPlan(RI_QueryKey *key, void *plan);
154
static void ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname,
156
static void *ri_PlanCheck(const char *querystr, int nargs, Oid *argtypes,
157
RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel,
159
static bool ri_PerformCheck(RI_QueryKey *qkey, void *qplan,
160
Relation fk_rel, Relation pk_rel,
161
HeapTuple old_tuple, HeapTuple new_tuple,
163
int expect_OK, const char *constrname);
164
static void ri_ExtractValues(RI_QueryKey *qkey, int key_idx,
165
Relation rel, HeapTuple tuple,
166
Datum *vals, char *nulls);
167
static void ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
168
Relation pk_rel, Relation fk_rel,
169
HeapTuple violator, TupleDesc tupdesc,
176
* Check foreign key existence (combined for INSERT and UPDATE).
180
RI_FKey_check(PG_FUNCTION_ARGS)
182
TriggerData *trigdata = (TriggerData *) fcinfo->context;
196
* Check that this is a valid trigger call on the right time and
199
ri_CheckTrigger(fcinfo, "RI_FKey_check", RI_TRIGTYPE_INUP);
201
tgnargs = trigdata->tg_trigger->tgnargs;
202
tgargs = trigdata->tg_trigger->tgargs;
205
* Get the relation descriptors of the FK and PK tables and the new
208
* pk_rel is opened in RowShareLock mode since that's what our eventual
209
* SELECT FOR UPDATE will get on it.
211
pk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
212
fk_rel = trigdata->tg_relation;
213
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
215
old_row = trigdata->tg_trigtuple;
216
new_row = trigdata->tg_newtuple;
217
new_row_buf = trigdata->tg_newtuplebuf;
222
new_row = trigdata->tg_trigtuple;
223
new_row_buf = trigdata->tg_trigtuplebuf;
227
* We should not even consider checking the row if it is no longer
228
* valid since it was either deleted (doesn't matter) or updated (in
229
* which case it'll be checked with its final values).
231
Assert(new_row_buf != InvalidBuffer);
232
if (!HeapTupleSatisfiesItself(new_row->t_data, new_row_buf))
234
heap_close(pk_rel, RowShareLock);
235
return PointerGetDatum(NULL);
239
* SQL3 11.9 <referential constraint definition>
240
* General rules 2) a):
241
* If Rf and Rt are empty (no columns to compare given)
242
* constraint is true if 0 < (SELECT COUNT(*) FROM T)
244
* Note: The special case that no columns are given cannot
245
* occur up to now in Postgres, it's just there for
246
* future enhancements.
251
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
252
RI_PLAN_CHECK_LOOKUPPK_NOCOLS,
256
if (SPI_connect() != SPI_OK_CONNECT)
257
elog(ERROR, "SPI_connect failed");
259
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
261
char querystr[MAX_QUOTED_REL_NAME_LEN + 100];
262
char pkrelname[MAX_QUOTED_REL_NAME_LEN];
265
* The query string built is
266
* SELECT 1 FROM ONLY <pktable>
269
quoteRelationName(pkrelname, pk_rel);
270
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x FOR UPDATE OF x",
273
/* Prepare and save the plan */
274
qplan = ri_PlanCheck(querystr, 0, NULL,
275
&qkey, fk_rel, pk_rel, true);
281
ri_PerformCheck(&qkey, qplan,
286
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
288
if (SPI_finish() != SPI_OK_FINISH)
289
elog(ERROR, "SPI_finish failed");
291
heap_close(pk_rel, RowShareLock);
293
return PointerGetDatum(NULL);
297
match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
299
if (match_type == RI_MATCH_TYPE_PARTIAL)
301
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
302
errmsg("MATCH PARTIAL not yet implemented")));
304
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
305
RI_PLAN_CHECK_LOOKUPPK, fk_rel, pk_rel,
308
switch (ri_NullCheck(fk_rel, new_row, &qkey, RI_KEYPAIR_FK_IDX))
310
case RI_KEYS_ALL_NULL:
313
* No check - if NULLs are allowed at all is already checked
314
* by NOT NULL constraint.
316
* This is true for MATCH FULL, MATCH PARTIAL, and MATCH
319
heap_close(pk_rel, RowShareLock);
320
return PointerGetDatum(NULL);
322
case RI_KEYS_SOME_NULL:
325
* This is the only case that differs between the three kinds
330
case RI_MATCH_TYPE_FULL:
333
* Not allowed - MATCH FULL says either all or none of
334
* the attributes can be NULLs
337
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
338
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
339
RelationGetRelationName(trigdata->tg_relation),
340
tgargs[RI_CONSTRAINT_NAME_ARGNO]),
341
errdetail("MATCH FULL does not allow mixing of null and nonnull key values.")));
342
heap_close(pk_rel, RowShareLock);
343
return PointerGetDatum(NULL);
345
case RI_MATCH_TYPE_UNSPECIFIED:
348
* MATCH <unspecified> - if ANY column is null, we
351
heap_close(pk_rel, RowShareLock);
352
return PointerGetDatum(NULL);
354
case RI_MATCH_TYPE_PARTIAL:
357
* MATCH PARTIAL - all non-null columns must match.
358
* (not implemented, can be done by modifying the
359
* query below to only include non-null columns, or by
360
* writing a special version here)
363
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
364
errmsg("MATCH PARTIAL not yet implemented")));
365
heap_close(pk_rel, RowShareLock);
366
return PointerGetDatum(NULL);
369
case RI_KEYS_NONE_NULL:
372
* Have a full qualified key - continue below for all three
379
* No need to check anything if old and new references are the same on
382
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
384
if (HeapTupleHeaderGetXmin(old_row->t_data) !=
385
GetCurrentTransactionId() &&
386
ri_KeysEqual(fk_rel, old_row, new_row, &qkey,
389
heap_close(pk_rel, RowShareLock);
390
return PointerGetDatum(NULL);
394
if (SPI_connect() != SPI_OK_CONNECT)
395
elog(ERROR, "SPI_connect failed");
398
* Fetch or prepare a saved plan for the real check
400
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
402
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
403
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
404
char pkrelname[MAX_QUOTED_REL_NAME_LEN];
405
char attname[MAX_QUOTED_NAME_LEN];
406
const char *querysep;
407
Oid queryoids[RI_MAX_NUMKEYS];
410
* The query string built is
411
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
412
* The type id's for the $ parameters are those of the
413
* corresponding FK attributes. Thus, ri_PlanCheck could
414
* eventually fail if the parser cannot identify some way
415
* how to compare these two types by '='.
418
quoteRelationName(pkrelname, pk_rel);
419
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", pkrelname);
421
for (i = 0; i < qkey.nkeypairs; i++)
423
quoteOneName(attname,
424
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_PK_IDX]);
425
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
426
querysep, attname, i + 1);
428
queryoids[i] = SPI_gettypeid(fk_rel->rd_att,
429
qkey.keypair[i][RI_KEYPAIR_FK_IDX]);
431
strcat(querystr, " FOR UPDATE OF x");
433
/* Prepare and save the plan */
434
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
435
&qkey, fk_rel, pk_rel, true);
439
* Now check that foreign key exists in PK table
441
ri_PerformCheck(&qkey, qplan,
446
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
448
if (SPI_finish() != SPI_OK_FINISH)
449
elog(ERROR, "SPI_finish failed");
451
heap_close(pk_rel, RowShareLock);
453
return PointerGetDatum(NULL);
458
* RI_FKey_check_ins -
460
* Check foreign key existence at insert event on FK table.
464
RI_FKey_check_ins(PG_FUNCTION_ARGS)
466
return RI_FKey_check(fcinfo);
471
* RI_FKey_check_upd -
473
* Check foreign key existence at update event on FK table.
477
RI_FKey_check_upd(PG_FUNCTION_ARGS)
479
return RI_FKey_check(fcinfo);
486
* Check for matching value of old pk row in current state for
487
* noaction triggers. Returns false if no row was found and a fk row
488
* could potentially be referencing this row, true otherwise.
492
ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
494
Oid tgoid, int match_type,
495
int tgnargs, char **tgargs)
502
ri_BuildQueryKeyPkCheck(&qkey, tgoid,
503
RI_PLAN_CHECK_LOOKUPPK, pk_rel,
506
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
508
case RI_KEYS_ALL_NULL:
511
* No check - nothing could have been referencing this row
516
case RI_KEYS_SOME_NULL:
519
* This is the only case that differs between the three kinds
524
case RI_MATCH_TYPE_FULL:
525
case RI_MATCH_TYPE_UNSPECIFIED:
528
* MATCH <unspecified>/FULL - if ANY column is null,
529
* we can't be matching to this row already.
533
case RI_MATCH_TYPE_PARTIAL:
536
* MATCH PARTIAL - all non-null columns must match.
537
* (not implemented, can be done by modifying the
538
* query below to only include non-null columns, or by
539
* writing a special version here)
542
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
543
errmsg("MATCH PARTIAL not yet implemented")));
547
case RI_KEYS_NONE_NULL:
550
* Have a full qualified key - continue below for all three
556
if (SPI_connect() != SPI_OK_CONNECT)
557
elog(ERROR, "SPI_connect failed");
560
* Fetch or prepare a saved plan for the real check
562
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
564
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
565
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
566
char pkrelname[MAX_QUOTED_REL_NAME_LEN];
567
char attname[MAX_QUOTED_NAME_LEN];
568
const char *querysep;
569
Oid queryoids[RI_MAX_NUMKEYS];
572
* The query string built is
573
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
574
* The type id's for the $ parameters are those of the
575
* corresponding FK attributes. Thus, ri_PlanCheck could
576
* eventually fail if the parser cannot identify some way
577
* how to compare these two types by '='.
580
quoteRelationName(pkrelname, pk_rel);
581
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", pkrelname);
583
for (i = 0; i < qkey.nkeypairs; i++)
585
quoteOneName(attname,
586
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_PK_IDX]);
587
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
588
querysep, attname, i + 1);
590
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
591
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
593
strcat(querystr, " FOR UPDATE OF x");
595
/* Prepare and save the plan */
596
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
597
&qkey, fk_rel, pk_rel, true);
601
* We have a plan now. Run it.
603
result = ri_PerformCheck(&qkey, qplan,
606
true, /* treat like update */
607
SPI_OK_SELECT, NULL);
609
if (SPI_finish() != SPI_OK_FINISH)
610
elog(ERROR, "SPI_finish failed");
617
* RI_FKey_noaction_del -
619
* Give an error and roll back the current transaction if the
620
* delete has resulted in a violation of the given referential
621
* integrity constraint.
625
RI_FKey_noaction_del(PG_FUNCTION_ARGS)
627
TriggerData *trigdata = (TriggerData *) fcinfo->context;
639
* Check that this is a valid trigger call on the right time and
642
ri_CheckTrigger(fcinfo, "RI_FKey_noaction_del", RI_TRIGTYPE_DELETE);
644
tgnargs = trigdata->tg_trigger->tgnargs;
645
tgargs = trigdata->tg_trigger->tgargs;
648
* Nothing to do if no column names to compare given
651
return PointerGetDatum(NULL);
654
* Get the relation descriptors of the FK and PK tables and the old
657
* fk_rel is opened in RowShareLock mode since that's what our eventual
658
* SELECT FOR UPDATE will get on it.
660
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
661
pk_rel = trigdata->tg_relation;
662
old_row = trigdata->tg_trigtuple;
664
match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
665
if (ri_Check_Pk_Match(pk_rel, fk_rel,
666
old_row, trigdata->tg_trigger->tgoid,
667
match_type, tgnargs, tgargs))
670
* There's either another row, or no row could match this one. In
671
* either case, we don't need to do the check.
673
heap_close(fk_rel, RowShareLock);
674
return PointerGetDatum(NULL);
680
* SQL3 11.9 <referential constraint definition>
681
* Gereral rules 6) a) iv):
682
* MATCH <unspecified> or MATCH FULL
683
* ... ON DELETE CASCADE
686
case RI_MATCH_TYPE_UNSPECIFIED:
687
case RI_MATCH_TYPE_FULL:
688
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
689
RI_PLAN_NOACTION_DEL_CHECKREF,
693
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
695
case RI_KEYS_ALL_NULL:
696
case RI_KEYS_SOME_NULL:
699
* No check - MATCH FULL means there cannot be any
700
* reference to old key if it contains NULL
702
heap_close(fk_rel, RowShareLock);
703
return PointerGetDatum(NULL);
705
case RI_KEYS_NONE_NULL:
708
* Have a full qualified key - continue below
713
if (SPI_connect() != SPI_OK_CONNECT)
714
elog(ERROR, "SPI_connect failed");
717
* Fetch or prepare a saved plan for the restrict delete
718
* lookup if foreign references exist
720
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
722
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
723
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
724
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
725
char attname[MAX_QUOTED_NAME_LEN];
726
const char *querysep;
727
Oid queryoids[RI_MAX_NUMKEYS];
730
* The query string built is
731
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
732
* The type id's for the $ parameters are those of the
733
* corresponding PK attributes. Thus, ri_PlanCheck could
734
* eventually fail if the parser cannot identify some way
735
* how to compare these two types by '='.
738
quoteRelationName(fkrelname, fk_rel);
739
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
741
for (i = 0; i < qkey.nkeypairs; i++)
743
quoteOneName(attname,
744
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
745
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
746
querysep, attname, i + 1);
748
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
749
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
751
strcat(querystr, " FOR UPDATE OF x");
753
/* Prepare and save the plan */
754
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
755
&qkey, fk_rel, pk_rel, true);
759
* We have a plan now. Run it to check for existing
762
ri_PerformCheck(&qkey, qplan,
765
true, /* must detect new rows */
767
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
769
if (SPI_finish() != SPI_OK_FINISH)
770
elog(ERROR, "SPI_finish failed");
772
heap_close(fk_rel, RowShareLock);
774
return PointerGetDatum(NULL);
777
* Handle MATCH PARTIAL restrict delete.
779
case RI_MATCH_TYPE_PARTIAL:
781
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
782
errmsg("MATCH PARTIAL not yet implemented")));
783
return PointerGetDatum(NULL);
789
elog(ERROR, "invalid match_type");
790
return PointerGetDatum(NULL);
795
* RI_FKey_noaction_upd -
797
* Give an error and roll back the current transaction if the
798
* update has resulted in a violation of the given referential
799
* integrity constraint.
803
RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
805
TriggerData *trigdata = (TriggerData *) fcinfo->context;
818
* Check that this is a valid trigger call on the right time and
821
ri_CheckTrigger(fcinfo, "RI_FKey_noaction_upd", RI_TRIGTYPE_UPDATE);
823
tgnargs = trigdata->tg_trigger->tgnargs;
824
tgargs = trigdata->tg_trigger->tgargs;
827
* Nothing to do if no column names to compare given
830
return PointerGetDatum(NULL);
833
* Get the relation descriptors of the FK and PK tables and the new
836
* fk_rel is opened in RowShareLock mode since that's what our eventual
837
* SELECT FOR UPDATE will get on it.
839
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
840
pk_rel = trigdata->tg_relation;
841
new_row = trigdata->tg_newtuple;
842
old_row = trigdata->tg_trigtuple;
844
match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
849
* SQL3 11.9 <referential constraint definition>
850
* Gereral rules 6) a) iv):
851
* MATCH <unspecified> or MATCH FULL
852
* ... ON DELETE CASCADE
855
case RI_MATCH_TYPE_UNSPECIFIED:
856
case RI_MATCH_TYPE_FULL:
857
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
858
RI_PLAN_NOACTION_UPD_CHECKREF,
862
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
864
case RI_KEYS_ALL_NULL:
865
case RI_KEYS_SOME_NULL:
868
* No check - MATCH FULL means there cannot be any
869
* reference to old key if it contains NULL
871
heap_close(fk_rel, RowShareLock);
872
return PointerGetDatum(NULL);
874
case RI_KEYS_NONE_NULL:
877
* Have a full qualified key - continue below
883
* No need to check anything if old and new keys are equal
885
if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
888
heap_close(fk_rel, RowShareLock);
889
return PointerGetDatum(NULL);
892
if (ri_Check_Pk_Match(pk_rel, fk_rel,
893
old_row, trigdata->tg_trigger->tgoid,
894
match_type, tgnargs, tgargs))
897
* There's either another row, or no row could match this
898
* one. In either case, we don't need to do the check.
900
heap_close(fk_rel, RowShareLock);
901
return PointerGetDatum(NULL);
904
if (SPI_connect() != SPI_OK_CONNECT)
905
elog(ERROR, "SPI_connect failed");
908
* Fetch or prepare a saved plan for the noaction update
909
* lookup if foreign references exist
911
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
913
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
914
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
915
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
916
char attname[MAX_QUOTED_NAME_LEN];
917
const char *querysep;
918
Oid queryoids[RI_MAX_NUMKEYS];
921
* The query string built is
922
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
923
* The type id's for the $ parameters are those of the
924
* corresponding PK attributes. Thus, ri_PlanCheck could
925
* eventually fail if the parser cannot identify some way
926
* how to compare these two types by '='.
929
quoteRelationName(fkrelname, fk_rel);
930
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
932
for (i = 0; i < qkey.nkeypairs; i++)
934
quoteOneName(attname,
935
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
936
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
937
querysep, attname, i + 1);
939
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
940
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
942
strcat(querystr, " FOR UPDATE OF x");
944
/* Prepare and save the plan */
945
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
946
&qkey, fk_rel, pk_rel, true);
950
* We have a plan now. Run it to check for existing
953
ri_PerformCheck(&qkey, qplan,
956
true, /* must detect new rows */
958
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
960
if (SPI_finish() != SPI_OK_FINISH)
961
elog(ERROR, "SPI_finish failed");
963
heap_close(fk_rel, RowShareLock);
965
return PointerGetDatum(NULL);
968
* Handle MATCH PARTIAL noaction update.
970
case RI_MATCH_TYPE_PARTIAL:
972
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
973
errmsg("MATCH PARTIAL not yet implemented")));
974
return PointerGetDatum(NULL);
980
elog(ERROR, "invalid match_type");
981
return PointerGetDatum(NULL);
986
* RI_FKey_cascade_del -
988
* Cascaded delete foreign key references at delete event on PK table.
992
RI_FKey_cascade_del(PG_FUNCTION_ARGS)
994
TriggerData *trigdata = (TriggerData *) fcinfo->context;
1005
* Check that this is a valid trigger call on the right time and
1008
ri_CheckTrigger(fcinfo, "RI_FKey_cascade_del", RI_TRIGTYPE_DELETE);
1010
tgnargs = trigdata->tg_trigger->tgnargs;
1011
tgargs = trigdata->tg_trigger->tgargs;
1014
* Nothing to do if no column names to compare given
1017
return PointerGetDatum(NULL);
1020
* Get the relation descriptors of the FK and PK tables and the old
1023
* fk_rel is opened in RowExclusiveLock mode since that's what our
1024
* eventual DELETE will get on it.
1026
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
1027
pk_rel = trigdata->tg_relation;
1028
old_row = trigdata->tg_trigtuple;
1030
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
1033
* SQL3 11.9 <referential constraint definition>
1034
* Gereral rules 6) a) i):
1035
* MATCH <unspecified> or MATCH FULL
1036
* ... ON DELETE CASCADE
1039
case RI_MATCH_TYPE_UNSPECIFIED:
1040
case RI_MATCH_TYPE_FULL:
1041
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1042
RI_PLAN_CASCADE_DEL_DODELETE,
1046
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1048
case RI_KEYS_ALL_NULL:
1049
case RI_KEYS_SOME_NULL:
1052
* No check - MATCH FULL means there cannot be any
1053
* reference to old key if it contains NULL
1055
heap_close(fk_rel, RowExclusiveLock);
1056
return PointerGetDatum(NULL);
1058
case RI_KEYS_NONE_NULL:
1061
* Have a full qualified key - continue below
1066
if (SPI_connect() != SPI_OK_CONNECT)
1067
elog(ERROR, "SPI_connect failed");
1070
* Fetch or prepare a saved plan for the cascaded delete
1072
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1074
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
1075
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1076
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1077
char attname[MAX_QUOTED_NAME_LEN];
1078
const char *querysep;
1079
Oid queryoids[RI_MAX_NUMKEYS];
1082
* The query string built is
1083
* DELETE FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
1084
* The type id's for the $ parameters are those of the
1085
* corresponding PK attributes. Thus, ri_PlanCheck could
1086
* eventually fail if the parser cannot identify some way
1087
* how to compare these two types by '='.
1090
quoteRelationName(fkrelname, fk_rel);
1091
snprintf(querystr, sizeof(querystr), "DELETE FROM ONLY %s", fkrelname);
1093
for (i = 0; i < qkey.nkeypairs; i++)
1095
quoteOneName(attname,
1096
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1097
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
1098
querysep, attname, i + 1);
1100
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1101
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1104
/* Prepare and save the plan */
1105
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
1106
&qkey, fk_rel, pk_rel, true);
1110
* We have a plan now. Build up the arguments from the key
1111
* values in the deleted PK tuple and delete the referencing
1114
ri_PerformCheck(&qkey, qplan,
1117
true, /* must detect new rows */
1119
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
1121
if (SPI_finish() != SPI_OK_FINISH)
1122
elog(ERROR, "SPI_finish failed");
1124
heap_close(fk_rel, RowExclusiveLock);
1126
return PointerGetDatum(NULL);
1129
* Handle MATCH PARTIAL cascaded delete.
1131
case RI_MATCH_TYPE_PARTIAL:
1133
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1134
errmsg("MATCH PARTIAL not yet implemented")));
1135
return PointerGetDatum(NULL);
1141
elog(ERROR, "invalid match_type");
1142
return PointerGetDatum(NULL);
1147
* RI_FKey_cascade_upd -
1149
* Cascaded update/delete foreign key references at update event on PK table.
1153
RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
1155
TriggerData *trigdata = (TriggerData *) fcinfo->context;
1168
* Check that this is a valid trigger call on the right time and
1171
ri_CheckTrigger(fcinfo, "RI_FKey_cascade_upd", RI_TRIGTYPE_UPDATE);
1173
tgnargs = trigdata->tg_trigger->tgnargs;
1174
tgargs = trigdata->tg_trigger->tgargs;
1177
* Nothing to do if no column names to compare given
1180
return PointerGetDatum(NULL);
1183
* Get the relation descriptors of the FK and PK tables and the new
1186
* fk_rel is opened in RowExclusiveLock mode since that's what our
1187
* eventual UPDATE will get on it.
1189
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
1190
pk_rel = trigdata->tg_relation;
1191
new_row = trigdata->tg_newtuple;
1192
old_row = trigdata->tg_trigtuple;
1194
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
1197
* SQL3 11.9 <referential constraint definition>
1198
* Gereral rules 7) a) i):
1199
* MATCH <unspecified> or MATCH FULL
1200
* ... ON UPDATE CASCADE
1203
case RI_MATCH_TYPE_UNSPECIFIED:
1204
case RI_MATCH_TYPE_FULL:
1205
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1206
RI_PLAN_CASCADE_UPD_DOUPDATE,
1210
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1212
case RI_KEYS_ALL_NULL:
1213
case RI_KEYS_SOME_NULL:
1216
* No update - MATCH FULL means there cannot be any
1217
* reference to old key if it contains NULL
1219
heap_close(fk_rel, RowExclusiveLock);
1220
return PointerGetDatum(NULL);
1222
case RI_KEYS_NONE_NULL:
1225
* Have a full qualified key - continue below
1231
* No need to do anything if old and new keys are equal
1233
if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
1236
heap_close(fk_rel, RowExclusiveLock);
1237
return PointerGetDatum(NULL);
1240
if (SPI_connect() != SPI_OK_CONNECT)
1241
elog(ERROR, "SPI_connect failed");
1244
* Fetch or prepare a saved plan for the cascaded update of
1245
* foreign references
1247
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1249
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
1250
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
1251
char qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1252
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1253
char attname[MAX_QUOTED_NAME_LEN];
1254
const char *querysep;
1255
const char *qualsep;
1256
Oid queryoids[RI_MAX_NUMKEYS * 2];
1259
* The query string built is
1260
* UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]
1261
* WHERE fkatt1 = $n [AND ...]
1262
* The type id's for the $ parameters are those of the
1263
* corresponding PK attributes. Thus, ri_PlanCheck could
1264
* eventually fail if the parser cannot identify some way
1265
* how to compare these two types by '='.
1268
quoteRelationName(fkrelname, fk_rel);
1269
snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
1273
for (i = 0, j = qkey.nkeypairs; i < qkey.nkeypairs; i++, j++)
1275
quoteOneName(attname,
1276
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1277
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = $%d",
1278
querysep, attname, i + 1);
1279
snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
1280
qualsep, attname, j + 1);
1283
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1284
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1285
queryoids[j] = queryoids[i];
1287
strcat(querystr, qualstr);
1289
/* Prepare and save the plan */
1290
qplan = ri_PlanCheck(querystr, qkey.nkeypairs * 2, queryoids,
1291
&qkey, fk_rel, pk_rel, true);
1295
* We have a plan now. Run it to update the existing
1298
ri_PerformCheck(&qkey, qplan,
1301
true, /* must detect new rows */
1303
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
1305
if (SPI_finish() != SPI_OK_FINISH)
1306
elog(ERROR, "SPI_finish failed");
1308
heap_close(fk_rel, RowExclusiveLock);
1310
return PointerGetDatum(NULL);
1313
* Handle MATCH PARTIAL cascade update.
1315
case RI_MATCH_TYPE_PARTIAL:
1317
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1318
errmsg("MATCH PARTIAL not yet implemented")));
1319
return PointerGetDatum(NULL);
1325
elog(ERROR, "invalid match_type");
1326
return PointerGetDatum(NULL);
1331
* RI_FKey_restrict_del -
1333
* Restrict delete from PK table to rows unreferenced by foreign key.
1335
* SQL3 intends that this referential action occur BEFORE the
1336
* update is performed, rather than after. This appears to be
1337
* the only difference between "NO ACTION" and "RESTRICT".
1339
* For now, however, we treat "RESTRICT" and "NO ACTION" as
1344
RI_FKey_restrict_del(PG_FUNCTION_ARGS)
1346
TriggerData *trigdata = (TriggerData *) fcinfo->context;
1357
* Check that this is a valid trigger call on the right time and
1360
ri_CheckTrigger(fcinfo, "RI_FKey_restrict_del", RI_TRIGTYPE_DELETE);
1362
tgnargs = trigdata->tg_trigger->tgnargs;
1363
tgargs = trigdata->tg_trigger->tgargs;
1366
* Nothing to do if no column names to compare given
1369
return PointerGetDatum(NULL);
1372
* Get the relation descriptors of the FK and PK tables and the old
1375
* fk_rel is opened in RowShareLock mode since that's what our eventual
1376
* SELECT FOR UPDATE will get on it.
1378
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
1379
pk_rel = trigdata->tg_relation;
1380
old_row = trigdata->tg_trigtuple;
1382
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
1385
* SQL3 11.9 <referential constraint definition>
1386
* Gereral rules 6) a) iv):
1387
* MATCH <unspecified> or MATCH FULL
1388
* ... ON DELETE CASCADE
1391
case RI_MATCH_TYPE_UNSPECIFIED:
1392
case RI_MATCH_TYPE_FULL:
1393
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1394
RI_PLAN_RESTRICT_DEL_CHECKREF,
1398
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1400
case RI_KEYS_ALL_NULL:
1401
case RI_KEYS_SOME_NULL:
1404
* No check - MATCH FULL means there cannot be any
1405
* reference to old key if it contains NULL
1407
heap_close(fk_rel, RowShareLock);
1408
return PointerGetDatum(NULL);
1410
case RI_KEYS_NONE_NULL:
1413
* Have a full qualified key - continue below
1418
if (SPI_connect() != SPI_OK_CONNECT)
1419
elog(ERROR, "SPI_connect failed");
1422
* Fetch or prepare a saved plan for the restrict delete
1423
* lookup if foreign references exist
1425
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1427
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
1428
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1429
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1430
char attname[MAX_QUOTED_NAME_LEN];
1431
const char *querysep;
1432
Oid queryoids[RI_MAX_NUMKEYS];
1435
* The query string built is
1436
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
1437
* The type id's for the $ parameters are those of the
1438
* corresponding PK attributes. Thus, ri_PlanCheck could
1439
* eventually fail if the parser cannot identify some way
1440
* how to compare these two types by '='.
1443
quoteRelationName(fkrelname, fk_rel);
1444
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
1446
for (i = 0; i < qkey.nkeypairs; i++)
1448
quoteOneName(attname,
1449
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1450
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
1451
querysep, attname, i + 1);
1453
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1454
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1456
strcat(querystr, " FOR UPDATE OF x");
1458
/* Prepare and save the plan */
1459
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
1460
&qkey, fk_rel, pk_rel, true);
1464
* We have a plan now. Run it to check for existing
1467
ri_PerformCheck(&qkey, qplan,
1470
true, /* must detect new rows */
1472
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
1474
if (SPI_finish() != SPI_OK_FINISH)
1475
elog(ERROR, "SPI_finish failed");
1477
heap_close(fk_rel, RowShareLock);
1479
return PointerGetDatum(NULL);
1482
* Handle MATCH PARTIAL restrict delete.
1484
case RI_MATCH_TYPE_PARTIAL:
1486
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1487
errmsg("MATCH PARTIAL not yet implemented")));
1488
return PointerGetDatum(NULL);
1494
elog(ERROR, "invalid match_type");
1495
return PointerGetDatum(NULL);
1500
* RI_FKey_restrict_upd -
1502
* Restrict update of PK to rows unreferenced by foreign key.
1504
* SQL3 intends that this referential action occur BEFORE the
1505
* update is performed, rather than after. This appears to be
1506
* the only difference between "NO ACTION" and "RESTRICT".
1508
* For now, however, we treat "RESTRICT" and "NO ACTION" as
1513
RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
1515
TriggerData *trigdata = (TriggerData *) fcinfo->context;
1527
* Check that this is a valid trigger call on the right time and
1530
ri_CheckTrigger(fcinfo, "RI_FKey_restrict_upd", RI_TRIGTYPE_UPDATE);
1532
tgnargs = trigdata->tg_trigger->tgnargs;
1533
tgargs = trigdata->tg_trigger->tgargs;
1536
* Nothing to do if no column names to compare given
1539
return PointerGetDatum(NULL);
1542
* Get the relation descriptors of the FK and PK tables and the new
1545
* fk_rel is opened in RowShareLock mode since that's what our eventual
1546
* SELECT FOR UPDATE will get on it.
1548
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
1549
pk_rel = trigdata->tg_relation;
1550
new_row = trigdata->tg_newtuple;
1551
old_row = trigdata->tg_trigtuple;
1553
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
1556
* SQL3 11.9 <referential constraint definition>
1557
* Gereral rules 6) a) iv):
1558
* MATCH <unspecified> or MATCH FULL
1559
* ... ON DELETE CASCADE
1562
case RI_MATCH_TYPE_UNSPECIFIED:
1563
case RI_MATCH_TYPE_FULL:
1564
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1565
RI_PLAN_RESTRICT_UPD_CHECKREF,
1569
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1571
case RI_KEYS_ALL_NULL:
1572
case RI_KEYS_SOME_NULL:
1575
* No check - MATCH FULL means there cannot be any
1576
* reference to old key if it contains NULL
1578
heap_close(fk_rel, RowShareLock);
1579
return PointerGetDatum(NULL);
1581
case RI_KEYS_NONE_NULL:
1584
* Have a full qualified key - continue below
1590
* No need to check anything if old and new keys are equal
1592
if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
1595
heap_close(fk_rel, RowShareLock);
1596
return PointerGetDatum(NULL);
1599
if (SPI_connect() != SPI_OK_CONNECT)
1600
elog(ERROR, "SPI_connect failed");
1603
* Fetch or prepare a saved plan for the restrict update
1604
* lookup if foreign references exist
1606
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1608
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
1609
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1610
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1611
char attname[MAX_QUOTED_NAME_LEN];
1612
const char *querysep;
1613
Oid queryoids[RI_MAX_NUMKEYS];
1616
* The query string built is
1617
* SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
1618
* The type id's for the $ parameters are those of the
1619
* corresponding PK attributes. Thus, ri_PlanCheck could
1620
* eventually fail if the parser cannot identify some way
1621
* how to compare these two types by '='.
1624
quoteRelationName(fkrelname, fk_rel);
1625
snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
1627
for (i = 0; i < qkey.nkeypairs; i++)
1629
quoteOneName(attname,
1630
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1631
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
1632
querysep, attname, i + 1);
1634
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1635
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1637
strcat(querystr, " FOR UPDATE OF x");
1639
/* Prepare and save the plan */
1640
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
1641
&qkey, fk_rel, pk_rel, true);
1645
* We have a plan now. Run it to check for existing
1648
ri_PerformCheck(&qkey, qplan,
1651
true, /* must detect new rows */
1653
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
1655
if (SPI_finish() != SPI_OK_FINISH)
1656
elog(ERROR, "SPI_finish failed");
1658
heap_close(fk_rel, RowShareLock);
1660
return PointerGetDatum(NULL);
1663
* Handle MATCH PARTIAL restrict update.
1665
case RI_MATCH_TYPE_PARTIAL:
1667
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1668
errmsg("MATCH PARTIAL not yet implemented")));
1669
return PointerGetDatum(NULL);
1675
elog(ERROR, "invalid match_type");
1676
return PointerGetDatum(NULL);
1681
* RI_FKey_setnull_del -
1683
* Set foreign key references to NULL values at delete event on PK table.
1687
RI_FKey_setnull_del(PG_FUNCTION_ARGS)
1689
TriggerData *trigdata = (TriggerData *) fcinfo->context;
1700
* Check that this is a valid trigger call on the right time and
1703
ri_CheckTrigger(fcinfo, "RI_FKey_setnull_del", RI_TRIGTYPE_DELETE);
1705
tgnargs = trigdata->tg_trigger->tgnargs;
1706
tgargs = trigdata->tg_trigger->tgargs;
1709
* Nothing to do if no column names to compare given
1712
return PointerGetDatum(NULL);
1715
* Get the relation descriptors of the FK and PK tables and the old
1718
* fk_rel is opened in RowExclusiveLock mode since that's what our
1719
* eventual UPDATE will get on it.
1721
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
1722
pk_rel = trigdata->tg_relation;
1723
old_row = trigdata->tg_trigtuple;
1725
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
1728
* SQL3 11.9 <referential constraint definition>
1729
* Gereral rules 6) a) ii):
1730
* MATCH <UNSPECIFIED> or MATCH FULL
1731
* ... ON DELETE SET NULL
1734
case RI_MATCH_TYPE_UNSPECIFIED:
1735
case RI_MATCH_TYPE_FULL:
1736
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1737
RI_PLAN_SETNULL_DEL_DOUPDATE,
1741
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1743
case RI_KEYS_ALL_NULL:
1744
case RI_KEYS_SOME_NULL:
1747
* No update - MATCH FULL means there cannot be any
1748
* reference to old key if it contains NULL
1750
heap_close(fk_rel, RowExclusiveLock);
1751
return PointerGetDatum(NULL);
1753
case RI_KEYS_NONE_NULL:
1756
* Have a full qualified key - continue below
1761
if (SPI_connect() != SPI_OK_CONNECT)
1762
elog(ERROR, "SPI_connect failed");
1765
* Fetch or prepare a saved plan for the set null delete
1768
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1770
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
1771
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
1772
char qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1773
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1774
char attname[MAX_QUOTED_NAME_LEN];
1775
const char *querysep;
1776
const char *qualsep;
1777
Oid queryoids[RI_MAX_NUMKEYS];
1780
* The query string built is
1781
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
1782
* WHERE fkatt1 = $1 [AND ...]
1783
* The type id's for the $ parameters are those of the
1784
* corresponding PK attributes. Thus, ri_PlanCheck could
1785
* eventually fail if the parser cannot identify some way
1786
* how to compare these two types by '='.
1789
quoteRelationName(fkrelname, fk_rel);
1790
snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
1794
for (i = 0; i < qkey.nkeypairs; i++)
1796
quoteOneName(attname,
1797
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1798
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
1800
snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
1801
qualsep, attname, i + 1);
1804
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1805
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1807
strcat(querystr, qualstr);
1809
/* Prepare and save the plan */
1810
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
1811
&qkey, fk_rel, pk_rel, true);
1815
* We have a plan now. Run it to check for existing
1818
ri_PerformCheck(&qkey, qplan,
1821
true, /* must detect new rows */
1823
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
1825
if (SPI_finish() != SPI_OK_FINISH)
1826
elog(ERROR, "SPI_finish failed");
1828
heap_close(fk_rel, RowExclusiveLock);
1830
return PointerGetDatum(NULL);
1833
* Handle MATCH PARTIAL set null delete.
1835
case RI_MATCH_TYPE_PARTIAL:
1837
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1838
errmsg("MATCH PARTIAL not yet implemented")));
1839
return PointerGetDatum(NULL);
1845
elog(ERROR, "invalid match_type");
1846
return PointerGetDatum(NULL);
1851
* RI_FKey_setnull_upd -
1853
* Set foreign key references to NULL at update event on PK table.
1857
RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
1859
TriggerData *trigdata = (TriggerData *) fcinfo->context;
1870
bool use_cached_query;
1873
* Check that this is a valid trigger call on the right time and
1876
ri_CheckTrigger(fcinfo, "RI_FKey_setnull_upd", RI_TRIGTYPE_UPDATE);
1878
tgnargs = trigdata->tg_trigger->tgnargs;
1879
tgargs = trigdata->tg_trigger->tgargs;
1882
* Nothing to do if no column names to compare given
1885
return PointerGetDatum(NULL);
1888
* Get the relation descriptors of the FK and PK tables and the old
1891
* fk_rel is opened in RowExclusiveLock mode since that's what our
1892
* eventual UPDATE will get on it.
1894
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
1895
pk_rel = trigdata->tg_relation;
1896
new_row = trigdata->tg_newtuple;
1897
old_row = trigdata->tg_trigtuple;
1898
match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
1903
* SQL3 11.9 <referential constraint definition>
1904
* Gereral rules 7) a) ii) 2):
1906
* ... ON UPDATE SET NULL
1909
case RI_MATCH_TYPE_UNSPECIFIED:
1910
case RI_MATCH_TYPE_FULL:
1911
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1912
RI_PLAN_SETNULL_UPD_DOUPDATE,
1916
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1918
case RI_KEYS_ALL_NULL:
1919
case RI_KEYS_SOME_NULL:
1922
* No update - MATCH FULL means there cannot be any
1923
* reference to old key if it contains NULL
1925
heap_close(fk_rel, RowExclusiveLock);
1926
return PointerGetDatum(NULL);
1928
case RI_KEYS_NONE_NULL:
1931
* Have a full qualified key - continue below
1937
* No need to do anything if old and new keys are equal
1939
if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
1942
heap_close(fk_rel, RowExclusiveLock);
1943
return PointerGetDatum(NULL);
1946
if (SPI_connect() != SPI_OK_CONNECT)
1947
elog(ERROR, "SPI_connect failed");
1950
* "MATCH <unspecified>" only changes columns corresponding to
1951
* the referenced columns that have changed in pk_rel. This
1952
* means the "SET attrn=NULL [, attrn=NULL]" string will be
1953
* change as well. In this case, we need to build a temporary
1954
* plan rather than use our cached plan, unless the update
1955
* happens to change all columns in the key. Fortunately, for
1956
* the most common case of a single-column foreign key, this
1959
* In case you're wondering, the inequality check works because
1960
* we know that the old key value has no NULLs (see above).
1963
use_cached_query = match_type == RI_MATCH_TYPE_FULL ||
1964
ri_AllKeysUnequal(pk_rel, old_row, new_row,
1965
&qkey, RI_KEYPAIR_PK_IDX);
1968
* Fetch or prepare a saved plan for the set null update
1969
* operation if possible, or build a temporary plan if not.
1971
if (!use_cached_query ||
1972
(qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
1974
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
1975
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
1976
char qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1977
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
1978
char attname[MAX_QUOTED_NAME_LEN];
1979
const char *querysep;
1980
const char *qualsep;
1981
Oid queryoids[RI_MAX_NUMKEYS];
1984
* The query string built is
1985
* UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
1986
* WHERE fkatt1 = $1 [AND ...]
1987
* The type id's for the $ parameters are those of the
1988
* corresponding PK attributes. Thus, ri_PlanCheck could
1989
* eventually fail if the parser cannot identify some way
1990
* how to compare these two types by '='.
1993
quoteRelationName(fkrelname, fk_rel);
1994
snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
1998
for (i = 0; i < qkey.nkeypairs; i++)
2000
quoteOneName(attname,
2001
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
2004
* MATCH <unspecified> - only change columns
2005
* corresponding to changed columns in pk_rel's key
2007
if (match_type == RI_MATCH_TYPE_FULL ||
2008
!ri_OneKeyEqual(pk_rel, i, old_row, new_row, &qkey,
2011
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
2015
snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
2016
qualsep, attname, i + 1);
2018
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2019
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
2021
strcat(querystr, qualstr);
2024
* Prepare the plan. Save it only if we're building the
2027
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
2028
&qkey, fk_rel, pk_rel,
2033
* We have a plan now. Run it to update the existing
2036
ri_PerformCheck(&qkey, qplan,
2039
true, /* must detect new rows */
2041
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
2043
if (SPI_finish() != SPI_OK_FINISH)
2044
elog(ERROR, "SPI_finish failed");
2046
heap_close(fk_rel, RowExclusiveLock);
2048
return PointerGetDatum(NULL);
2051
* Handle MATCH PARTIAL set null update.
2053
case RI_MATCH_TYPE_PARTIAL:
2055
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2056
errmsg("MATCH PARTIAL not yet implemented")));
2057
return PointerGetDatum(NULL);
2063
elog(ERROR, "invalid match_type");
2064
return PointerGetDatum(NULL);
2069
* RI_FKey_setdefault_del -
2071
* Set foreign key references to defaults at delete event on PK table.
2075
RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
2077
TriggerData *trigdata = (TriggerData *) fcinfo->context;
2087
* Check that this is a valid trigger call on the right time and
2090
ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_del", RI_TRIGTYPE_DELETE);
2092
tgnargs = trigdata->tg_trigger->tgnargs;
2093
tgargs = trigdata->tg_trigger->tgargs;
2096
* Nothing to do if no column names to compare given
2099
return PointerGetDatum(NULL);
2102
* Get the relation descriptors of the FK and PK tables and the old
2105
* fk_rel is opened in RowExclusiveLock mode since that's what our
2106
* eventual UPDATE will get on it.
2108
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
2109
pk_rel = trigdata->tg_relation;
2110
old_row = trigdata->tg_trigtuple;
2112
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
2115
* SQL3 11.9 <referential constraint definition>
2116
* Gereral rules 6) a) iii):
2117
* MATCH <UNSPECIFIED> or MATCH FULL
2118
* ... ON DELETE SET DEFAULT
2121
case RI_MATCH_TYPE_UNSPECIFIED:
2122
case RI_MATCH_TYPE_FULL:
2123
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2124
RI_PLAN_SETNULL_DEL_DOUPDATE,
2128
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
2130
case RI_KEYS_ALL_NULL:
2131
case RI_KEYS_SOME_NULL:
2134
* No update - MATCH FULL means there cannot be any
2135
* reference to old key if it contains NULL
2137
heap_close(fk_rel, RowExclusiveLock);
2138
return PointerGetDatum(NULL);
2140
case RI_KEYS_NONE_NULL:
2143
* Have a full qualified key - continue below
2148
if (SPI_connect() != SPI_OK_CONNECT)
2149
elog(ERROR, "SPI_connect failed");
2152
* Prepare a plan for the set default delete operation.
2153
* Unfortunately we need to do it on every invocation because
2154
* the default value could potentially change between calls.
2157
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
2158
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
2159
char qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
2160
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
2161
char attname[MAX_QUOTED_NAME_LEN];
2162
const char *querysep;
2163
const char *qualsep;
2164
Oid queryoids[RI_MAX_NUMKEYS];
2168
* The query string built is
2169
* UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]
2170
* WHERE fkatt1 = $1 [AND ...]
2171
* The type id's for the $ parameters are those of the
2172
* corresponding PK attributes. Thus, ri_PlanCheck could
2173
* eventually fail if the parser cannot identify some way
2174
* how to compare these two types by '='.
2177
quoteRelationName(fkrelname, fk_rel);
2178
snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
2182
for (i = 0; i < qkey.nkeypairs; i++)
2184
quoteOneName(attname,
2185
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
2186
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = DEFAULT",
2188
snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
2189
qualsep, attname, i + 1);
2192
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2193
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
2195
strcat(querystr, qualstr);
2197
/* Prepare the plan, don't save it */
2198
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
2199
&qkey, fk_rel, pk_rel, false);
2203
* We have a plan now. Run it to update the existing
2206
ri_PerformCheck(&qkey, qplan,
2209
true, /* must detect new rows */
2211
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
2213
if (SPI_finish() != SPI_OK_FINISH)
2214
elog(ERROR, "SPI_finish failed");
2216
heap_close(fk_rel, RowExclusiveLock);
2219
* In the case we delete the row who's key is equal to the
2220
* default values AND a referencing row in the foreign key
2221
* table exists, we would just have updated it to the same
2222
* values. We need to do another lookup now and in case a
2223
* reference exists, abort the operation. That is already
2224
* implemented in the NO ACTION trigger.
2226
RI_FKey_noaction_del(fcinfo);
2228
return PointerGetDatum(NULL);
2231
* Handle MATCH PARTIAL set null delete.
2233
case RI_MATCH_TYPE_PARTIAL:
2235
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2236
errmsg("MATCH PARTIAL not yet implemented")));
2237
return PointerGetDatum(NULL);
2243
elog(ERROR, "invalid match_type");
2244
return PointerGetDatum(NULL);
2249
* RI_FKey_setdefault_upd -
2251
* Set foreign key references to defaults at update event on PK table.
2255
RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
2257
TriggerData *trigdata = (TriggerData *) fcinfo->context;
2269
* Check that this is a valid trigger call on the right time and
2272
ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_upd", RI_TRIGTYPE_UPDATE);
2274
tgnargs = trigdata->tg_trigger->tgnargs;
2275
tgargs = trigdata->tg_trigger->tgargs;
2278
* Nothing to do if no column names to compare given
2281
return PointerGetDatum(NULL);
2284
* Get the relation descriptors of the FK and PK tables and the old
2287
* fk_rel is opened in RowExclusiveLock mode since that's what our
2288
* eventual UPDATE will get on it.
2290
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
2291
pk_rel = trigdata->tg_relation;
2292
new_row = trigdata->tg_newtuple;
2293
old_row = trigdata->tg_trigtuple;
2295
match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
2300
* SQL3 11.9 <referential constraint definition>
2301
* Gereral rules 7) a) iii):
2302
* MATCH <UNSPECIFIED> or MATCH FULL
2303
* ... ON UPDATE SET DEFAULT
2306
case RI_MATCH_TYPE_UNSPECIFIED:
2307
case RI_MATCH_TYPE_FULL:
2308
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2309
RI_PLAN_SETNULL_DEL_DOUPDATE,
2313
switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
2315
case RI_KEYS_ALL_NULL:
2316
case RI_KEYS_SOME_NULL:
2319
* No update - MATCH FULL means there cannot be any
2320
* reference to old key if it contains NULL
2322
heap_close(fk_rel, RowExclusiveLock);
2323
return PointerGetDatum(NULL);
2325
case RI_KEYS_NONE_NULL:
2328
* Have a full qualified key - continue below
2334
* No need to do anything if old and new keys are equal
2336
if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
2339
heap_close(fk_rel, RowExclusiveLock);
2340
return PointerGetDatum(NULL);
2343
if (SPI_connect() != SPI_OK_CONNECT)
2344
elog(ERROR, "SPI_connect failed");
2347
* Prepare a plan for the set default delete operation.
2348
* Unfortunately we need to do it on every invocation because
2349
* the default value could potentially change between calls.
2352
char querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
2353
(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
2354
char qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
2355
char fkrelname[MAX_QUOTED_REL_NAME_LEN];
2356
char attname[MAX_QUOTED_NAME_LEN];
2357
const char *querysep;
2358
const char *qualsep;
2359
Oid queryoids[RI_MAX_NUMKEYS];
2363
* The query string built is
2364
* UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]
2365
* WHERE fkatt1 = $1 [AND ...]
2366
* The type id's for the $ parameters are those of the
2367
* corresponding PK attributes. Thus, ri_PlanCheck could
2368
* eventually fail if the parser cannot identify some way
2369
* how to compare these two types by '='.
2372
quoteRelationName(fkrelname, fk_rel);
2373
snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
2377
for (i = 0; i < qkey.nkeypairs; i++)
2379
quoteOneName(attname,
2380
tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
2383
* MATCH <unspecified> - only change columns
2384
* corresponding to changed columns in pk_rel's key
2386
if (match_type == RI_MATCH_TYPE_FULL ||
2387
!ri_OneKeyEqual(pk_rel, i, old_row,
2388
new_row, &qkey, RI_KEYPAIR_PK_IDX))
2390
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = DEFAULT",
2394
snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
2395
qualsep, attname, i + 1);
2397
queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2398
qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
2400
strcat(querystr, qualstr);
2402
/* Prepare the plan, don't save it */
2403
qplan = ri_PlanCheck(querystr, qkey.nkeypairs, queryoids,
2404
&qkey, fk_rel, pk_rel, false);
2408
* We have a plan now. Run it to update the existing
2411
ri_PerformCheck(&qkey, qplan,
2414
true, /* must detect new rows */
2416
tgargs[RI_CONSTRAINT_NAME_ARGNO]);
2418
if (SPI_finish() != SPI_OK_FINISH)
2419
elog(ERROR, "SPI_finish failed");
2421
heap_close(fk_rel, RowExclusiveLock);
2424
* In the case we updated the row who's key was equal to the
2425
* default values AND a referencing row in the foreign key
2426
* table exists, we would just have updated it to the same
2427
* values. We need to do another lookup now and in case a
2428
* reference exists, abort the operation. That is already
2429
* implemented in the NO ACTION trigger.
2431
RI_FKey_noaction_upd(fcinfo);
2433
return PointerGetDatum(NULL);
2436
* Handle MATCH PARTIAL set null delete.
2438
case RI_MATCH_TYPE_PARTIAL:
2440
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2441
errmsg("MATCH PARTIAL not yet implemented")));
2442
return PointerGetDatum(NULL);
2448
elog(ERROR, "invalid match_type");
2449
return PointerGetDatum(NULL);
2454
* RI_FKey_keyequal_upd -
2456
* Check if we have a key change on update.
2458
* This is not a real trigger procedure. It is used by the AFTER
2459
* trigger queue manager to detect "triggered data change violation".
2463
RI_FKey_keyequal_upd(TriggerData *trigdata)
2474
* Check for the correct # of call arguments
2476
tgnargs = trigdata->tg_trigger->tgnargs;
2477
tgargs = trigdata->tg_trigger->tgargs;
2479
tgnargs > RI_MAX_ARGUMENTS ||
2482
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2483
errmsg("function \"%s\" called with wrong number of trigger arguments",
2484
"RI_FKey_keyequal_upd")));
2487
* Nothing to do if no column names to compare given
2493
* Get the relation descriptors of the FK and PK tables and the new
2496
* Use minimal locking for fk_rel here.
2498
if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
2500
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
2501
errmsg("no target table given for trigger \"%s\" on table \"%s\"",
2502
trigdata->tg_trigger->tgname,
2503
RelationGetRelationName(trigdata->tg_relation)),
2504
errhint("Remove this referential integrity trigger and its mates, then do ALTER TABLE ADD CONSTRAINT.")));
2506
fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, AccessShareLock);
2507
pk_rel = trigdata->tg_relation;
2508
new_row = trigdata->tg_newtuple;
2509
old_row = trigdata->tg_trigtuple;
2511
switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
2514
* MATCH <UNSPECIFIED>
2516
case RI_MATCH_TYPE_UNSPECIFIED:
2517
case RI_MATCH_TYPE_FULL:
2518
ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2519
RI_PLAN_KEYEQUAL_UPD,
2523
heap_close(fk_rel, AccessShareLock);
2526
* Return if key's are equal
2528
return ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
2532
* Handle MATCH PARTIAL set null delete.
2534
case RI_MATCH_TYPE_PARTIAL:
2536
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2537
errmsg("MATCH PARTIAL not yet implemented")));
2544
elog(ERROR, "invalid match_type");
2550
* RI_Initial_Check -
2552
* Check an entire table for non-matching values using a single query.
2553
* This is not a trigger procedure, but is called during ALTER TABLE
2554
* ADD FOREIGN KEY to validate the initial table contents.
2556
* We expect that an exclusive lock has been taken on rel and pkrel;
2557
* hence, we do not need to lock individual rows for the check.
2559
* If the check fails because the current user doesn't have permissions
2560
* to read both tables, return false to let our caller know that they will
2561
* need to do something else to check the constraint.
2565
RI_Initial_Check(FkConstraint *fkconstraint, Relation rel, Relation pkrel)
2567
const char *constrname = fkconstraint->constr_name;
2568
char querystr[MAX_QUOTED_REL_NAME_LEN * 2 + 250 +
2569
(MAX_QUOTED_NAME_LEN + 32) * ((RI_MAX_NUMKEYS * 4) + 1)];
2570
char pkrelname[MAX_QUOTED_REL_NAME_LEN];
2571
char relname[MAX_QUOTED_REL_NAME_LEN];
2572
char attname[MAX_QUOTED_NAME_LEN];
2573
char fkattname[MAX_QUOTED_NAME_LEN];
2578
char workmembuf[32];
2583
* Check to make sure current user has enough permissions to do the
2584
* test query. (If not, caller can fall back to the trigger method,
2585
* which works because it changes user IDs on the fly.)
2587
* XXX are there any other show-stopper conditions to check?
2589
if (pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_SELECT) != ACLCHECK_OK)
2591
if (pg_class_aclcheck(RelationGetRelid(pkrel), GetUserId(), ACL_SELECT) != ACLCHECK_OK)
2595
* The query string built is:
2596
* SELECT fk.keycols FROM ONLY relname fk
2597
* LEFT OUTER JOIN ONLY pkrelname pk
2598
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
2599
* WHERE pk.pkkeycol1 IS NULL AND
2600
* For MATCH unspecified:
2601
* (fk.keycol1 IS NOT NULL [AND ...])
2603
* (fk.keycol1 IS NOT NULL [OR ...])
2607
sprintf(querystr, "SELECT ");
2609
foreach(l, fkconstraint->fk_attrs)
2611
quoteOneName(attname, strVal(lfirst(l)));
2612
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2613
"%sfk.%s", sep, attname);
2617
quoteRelationName(pkrelname, pkrel);
2618
quoteRelationName(relname, rel);
2619
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2620
" FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON (",
2621
relname, pkrelname);
2624
forboth(l, fkconstraint->pk_attrs, l2, fkconstraint->fk_attrs)
2626
quoteOneName(attname, strVal(lfirst(l)));
2627
quoteOneName(fkattname, strVal(lfirst(l2)));
2628
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2630
sep, attname, fkattname);
2635
* It's sufficient to test any one pk attribute for null to detect a
2638
quoteOneName(attname, strVal(linitial(fkconstraint->pk_attrs)));
2639
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2640
") WHERE pk.%s IS NULL AND (", attname);
2643
foreach(l, fkconstraint->fk_attrs)
2645
quoteOneName(attname, strVal(lfirst(l)));
2646
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2647
"%sfk.%s IS NOT NULL",
2649
switch (fkconstraint->fk_matchtype)
2651
case FKCONSTR_MATCH_UNSPECIFIED:
2654
case FKCONSTR_MATCH_FULL:
2657
case FKCONSTR_MATCH_PARTIAL:
2659
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2660
errmsg("MATCH PARTIAL not yet implemented")));
2663
elog(ERROR, "unrecognized match type: %d",
2664
fkconstraint->fk_matchtype);
2668
snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr),
2672
* Temporarily increase work_mem so that the check query can be
2673
* executed more efficiently. It seems okay to do this because the
2674
* query is simple enough to not use a multiple of work_mem, and one
2675
* typically would not have many large foreign-key validations
2676
* happening concurrently. So this seems to meet the criteria for
2677
* being considered a "maintenance" operation, and accordingly we use
2678
* maintenance_work_mem.
2680
* We do the equivalent of "SET LOCAL work_mem" so that transaction abort
2681
* will restore the old value if we lose control due to an error.
2683
old_work_mem = work_mem;
2684
snprintf(workmembuf, sizeof(workmembuf), "%d", maintenance_work_mem);
2685
(void) set_config_option("work_mem", workmembuf,
2686
PGC_USERSET, PGC_S_SESSION,
2689
if (SPI_connect() != SPI_OK_CONNECT)
2690
elog(ERROR, "SPI_connect failed");
2693
* Generate the plan. We don't need to cache it, and there are no
2694
* arguments to the plan.
2696
qplan = SPI_prepare(querystr, 0, NULL);
2699
elog(ERROR, "SPI_prepare returned %d for %s", SPI_result, querystr);
2702
* Run the plan. For safety we force a current snapshot to be used.
2703
* (In serializable mode, this arguably violates serializability, but we
2704
* really haven't got much choice.) We need at most one tuple returned,
2705
* so pass limit = 1.
2707
spi_result = SPI_execute_snapshot(qplan,
2709
CopySnapshot(GetLatestSnapshot()),
2714
if (spi_result != SPI_OK_SELECT)
2715
elog(ERROR, "SPI_execute_snapshot returned %d", spi_result);
2717
/* Did we find a tuple violating the constraint? */
2718
if (SPI_processed > 0)
2720
HeapTuple tuple = SPI_tuptable->vals[0];
2721
TupleDesc tupdesc = SPI_tuptable->tupdesc;
2722
int nkeys = list_length(fkconstraint->fk_attrs);
2727
* If it's MATCH FULL, and there are any nulls in the FK keys,
2728
* complain about that rather than the lack of a match. MATCH
2729
* FULL disallows partially-null FK rows.
2731
if (fkconstraint->fk_matchtype == FKCONSTR_MATCH_FULL)
2733
bool isnull = false;
2735
for (i = 1; i <= nkeys; i++)
2737
(void) SPI_getbinval(tuple, tupdesc, i, &isnull);
2743
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
2744
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
2745
RelationGetRelationName(rel),
2747
errdetail("MATCH FULL does not allow mixing of null and nonnull key values.")));
2751
* Although we didn't cache the query, we need to set up a fake
2752
* query key to pass to ri_ReportViolation.
2754
MemSet(&qkey, 0, sizeof(qkey));
2755
qkey.constr_queryno = RI_PLAN_CHECK_LOOKUPPK;
2756
qkey.nkeypairs = nkeys;
2757
for (i = 0; i < nkeys; i++)
2758
qkey.keypair[i][RI_KEYPAIR_FK_IDX] = i + 1;
2760
ri_ReportViolation(&qkey, constrname,
2766
if (SPI_finish() != SPI_OK_FINISH)
2767
elog(ERROR, "SPI_finish failed");
2770
* Restore work_mem for the remainder of the current transaction. This
2771
* is another SET LOCAL, so it won't affect the session value, nor any
2772
* tentative value if there is one.
2774
snprintf(workmembuf, sizeof(workmembuf), "%d", old_work_mem);
2775
(void) set_config_option("work_mem", workmembuf,
2776
PGC_USERSET, PGC_S_SESSION,
2784
* Local functions below
2790
* quoteOneName --- safely quote a single SQL name
2792
* buffer must be MAX_QUOTED_NAME_LEN long (includes room for \0)
2795
quoteOneName(char *buffer, const char *name)
2797
/* Rather than trying to be smart, just always quote it. */
2803
*buffer++ = *name++;
2810
* quoteRelationName --- safely quote a fully qualified relation name
2812
* buffer must be MAX_QUOTED_REL_NAME_LEN long (includes room for \0)
2815
quoteRelationName(char *buffer, Relation rel)
2817
quoteOneName(buffer, get_namespace_name(RelationGetNamespace(rel)));
2818
buffer += strlen(buffer);
2820
quoteOneName(buffer, RelationGetRelationName(rel));
2825
* ri_DetermineMatchType -
2827
* Convert the MATCH TYPE string into a switchable int
2831
ri_DetermineMatchType(char *str)
2833
if (strcmp(str, "UNSPECIFIED") == 0)
2834
return RI_MATCH_TYPE_UNSPECIFIED;
2835
if (strcmp(str, "FULL") == 0)
2836
return RI_MATCH_TYPE_FULL;
2837
if (strcmp(str, "PARTIAL") == 0)
2838
return RI_MATCH_TYPE_PARTIAL;
2840
elog(ERROR, "unrecognized referential integrity match type \"%s\"", str);
2846
* ri_BuildQueryKeyFull -
2848
* Build up a new hashtable key for a prepared SPI plan of a
2849
* constraint trigger of MATCH FULL. The key consists of:
2851
* constr_type is FULL
2852
* constr_id is the OID of the pg_trigger row that invoked us
2853
* constr_queryno is an internal number of the query inside the proc
2854
* fk_relid is the OID of referencing relation
2855
* pk_relid is the OID of referenced relation
2856
* nkeypairs is the number of keypairs
2857
* following are the attribute number keypairs of the trigger invocation
2859
* At least for MATCH FULL this builds a unique key per plan.
2863
ri_BuildQueryKeyFull(RI_QueryKey *key, Oid constr_id, int32 constr_queryno,
2864
Relation fk_rel, Relation pk_rel,
2865
int argc, char **argv)
2872
* Initialize the key and fill in type, oid's and number of keypairs
2874
memset((void *) key, 0, sizeof(RI_QueryKey));
2875
key->constr_type = RI_MATCH_TYPE_FULL;
2876
key->constr_id = constr_id;
2877
key->constr_queryno = constr_queryno;
2878
key->fk_relid = fk_rel->rd_id;
2879
key->pk_relid = pk_rel->rd_id;
2880
key->nkeypairs = (argc - RI_FIRST_ATTNAME_ARGNO) / 2;
2883
* Lookup the attribute numbers of the arguments to the trigger call
2884
* and fill in the keypairs.
2886
for (i = 0, j = RI_FIRST_ATTNAME_ARGNO; j < argc; i++, j += 2)
2888
fno = SPI_fnumber(fk_rel->rd_att, argv[j]);
2889
if (fno == SPI_ERROR_NOATTRIBUTE)
2891
(errcode(ERRCODE_UNDEFINED_COLUMN),
2892
errmsg("table \"%s\" does not have column \"%s\" referenced by constraint \"%s\"",
2893
RelationGetRelationName(fk_rel),
2895
argv[RI_CONSTRAINT_NAME_ARGNO])));
2896
key->keypair[i][RI_KEYPAIR_FK_IDX] = fno;
2898
fno = SPI_fnumber(pk_rel->rd_att, argv[j + 1]);
2899
if (fno == SPI_ERROR_NOATTRIBUTE)
2901
(errcode(ERRCODE_UNDEFINED_COLUMN),
2902
errmsg("table \"%s\" does not have column \"%s\" referenced by constraint \"%s\"",
2903
RelationGetRelationName(pk_rel),
2905
argv[RI_CONSTRAINT_NAME_ARGNO])));
2906
key->keypair[i][RI_KEYPAIR_PK_IDX] = fno;
2911
* Check that RI trigger function was called in expected context
2914
ri_CheckTrigger(FunctionCallInfo fcinfo, const char *funcname, int tgkind)
2916
TriggerData *trigdata = (TriggerData *) fcinfo->context;
2919
if (!CALLED_AS_TRIGGER(fcinfo))
2921
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2922
errmsg("function \"%s\" was not called by trigger manager", funcname)));
2925
* Check proper event
2927
if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
2928
!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
2930
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2931
errmsg("function \"%s\" must be fired AFTER ROW", funcname)));
2935
case RI_TRIGTYPE_INSERT:
2936
if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
2938
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2939
errmsg("function \"%s\" must be fired for INSERT", funcname)));
2941
case RI_TRIGTYPE_UPDATE:
2942
if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
2944
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2945
errmsg("function \"%s\" must be fired for UPDATE", funcname)));
2947
case RI_TRIGTYPE_INUP:
2948
if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event) &&
2949
!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
2951
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2952
errmsg("function \"%s\" must be fired for INSERT or UPDATE",
2955
case RI_TRIGTYPE_DELETE:
2956
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
2958
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2959
errmsg("function \"%s\" must be fired for DELETE", funcname)));
2964
* Check for the correct # of call arguments
2966
tgnargs = trigdata->tg_trigger->tgnargs;
2968
tgnargs > RI_MAX_ARGUMENTS ||
2971
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
2972
errmsg("function \"%s\" called with wrong number of trigger arguments",
2976
* Check that tgconstrrelid is known. We need to check here because
2977
* of ancient pg_dump bug; see notes in CreateTrigger().
2979
if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
2981
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
2982
errmsg("no target table given for trigger \"%s\" on table \"%s\"",
2983
trigdata->tg_trigger->tgname,
2984
RelationGetRelationName(trigdata->tg_relation)),
2985
errhint("Remove this referential integrity trigger and its mates, then do ALTER TABLE ADD CONSTRAINT.")));
2990
* Prepare execution plan for a query to enforce an RI restriction
2992
* If cache_plan is true, the plan is saved into our plan hashtable
2993
* so that we don't need to plan it again.
2996
ri_PlanCheck(const char *querystr, int nargs, Oid *argtypes,
2997
RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel,
3005
* The query is always run against the FK table except when this is an
3006
* update/insert trigger on the FK table itself - either
3007
* RI_PLAN_CHECK_LOOKUPPK or RI_PLAN_CHECK_LOOKUPPK_NOCOLS
3009
if (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK ||
3010
qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK_NOCOLS)
3015
/* Switch to proper UID to perform check as */
3016
save_uid = GetUserId();
3017
SetUserId(RelationGetForm(query_rel)->relowner);
3019
/* Create the plan */
3020
qplan = SPI_prepare(querystr, nargs, argtypes);
3023
elog(ERROR, "SPI_prepare returned %d for %s", SPI_result, querystr);
3026
SetUserId(save_uid);
3028
/* Save the plan if requested */
3031
qplan = SPI_saveplan(qplan);
3032
ri_HashPreparedPlan(qkey, qplan);
3039
* Perform a query to enforce an RI restriction
3042
ri_PerformCheck(RI_QueryKey *qkey, void *qplan,
3043
Relation fk_rel, Relation pk_rel,
3044
HeapTuple old_tuple, HeapTuple new_tuple,
3046
int expect_OK, const char *constrname)
3051
Snapshot test_snapshot;
3052
Snapshot crosscheck_snapshot;
3056
Datum vals[RI_MAX_NUMKEYS * 2];
3057
char nulls[RI_MAX_NUMKEYS * 2];
3060
* The query is always run against the FK table except when this is an
3061
* update/insert trigger on the FK table itself - either
3062
* RI_PLAN_CHECK_LOOKUPPK or RI_PLAN_CHECK_LOOKUPPK_NOCOLS
3064
if (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK ||
3065
qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK_NOCOLS)
3071
* The values for the query are taken from the table on which the
3072
* trigger is called - it is normally the other one with respect to
3073
* query_rel. An exception is ri_Check_Pk_Match(), which uses the PK
3074
* table for both (the case when constrname == NULL)
3076
if (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK && constrname != NULL)
3078
source_rel = fk_rel;
3079
key_idx = RI_KEYPAIR_FK_IDX;
3083
source_rel = pk_rel;
3084
key_idx = RI_KEYPAIR_PK_IDX;
3087
/* Extract the parameters to be passed into the query */
3090
ri_ExtractValues(qkey, key_idx, source_rel, new_tuple,
3093
ri_ExtractValues(qkey, key_idx, source_rel, old_tuple,
3094
vals + qkey->nkeypairs, nulls + qkey->nkeypairs);
3098
ri_ExtractValues(qkey, key_idx, source_rel, old_tuple,
3103
* In READ COMMITTED mode, we just need to use an up-to-date regular
3104
* snapshot, and we will see all rows that could be interesting.
3105
* But in SERIALIZABLE mode, we can't change the transaction snapshot.
3106
* If the caller passes detectNewRows == false then it's okay to do the
3107
* query with the transaction snapshot; otherwise we use a current
3108
* snapshot, and tell the executor to error out if it finds any rows under
3109
* the current snapshot that wouldn't be visible per the transaction
3112
if (IsXactIsoLevelSerializable && detectNewRows)
3114
CommandCounterIncrement(); /* be sure all my own work is visible */
3115
test_snapshot = CopySnapshot(GetLatestSnapshot());
3116
crosscheck_snapshot = CopySnapshot(GetTransactionSnapshot());
3120
/* the default SPI behavior is okay */
3121
test_snapshot = InvalidSnapshot;
3122
crosscheck_snapshot = InvalidSnapshot;
3126
* If this is a select query (e.g., for a 'no action' or 'restrict'
3127
* trigger), we only need to see if there is a single row in the
3128
* table, matching the key. Otherwise, limit = 0 - because we want
3129
* the query to affect ALL the matching rows.
3131
limit = (expect_OK == SPI_OK_SELECT) ? 1 : 0;
3133
/* Switch to proper UID to perform check as */
3134
save_uid = GetUserId();
3135
SetUserId(RelationGetForm(query_rel)->relowner);
3137
/* Finally we can run the query. */
3138
spi_result = SPI_execute_snapshot(qplan,
3140
test_snapshot, crosscheck_snapshot,
3144
SetUserId(save_uid);
3148
elog(ERROR, "SPI_execute_snapshot returned %d", spi_result);
3150
if (expect_OK >= 0 && spi_result != expect_OK)
3151
ri_ReportViolation(qkey, constrname ? constrname : "",
3153
new_tuple ? new_tuple : old_tuple,
3157
/* XXX wouldn't it be clearer to do this part at the caller? */
3158
if (constrname && expect_OK == SPI_OK_SELECT &&
3159
(SPI_processed == 0) == (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK))
3160
ri_ReportViolation(qkey, constrname,
3162
new_tuple ? new_tuple : old_tuple,
3166
return SPI_processed != 0;
3170
* Extract fields from a tuple into Datum/nulls arrays
3173
ri_ExtractValues(RI_QueryKey *qkey, int key_idx,
3174
Relation rel, HeapTuple tuple,
3175
Datum *vals, char *nulls)
3180
for (i = 0; i < qkey->nkeypairs; i++)
3182
vals[i] = SPI_getbinval(tuple, rel->rd_att,
3183
qkey->keypair[i][key_idx],
3185
nulls[i] = isnull ? 'n' : ' ';
3190
* Produce an error report
3192
* If the failed constraint was on insert/update to the FK table,
3193
* we want the key names and values extracted from there, and the error
3194
* message to look like 'key blah is not present in PK'.
3195
* Otherwise, the attr names and values come from the PK table and the
3196
* message looks like 'key blah is still referenced from FK'.
3199
ri_ReportViolation(RI_QueryKey *qkey, const char *constrname,
3200
Relation pk_rel, Relation fk_rel,
3201
HeapTuple violator, TupleDesc tupdesc,
3204
#define BUFLENGTH 512
3205
char key_names[BUFLENGTH];
3206
char key_values[BUFLENGTH];
3207
char *name_ptr = key_names;
3208
char *val_ptr = key_values;
3215
(errcode(ERRCODE_INTERNAL_ERROR),
3216
errmsg("referential integrity query on \"%s\" from constraint \"%s\" on \"%s\" gave unexpected result",
3217
RelationGetRelationName(pk_rel),
3219
RelationGetRelationName(fk_rel)),
3220
errhint("This is most likely due to a rule having rewritten the query.")));
3223
* Determine which relation to complain about. If tupdesc wasn't
3224
* passed by caller, assume the violator tuple came from there.
3226
onfk = (qkey->constr_queryno == RI_PLAN_CHECK_LOOKUPPK);
3229
key_idx = RI_KEYPAIR_FK_IDX;
3230
if (tupdesc == NULL)
3231
tupdesc = fk_rel->rd_att;
3235
key_idx = RI_KEYPAIR_PK_IDX;
3236
if (tupdesc == NULL)
3237
tupdesc = pk_rel->rd_att;
3241
* Special case - if there are no keys at all, this is a 'no column'
3242
* constraint - no need to try to extract the values, and the message
3243
* in this case looks different.
3245
if (qkey->nkeypairs == 0)
3248
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
3249
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
3250
RelationGetRelationName(fk_rel), constrname),
3251
errdetail("No rows were found in \"%s\".",
3252
RelationGetRelationName(pk_rel))));
3255
/* Get printable versions of the keys involved */
3256
for (idx = 0; idx < qkey->nkeypairs; idx++)
3258
int fnum = qkey->keypair[idx][key_idx];
3262
name = SPI_fname(tupdesc, fnum);
3263
val = SPI_getvalue(violator, tupdesc, fnum);
3268
* Go to "..." if name or value doesn't fit in buffer. We reserve
3269
* 5 bytes to ensure we can add comma, "...", null.
3271
if (strlen(name) >= (key_names + BUFLENGTH - 5) - name_ptr ||
3272
strlen(val) >= (key_values + BUFLENGTH - 5) - val_ptr)
3274
sprintf(name_ptr, "...");
3275
sprintf(val_ptr, "...");
3279
name_ptr += sprintf(name_ptr, "%s%s", idx > 0 ? "," : "", name);
3280
val_ptr += sprintf(val_ptr, "%s%s", idx > 0 ? "," : "", val);
3285
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
3286
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
3287
RelationGetRelationName(fk_rel), constrname),
3288
errdetail("Key (%s)=(%s) is not present in table \"%s\".",
3289
key_names, key_values,
3290
RelationGetRelationName(pk_rel))));
3293
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
3294
errmsg("update or delete on \"%s\" violates foreign key constraint \"%s\" on \"%s\"",
3295
RelationGetRelationName(pk_rel),
3296
constrname, RelationGetRelationName(fk_rel)),
3297
errdetail("Key (%s)=(%s) is still referenced from table \"%s\".",
3298
key_names, key_values,
3299
RelationGetRelationName(fk_rel))));
3303
* ri_BuildQueryKeyPkCheck -
3305
* Build up a new hashtable key for a prepared SPI plan of a
3306
* check for PK rows in noaction triggers.
3308
* constr_type is FULL
3309
* constr_id is the OID of the pg_trigger row that invoked us
3310
* constr_queryno is an internal number of the query inside the proc
3311
* pk_relid is the OID of referenced relation
3312
* nkeypairs is the number of keypairs
3313
* following are the attribute number keypairs of the trigger invocation
3315
* At least for MATCH FULL this builds a unique key per plan.
3319
ri_BuildQueryKeyPkCheck(RI_QueryKey *key, Oid constr_id, int32 constr_queryno,
3321
int argc, char **argv)
3328
* Initialize the key and fill in type, oid's and number of keypairs
3330
memset((void *) key, 0, sizeof(RI_QueryKey));
3331
key->constr_type = RI_MATCH_TYPE_FULL;
3332
key->constr_id = constr_id;
3333
key->constr_queryno = constr_queryno;
3335
key->pk_relid = pk_rel->rd_id;
3336
key->nkeypairs = (argc - RI_FIRST_ATTNAME_ARGNO) / 2;
3339
* Lookup the attribute numbers of the arguments to the trigger call
3340
* and fill in the keypairs.
3342
for (i = 0, j = RI_FIRST_ATTNAME_ARGNO + RI_KEYPAIR_PK_IDX; j < argc; i++, j += 2)
3344
fno = SPI_fnumber(pk_rel->rd_att, argv[j]);
3345
if (fno == SPI_ERROR_NOATTRIBUTE)
3347
(errcode(ERRCODE_UNDEFINED_COLUMN),
3348
errmsg("table \"%s\" does not have column \"%s\" referenced by constraint \"%s\"",
3349
RelationGetRelationName(pk_rel),
3351
argv[RI_CONSTRAINT_NAME_ARGNO])));
3352
key->keypair[i][RI_KEYPAIR_PK_IDX] = fno;
3353
key->keypair[i][RI_KEYPAIR_FK_IDX] = 0;
3361
* Determine the NULL state of all key values in a tuple
3363
* Returns one of RI_KEYS_ALL_NULL, RI_KEYS_NONE_NULL or RI_KEYS_SOME_NULL.
3367
ri_NullCheck(Relation rel, HeapTuple tup, RI_QueryKey *key, int pairidx)
3371
bool allnull = true;
3372
bool nonenull = true;
3374
for (i = 0; i < key->nkeypairs; i++)
3377
SPI_getbinval(tup, rel->rd_att, key->keypair[i][pairidx], &isnull);
3385
return RI_KEYS_ALL_NULL;
3388
return RI_KEYS_NONE_NULL;
3390
return RI_KEYS_SOME_NULL;
3395
* ri_InitHashTables -
3397
* Initialize our internal hash table for prepared
3402
ri_InitHashTables(void)
3406
memset(&ctl, 0, sizeof(ctl));
3407
ctl.keysize = sizeof(RI_QueryKey);
3408
ctl.entrysize = sizeof(RI_QueryHashEntry);
3409
ctl.hash = tag_hash;
3410
ri_query_cache = hash_create("RI query cache", RI_INIT_QUERYHASHSIZE,
3411
&ctl, HASH_ELEM | HASH_FUNCTION);
3416
* ri_FetchPreparedPlan -
3418
* Lookup for a query key in our private hash table of prepared
3419
* and saved SPI execution plans. Return the plan if found or NULL.
3423
ri_FetchPreparedPlan(RI_QueryKey *key)
3425
RI_QueryHashEntry *entry;
3428
* On the first call initialize the hashtable
3430
if (!ri_query_cache)
3431
ri_InitHashTables();
3434
* Lookup for the key
3436
entry = (RI_QueryHashEntry *) hash_search(ri_query_cache,
3446
* ri_HashPreparedPlan -
3448
* Add another plan to our private SPI query plan hashtable.
3452
ri_HashPreparedPlan(RI_QueryKey *key, void *plan)
3454
RI_QueryHashEntry *entry;
3458
* On the first call initialize the hashtable
3460
if (!ri_query_cache)
3461
ri_InitHashTables();
3466
entry = (RI_QueryHashEntry *) hash_search(ri_query_cache,
3468
HASH_ENTER, &found);
3471
(errcode(ERRCODE_OUT_OF_MEMORY),
3472
errmsg("out of memory")));
3480
* Check if all key values in OLD and NEW are equal.
3484
ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
3485
RI_QueryKey *key, int pairidx)
3493
for (i = 0; i < key->nkeypairs; i++)
3496
* Get one attributes oldvalue. If it is NULL - they're not equal.
3498
oldvalue = SPI_getbinval(oldtup, rel->rd_att,
3499
key->keypair[i][pairidx], &isnull);
3504
* Get one attributes oldvalue. If it is NULL - they're not equal.
3506
newvalue = SPI_getbinval(newtup, rel->rd_att,
3507
key->keypair[i][pairidx], &isnull);
3512
* Get the attributes type OID and call the '=' operator to
3513
* compare the values.
3515
typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]);
3516
if (!ri_AttributesEqual(typeid, oldvalue, newvalue))
3525
* ri_AllKeysUnequal -
3527
* Check if all key values in OLD and NEW are not equal.
3531
ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
3532
RI_QueryKey *key, int pairidx)
3541
keys_unequal = true;
3542
for (i = 0; keys_unequal && i < key->nkeypairs; i++)
3545
* Get one attributes oldvalue. If it is NULL - they're not equal.
3547
oldvalue = SPI_getbinval(oldtup, rel->rd_att,
3548
key->keypair[i][pairidx], &isnull);
3553
* Get one attributes oldvalue. If it is NULL - they're not equal.
3555
newvalue = SPI_getbinval(newtup, rel->rd_att,
3556
key->keypair[i][pairidx], &isnull);
3561
* Get the attributes type OID and call the '=' operator to
3562
* compare the values.
3564
typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]);
3565
if (!ri_AttributesEqual(typeid, oldvalue, newvalue))
3567
keys_unequal = false;
3570
return keys_unequal;
3577
* Check if one key value in OLD and NEW is equal.
3579
* ri_KeysEqual could call this but would run a bit slower. For
3580
* now, let's duplicate the code.
3584
ri_OneKeyEqual(Relation rel, int column, HeapTuple oldtup, HeapTuple newtup,
3585
RI_QueryKey *key, int pairidx)
3593
* Get one attributes oldvalue. If it is NULL - they're not equal.
3595
oldvalue = SPI_getbinval(oldtup, rel->rd_att,
3596
key->keypair[column][pairidx], &isnull);
3601
* Get one attributes oldvalue. If it is NULL - they're not equal.
3603
newvalue = SPI_getbinval(newtup, rel->rd_att,
3604
key->keypair[column][pairidx], &isnull);
3609
* Get the attributes type OID and call the '=' operator to compare
3612
typeid = SPI_gettypeid(rel->rd_att, key->keypair[column][pairidx]);
3613
if (!ri_AttributesEqual(typeid, oldvalue, newvalue))
3621
* ri_AttributesEqual -
3623
* Call the type specific '=' operator comparison function
3626
* NB: we have already checked that neither value is null.
3630
ri_AttributesEqual(Oid typeid, Datum oldvalue, Datum newvalue)
3632
TypeCacheEntry *typentry;
3635
* Find the data type in the typcache, and ask for eq_opr info.
3637
typentry = lookup_type_cache(typeid, TYPECACHE_EQ_OPR_FINFO);
3639
if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
3641
(errcode(ERRCODE_UNDEFINED_FUNCTION),
3642
errmsg("could not identify an equality operator for type %s",
3643
format_type_be(typeid))));
3646
* Call the type specific '=' function
3648
return DatumGetBool(FunctionCall2(&(typentry->eq_opr_finfo),
3649
oldvalue, newvalue));
added
removed
src/backend/utils/adt/ri_triggers.c
