1/*
2 * contrib/spi/refint.c
3 *
4 *
5 * refint.c -- set of functions to define referential integrity
6 * constraints using general triggers.
7 */
8#include "postgres.h"
9
10#include <ctype.h>
11
12#include "commands/trigger.h"
13#include "executor/spi.h"
14#include "utils/builtins.h"
15#include "utils/rel.h"
16
17PG_MODULE_MAGIC;
18
19typedef struct
20{
21 char *ident;
22 int nplans;
23 SPIPlanPtr *splan;
24} EPlan;
25
26static EPlan *FPlans = NULL;
27static int nFPlans = 0;
28static EPlan *PPlans = NULL;
29static int nPPlans = 0;
30
31static EPlan *find_plan(char *ident, EPlan **eplan, int *nplans);
32
33/*
34 * check_primary_key () -- check that key in tuple being inserted/updated
35 * references existing tuple in "primary" table.
36 * Though it's called without args You have to specify referenced
37 * table/keys while creating trigger: key field names in triggered table,
38 * referenced table name, referenced key field names:
39 * EXECUTE PROCEDURE
40 * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
41 */
42
43PG_FUNCTION_INFO_V1(check_primary_key);
44
45Datum
46check_primary_key(PG_FUNCTION_ARGS)
47{
48 TriggerData *trigdata = (TriggerData *) fcinfo->context;
49 Trigger *trigger; /* to get trigger name */
50 int nargs; /* # of args specified in CREATE TRIGGER */
51 char **args; /* arguments: column names and table name */
52 int nkeys; /* # of key columns (= nargs / 2) */
53 Datum *kvals; /* key values */
54 char *relname; /* referenced relation name */
55 Relation rel; /* triggered relation */
56 HeapTuple tuple = NULL; /* tuple to return */
57 TupleDesc tupdesc; /* tuple description */
58 EPlan *plan; /* prepared plan */
59 Oid *argtypes = NULL; /* key types to prepare execution plan */
60 bool isnull; /* to know is some column NULL or not */
61 char ident[2 * NAMEDATALEN]; /* to identify myself */
62 int ret;
63 int i;
64
65#ifdef DEBUG_QUERY
66 elog(DEBUG4, "check_primary_key: Enter Function");
67#endif
68
69 /*
70 * Some checks first...
71 */
72
73 /* Called by trigger manager ? */
74 if (!CALLED_AS_TRIGGER(fcinfo))
75 /* internal error */
76 elog(ERROR, "check_primary_key: not fired by trigger manager");
77
78 /* Should be called for ROW trigger */
79 if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
80 /* internal error */
81 elog(ERROR, "check_primary_key: must be fired for row");
82
83 /* If INSERTion then must check Tuple to being inserted */
84 if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
85 tuple = trigdata->tg_trigtuple;
86
87 /* Not should be called for DELETE */
88 else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
89 /* internal error */
90 elog(ERROR, "check_primary_key: cannot process DELETE events");
91
92 /* If UPDATE, then must check new Tuple, not old one */
93 else
94 tuple = trigdata->tg_newtuple;
95
96 trigger = trigdata->tg_trigger;
97 nargs = trigger->tgnargs;
98 args = trigger->tgargs;
99
100 if (nargs % 2 != 1) /* odd number of arguments! */
101 /* internal error */
102 elog(ERROR, "check_primary_key: odd number of arguments should be specified");
103
104 nkeys = nargs / 2;
105 relname = args[nkeys];
106 rel = trigdata->tg_relation;
107 tupdesc = rel->rd_att;
108
109 /* Connect to SPI manager */
110 if ((ret = SPI_connect()) < 0)
111 /* internal error */
112 elog(ERROR, "check_primary_key: SPI_connect returned %d", ret);
113
114 /*
115 * We use SPI plan preparation feature, so allocate space to place key
116 * values.
117 */
118 kvals = (Datum *) palloc(nkeys * sizeof(Datum));
119
120 /*
121 * Construct ident string as TriggerName $ TriggeredRelationId and try to
122 * find prepared execution plan.
123 */
124 snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
125 plan = find_plan(ident, &PPlans, &nPPlans);
126
127 /* if there is no plan then allocate argtypes for preparation */
128 if (plan->nplans <= 0)
129 argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
130
131 /* For each column in key ... */
132 for (i = 0; i < nkeys; i++)
133 {
134 /* get index of column in tuple */
135 int fnumber = SPI_fnumber(tupdesc, args[i]);
136
137 /* Bad guys may give us un-existing column in CREATE TRIGGER */
138 if (fnumber <= 0)
139 ereport(ERROR,
140 (errcode(ERRCODE_UNDEFINED_COLUMN),
141 errmsg("there is no attribute \"%s\" in relation \"%s\"",
142 args[i], SPI_getrelname(rel))));
143
144 /* Well, get binary (in internal format) value of column */
145 kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
146
147 /*
148 * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
149 * DON'T FORGET return tuple! Executor inserts tuple you're returning!
150 * If you return NULL then nothing will be inserted!
151 */
152 if (isnull)
153 {
154 SPI_finish();
155 return PointerGetDatum(tuple);
156 }
157
158 if (plan->nplans <= 0) /* Get typeId of column */
159 argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
160 }
161
162 /*
163 * If we have to prepare plan ...
164 */
165 if (plan->nplans <= 0)
166 {
167 SPIPlanPtr pplan;
168 char sql[8192];
169
170 /*
171 * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 =
172 * $1 [AND Pkey2 = $2 [...]]
173 */
174 snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
175 for (i = 0; i < nkeys; i++)
176 {
177 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
178 args[i + nkeys + 1], i + 1, (i < nkeys - 1) ? "and " : "");
179 }
180
181 /* Prepare plan for query */
182 pplan = SPI_prepare(sql, nkeys, argtypes);
183 if (pplan == NULL)
184 /* internal error */
185 elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
186
187 /*
188 * Remember that SPI_prepare places plan in current memory context -
189 * so, we have to save plan in Top memory context for later use.
190 */
191 if (SPI_keepplan(pplan))
192 /* internal error */
193 elog(ERROR, "check_primary_key: SPI_keepplan failed");
194 plan->splan = (SPIPlanPtr *) malloc(sizeof(SPIPlanPtr));
195 *(plan->splan) = pplan;
196 plan->nplans = 1;
197 }
198
199 /*
200 * Ok, execute prepared plan.
201 */
202 ret = SPI_execp(*(plan->splan), kvals, NULL, 1);
203 /* we have no NULLs - so we pass ^^^^ here */
204
205 if (ret < 0)
206 /* internal error */
207 elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
208
209 /*
210 * If there are no tuples returned by SELECT then ...
211 */
212 if (SPI_processed == 0)
213 ereport(ERROR,
214 (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
215 errmsg("tuple references non-existent key"),
216 errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
217
218 SPI_finish();
219
220 return PointerGetDatum(tuple);
221}
222
223/*
224 * check_foreign_key () -- check that key in tuple being deleted/updated
225 * is not referenced by tuples in "foreign" table(s).
226 * Though it's called without args You have to specify (while creating trigger):
227 * number of references, action to do if key referenced
228 * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
229 * ("primary") table and referencing table(s)/keys:
230 * EXECUTE PROCEDURE
231 * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
232 * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
233 */
234
235PG_FUNCTION_INFO_V1(check_foreign_key);
236
237Datum
238check_foreign_key(PG_FUNCTION_ARGS)
239{
240 TriggerData *trigdata = (TriggerData *) fcinfo->context;
241 Trigger *trigger; /* to get trigger name */
242 int nargs; /* # of args specified in CREATE TRIGGER */
243 char **args; /* arguments: as described above */
244 char **args_temp;
245 int nrefs; /* number of references (== # of plans) */
246 char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
247 int nkeys; /* # of key columns */
248 Datum *kvals; /* key values */
249 char *relname; /* referencing relation name */
250 Relation rel; /* triggered relation */
251 HeapTuple trigtuple = NULL; /* tuple to being changed */
252 HeapTuple newtuple = NULL; /* tuple to return */
253 TupleDesc tupdesc; /* tuple description */
254 EPlan *plan; /* prepared plan(s) */
255 Oid *argtypes = NULL; /* key types to prepare execution plan */
256 bool isnull; /* to know is some column NULL or not */
257 bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
258 char ident[2 * NAMEDATALEN]; /* to identify myself */
259 int is_update = 0;
260 int ret;
261 int i,
262 r;
263
264#ifdef DEBUG_QUERY
265 elog(DEBUG4, "check_foreign_key: Enter Function");
266#endif
267
268 /*
269 * Some checks first...
270 */
271
272 /* Called by trigger manager ? */
273 if (!CALLED_AS_TRIGGER(fcinfo))
274 /* internal error */
275 elog(ERROR, "check_foreign_key: not fired by trigger manager");
276
277 /* Should be called for ROW trigger */
278 if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
279 /* internal error */
280 elog(ERROR, "check_foreign_key: must be fired for row");
281
282 /* Not should be called for INSERT */
283 if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
284 /* internal error */
285 elog(ERROR, "check_foreign_key: cannot process INSERT events");
286
287 /* Have to check tg_trigtuple - tuple being deleted */
288 trigtuple = trigdata->tg_trigtuple;
289
290 /*
291 * But if this is UPDATE then we have to return tg_newtuple. Also, if key
292 * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
293 */
294 is_update = 0;
295 if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
296 {
297 newtuple = trigdata->tg_newtuple;
298 is_update = 1;
299 }
300 trigger = trigdata->tg_trigger;
301 nargs = trigger->tgnargs;
302 args = trigger->tgargs;
303
304 if (nargs < 5) /* nrefs, action, key, Relation, key - at
305 * least */
306 /* internal error */
307 elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
308
309 nrefs = pg_strtoint32(args[0]);
310 if (nrefs < 1)
311 /* internal error */
312 elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
313 action = tolower((unsigned char) *(args[1]));
314 if (action != 'r' && action != 'c' && action != 's')
315 /* internal error */
316 elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
317 nargs -= 2;
318 args += 2;
319 nkeys = (nargs - nrefs) / (nrefs + 1);
320 if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
321 /* internal error */
322 elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
323 nargs + 2, nrefs);
324
325 rel = trigdata->tg_relation;
326 tupdesc = rel->rd_att;
327
328 /* Connect to SPI manager */
329 if ((ret = SPI_connect()) < 0)
330 /* internal error */
331 elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret);
332
333 /*
334 * We use SPI plan preparation feature, so allocate space to place key
335 * values.
336 */
337 kvals = (Datum *) palloc(nkeys * sizeof(Datum));
338
339 /*
340 * Construct ident string as TriggerName $ TriggeredRelationId and try to
341 * find prepared execution plan(s).
342 */
343 snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
344 plan = find_plan(ident, &FPlans, &nFPlans);
345
346 /* if there is no plan(s) then allocate argtypes for preparation */
347 if (plan->nplans <= 0)
348 argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
349
350 /*
351 * else - check that we have exactly nrefs plan(s) ready
352 */
353 else if (plan->nplans != nrefs)
354 /* internal error */
355 elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime",
356 trigger->tgname);
357
358 /* For each column in key ... */
359 for (i = 0; i < nkeys; i++)
360 {
361 /* get index of column in tuple */
362 int fnumber = SPI_fnumber(tupdesc, args[i]);
363
364 /* Bad guys may give us un-existing column in CREATE TRIGGER */
365 if (fnumber <= 0)
366 ereport(ERROR,
367 (errcode(ERRCODE_UNDEFINED_COLUMN),
368 errmsg("there is no attribute \"%s\" in relation \"%s\"",
369 args[i], SPI_getrelname(rel))));
370
371 /* Well, get binary (in internal format) value of column */
372 kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
373
374 /*
375 * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
376 * DON'T FORGET return tuple! Executor inserts tuple you're returning!
377 * If you return NULL then nothing will be inserted!
378 */
379 if (isnull)
380 {
381 SPI_finish();
382 return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
383 }
384
385 /*
386 * If UPDATE then get column value from new tuple being inserted and
387 * compare is this the same as old one. For the moment we use string
388 * presentation of values...
389 */
390 if (newtuple != NULL)
391 {
392 char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
393 char *newval;
394
395 /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
396 if (oldval == NULL)
397 /* internal error */
398 elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result));
399 newval = SPI_getvalue(newtuple, tupdesc, fnumber);
400 if (newval == NULL || strcmp(oldval, newval) != 0)
401 isequal = false;
402 }
403
404 if (plan->nplans <= 0) /* Get typeId of column */
405 argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
406 }
407 args_temp = args;
408 nargs -= nkeys;
409 args += nkeys;
410
411 /*
412 * If we have to prepare plans ...
413 */
414 if (plan->nplans <= 0)
415 {
416 SPIPlanPtr pplan;
417 char sql[8192];
418 char **args2 = args;
419
420 plan->splan = (SPIPlanPtr *) malloc(nrefs * sizeof(SPIPlanPtr));
421
422 for (r = 0; r < nrefs; r++)
423 {
424 relname = args2[0];
425
426 /*---------
427 * For 'R'estrict action we construct SELECT query:
428 *
429 * SELECT 1
430 * FROM _referencing_relation_
431 * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
432 *
433 * to check is tuple referenced or not.
434 *---------
435 */
436 if (action == 'r')
437
438 snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
439
440 /*---------
441 * For 'C'ascade action we construct DELETE query
442 *
443 * DELETE
444 * FROM _referencing_relation_
445 * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
446 *
447 * to delete all referencing tuples.
448 *---------
449 */
450
451 /*
452 * Max : Cascade with UPDATE query i create update query that
453 * updates new key values in referenced tables
454 */
455
456
457 else if (action == 'c')
458 {
459 if (is_update == 1)
460 {
461 int fn;
462 char *nv;
463 int k;
464
465 snprintf(sql, sizeof(sql), "update %s set ", relname);
466 for (k = 1; k <= nkeys; k++)
467 {
468 int is_char_type = 0;
469 char *type;
470
471 fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
472 Assert(fn > 0); /* already checked above */
473 nv = SPI_getvalue(newtuple, tupdesc, fn);
474 type = SPI_gettype(tupdesc, fn);
475
476 if (strcmp(type, "text") == 0 ||
477 strcmp(type, "varchar") == 0 ||
478 strcmp(type, "char") == 0 ||
479 strcmp(type, "bpchar") == 0 ||
480 strcmp(type, "date") == 0 ||
481 strcmp(type, "timestamp") == 0)
482 is_char_type = 1;
483#ifdef DEBUG_QUERY
484 elog(DEBUG4, "check_foreign_key Debug value %s type %s %d",
485 nv, type, is_char_type);
486#endif
487
488 /*
489 * is_char_type =1 i set ' ' for define a new value
490 */
491 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
492 " %s = %s%s%s %s ",
493 args2[k], (is_char_type > 0) ? "'" : "",
494 nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : "");
495 }
496 strcat(sql, " where ");
497
498 }
499 else
500 /* DELETE */
501 snprintf(sql, sizeof(sql), "delete from %s where ", relname);
502
503 }
504
505 /*
506 * For 'S'etnull action we construct UPDATE query - UPDATE
507 * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]]
508 * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in
509 * all referencing tuples to NULL.
510 */
511 else if (action == 's')
512 {
513 snprintf(sql, sizeof(sql), "update %s set ", relname);
514 for (i = 1; i <= nkeys; i++)
515 {
516 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
517 "%s = null%s",
518 args2[i], (i < nkeys) ? ", " : "");
519 }
520 strcat(sql, " where ");
521 }
522
523 /* Construct WHERE qual */
524 for (i = 1; i <= nkeys; i++)
525 {
526 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
527 args2[i], i, (i < nkeys) ? "and " : "");
528 }
529
530 /* Prepare plan for query */
531 pplan = SPI_prepare(sql, nkeys, argtypes);
532 if (pplan == NULL)
533 /* internal error */
534 elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
535
536 /*
537 * Remember that SPI_prepare places plan in current memory context
538 * - so, we have to save plan in Top memory context for later use.
539 */
540 if (SPI_keepplan(pplan))
541 /* internal error */
542 elog(ERROR, "check_foreign_key: SPI_keepplan failed");
543
544 plan->splan[r] = pplan;
545
546 args2 += nkeys + 1; /* to the next relation */
547 }
548 plan->nplans = nrefs;
549#ifdef DEBUG_QUERY
550 elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql);
551#endif
552 }
553
554 /*
555 * If UPDATE and key is not changed ...
556 */
557 if (newtuple != NULL && isequal)
558 {
559 SPI_finish();
560 return PointerGetDatum(newtuple);
561 }
562
563 /*
564 * Ok, execute prepared plan(s).
565 */
566 for (r = 0; r < nrefs; r++)
567 {
568 /*
569 * For 'R'estrict we may to execute plan for one tuple only, for other
570 * actions - for all tuples.
571 */
572 int tcount = (action == 'r') ? 1 : 0;
573
574 relname = args[0];
575
576 snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
577 plan = find_plan(ident, &FPlans, &nFPlans);
578 ret = SPI_execp(plan->splan[r], kvals, NULL, tcount);
579 /* we have no NULLs - so we pass ^^^^ here */
580
581 if (ret < 0)
582 ereport(ERROR,
583 (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
584 errmsg("SPI_execp returned %d", ret)));
585
586 /* If action is 'R'estrict ... */
587 if (action == 'r')
588 {
589 /* If there is tuple returned by SELECT then ... */
590 if (SPI_processed > 0)
591 ereport(ERROR,
592 (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
593 errmsg("\"%s\": tuple is referenced in \"%s\"",
594 trigger->tgname, relname)));
595 }
596 else
597 {
598#ifdef REFINT_VERBOSE
599 elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
600 trigger->tgname, SPI_processed, relname,
601 (action == 'c') ? "deleted" : "set to null");
602#endif
603 }
604 args += nkeys + 1; /* to the next relation */
605 }
606
607 SPI_finish();
608
609 return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
610}
611
612static EPlan *
613find_plan(char *ident, EPlan **eplan, int *nplans)
614{
615 EPlan *newp;
616 int i;
617
618 if (*nplans > 0)
619 {
620 for (i = 0; i < *nplans; i++)
621 {
622 if (strcmp((*eplan)[i].ident, ident) == 0)
623 break;
624 }
625 if (i != *nplans)
626 return (*eplan + i);
627 *eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
628 newp = *eplan + i;
629 }
630 else
631 {
632 newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
633 (*nplans) = i = 0;
634 }
635
636 newp->ident = strdup(ident);
637 newp->nplans = 0;
638 newp->splan = NULL;
639 (*nplans)++;
640
641 return newp;
642}
643