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