1 /*
2 * contrib/spi/timetravel.c
3 *
4 *
5 * timetravel.c -- function to get time travel feature
6 * using general triggers.
7 *
8 * Modified by BÖJTHE Zoltán, Hungary, mailto:urdesobt@axelero.hu
9 */
10 #include "postgres.h"
11
12 #include <ctype.h>
13
14 #include "access/htup_details.h"
15 #include "catalog/pg_type.h"
16 #include "commands/trigger.h"
17 #include "executor/spi.h"
18 #include "miscadmin.h"
19 #include "utils/builtins.h"
main(int,char **)20 #include "utils/nabstime.h"
21 #include "utils/rel.h"
22
23 PG_MODULE_MAGIC;
24
25 /* AbsoluteTime currabstime(void); */
26
27 typedef struct
28 {
29 char *ident;
30 SPIPlanPtr splan;
31 } EPlan;
32
33 static EPlan *Plans = NULL; /* for UPDATE/DELETE */
34 static int nPlans = 0;
35
36 typedef struct _TTOffList
37 {
38 struct _TTOffList *next;
39 char name[FLEXIBLE_ARRAY_MEMBER];
40 } TTOffList;
41
42 static TTOffList *TTOff = NULL;
43
44 static int findTTStatus(char *name);
45 static EPlan *find_plan(char *ident, EPlan **eplan, int *nplans);
46
47 /*
48 * timetravel () --
49 * 1. IF an update affects tuple with stop_date eq INFINITY
50 * then form (and return) new tuple with start_date eq current date
51 * and stop_date eq INFINITY [ and update_user eq current user ]
52 * and all other column values as in new tuple, and insert tuple
53 * with old data and stop_date eq current date
54 * ELSE - skip updating of tuple.
55 * 2. IF a delete affects tuple with stop_date eq INFINITY
56 * then insert the same tuple with stop_date eq current date
57 * [ and delete_user eq current user ]
58 * ELSE - skip deletion of tuple.
59 * 3. On INSERT, if start_date is NULL then current date will be
60 * inserted, if stop_date is NULL then INFINITY will be inserted.
61 * [ and insert_user eq current user, update_user and delete_user
62 * eq NULL ]
63 *
64 * In CREATE TRIGGER you are to specify start_date and stop_date column
65 * names:
66 * EXECUTE PROCEDURE
67 * timetravel ('date_on', 'date_off' [,'insert_user', 'update_user', 'delete_user' ] ).
68 */
69
70 #define MaxAttrNum 5
71 #define MinAttrNum 2
72
73 #define a_time_on 0
74 #define a_time_off 1
75 #define a_ins_user 2
76 #define a_upd_user 3
77 #define a_del_user 4
78
79 PG_FUNCTION_INFO_V1(timetravel);
80
81 Datum /* have to return HeapTuple to Executor */
82 timetravel(PG_FUNCTION_ARGS)
83 {
84 TriggerData *trigdata = (TriggerData *) fcinfo->context;
85 Trigger *trigger; /* to get trigger name */
86 int argc;
87 char **args; /* arguments */
88 int attnum[MaxAttrNum]; /* fnumbers of start/stop columns */
89 Datum oldtimeon,
90 oldtimeoff;
91 Datum newtimeon,
92 newtimeoff,
93 newuser,
94 nulltext;
95 Datum *cvals; /* column values */
96 char *cnulls; /* column nulls */
97 char *relname; /* triggered relation name */
98 Relation rel; /* triggered relation */
99 HeapTuple trigtuple;
100 HeapTuple newtuple = NULL;
101 HeapTuple rettuple;
102 TupleDesc tupdesc; /* tuple description */
103 int natts; /* # of attributes */
104 EPlan *plan; /* prepared plan */
105 char ident[2 * NAMEDATALEN];
106 bool isnull; /* to know is some column NULL or not */
107 bool isinsert = false;
108 int ret;
109 int i;
110
111 /*
112 * Some checks first...
113 */
114
115 /* Called by trigger manager ? */
116 if (!CALLED_AS_TRIGGER(fcinfo))
117 elog(ERROR, "timetravel: not fired by trigger manager");
118
119 /* Should be called for ROW trigger */
120 if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
121 elog(ERROR, "timetravel: must be fired for row");
122
123 /* Should be called BEFORE */
124 if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
125 elog(ERROR, "timetravel: must be fired before event");
126
127 /* INSERT ? */
128 if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
129 isinsert = true;
130
131 if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
132 newtuple = trigdata->tg_newtuple;
133
134 trigtuple = trigdata->tg_trigtuple;
135
136 rel = trigdata->tg_relation;
137 relname = SPI_getrelname(rel);
138
139 /* check if TT is OFF for this relation */
140 if (0 == findTTStatus(relname))
141 {
142 /* OFF - nothing to do */
143 pfree(relname);
144 return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
145 }
146
147 trigger = trigdata->tg_trigger;
148
149 argc = trigger->tgnargs;
150 if (argc != MinAttrNum && argc != MaxAttrNum)
151 elog(ERROR, "timetravel (%s): invalid (!= %d or %d) number of arguments %d",
152 relname, MinAttrNum, MaxAttrNum, trigger->tgnargs);
153
154 args = trigger->tgargs;
155 tupdesc = rel->rd_att;
156 natts = tupdesc->natts;
157
158 for (i = 0; i < MinAttrNum; i++)
159 {
160 attnum[i] = SPI_fnumber(tupdesc, args[i]);
161 if (attnum[i] <= 0)
162 elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
163 if (SPI_gettypeid(tupdesc, attnum[i]) != ABSTIMEOID)
164 elog(ERROR, "timetravel (%s): attribute %s must be of abstime type",
165 relname, args[i]);
166 }
167 for (; i < argc; i++)
168 {
169 attnum[i] = SPI_fnumber(tupdesc, args[i]);
170 if (attnum[i] <= 0)
171 elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
172 if (SPI_gettypeid(tupdesc, attnum[i]) != TEXTOID)
173 elog(ERROR, "timetravel (%s): attribute %s must be of text type",
174 relname, args[i]);
175 }
176
177 /* create fields containing name */
178 newuser = CStringGetTextDatum(GetUserNameFromId(GetUserId(), false));
179
180 nulltext = (Datum) NULL;
181
182 if (isinsert)
183 { /* INSERT */
184 int chnattrs = 0;
185 int chattrs[MaxAttrNum];
186 Datum newvals[MaxAttrNum];
187 bool newnulls[MaxAttrNum];
188
189 oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
190 if (isnull)
191 {
192 newvals[chnattrs] = GetCurrentAbsoluteTime();
193 newnulls[chnattrs] = false;
194 chattrs[chnattrs] = attnum[a_time_on];
195 chnattrs++;
196 }
197
198 oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
199 if (isnull)
200 {
201 if ((chnattrs == 0 && DatumGetInt32(oldtimeon) >= NOEND_ABSTIME) ||
202 (chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) >= NOEND_ABSTIME))
203 elog(ERROR, "timetravel (%s): %s is infinity", relname, args[a_time_on]);
204 newvals[chnattrs] = NOEND_ABSTIME;
205 newnulls[chnattrs] = false;
206 chattrs[chnattrs] = attnum[a_time_off];
207 chnattrs++;
208 }
209 else
210 {
211 if ((chnattrs == 0 && DatumGetInt32(oldtimeon) > DatumGetInt32(oldtimeoff)) ||
212 (chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) > DatumGetInt32(oldtimeoff)))
213 elog(ERROR, "timetravel (%s): %s gt %s", relname, args[a_time_on], args[a_time_off]);
214 }
215
216 pfree(relname);
217 if (chnattrs <= 0)
218 return PointerGetDatum(trigtuple);
219
220 if (argc == MaxAttrNum)
221 {
222 /* clear update_user value */
223 newvals[chnattrs] = nulltext;
224 newnulls[chnattrs] = true;
225 chattrs[chnattrs] = attnum[a_upd_user];
226 chnattrs++;
227 /* clear delete_user value */
228 newvals[chnattrs] = nulltext;
229 newnulls[chnattrs] = true;
230 chattrs[chnattrs] = attnum[a_del_user];
231 chnattrs++;
232 /* set insert_user value */
233 newvals[chnattrs] = newuser;
234 newnulls[chnattrs] = false;
235 chattrs[chnattrs] = attnum[a_ins_user];
236 chnattrs++;
237 }
238 rettuple = heap_modify_tuple_by_cols(trigtuple, tupdesc,
239 chnattrs, chattrs,
240 newvals, newnulls);
241 return PointerGetDatum(rettuple);
242 /* end of INSERT */
243 }
244
245 /* UPDATE/DELETE: */
246 oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
247 if (isnull)
248 elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
249
250 oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
251 if (isnull)
252 elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
253
254 /*
255 * If DELETE/UPDATE of tuple with stop_date neq INFINITY then say upper
256 * Executor to skip operation for this tuple
257 */
258 if (newtuple != NULL)
259 { /* UPDATE */
260 newtimeon = SPI_getbinval(newtuple, tupdesc, attnum[a_time_on], &isnull);
261 if (isnull)
262 elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
263
264 newtimeoff = SPI_getbinval(newtuple, tupdesc, attnum[a_time_off], &isnull);
265 if (isnull)
266 elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
267
268 if (oldtimeon != newtimeon || oldtimeoff != newtimeoff)
269 elog(ERROR, "timetravel (%s): you cannot change %s and/or %s columns (use set_timetravel)",
270 relname, args[a_time_on], args[a_time_off]);
271 }
272 if (oldtimeoff != NOEND_ABSTIME)
273 { /* current record is a deleted/updated record */
274 pfree(relname);
275 return PointerGetDatum(NULL);
276 }
277
278 newtimeoff = GetCurrentAbsoluteTime();
279
280 /* Connect to SPI manager */
281 if ((ret = SPI_connect()) < 0)
282 elog(ERROR, "timetravel (%s): SPI_connect returned %d", relname, ret);
283
284 /* Fetch tuple values and nulls */
285 cvals = (Datum *) palloc(natts * sizeof(Datum));
286 cnulls = (char *) palloc(natts * sizeof(char));
287 for (i = 0; i < natts; i++)
288 {
289 cvals[i] = SPI_getbinval(trigtuple, tupdesc, i + 1, &isnull);
290 cnulls[i] = (isnull) ? 'n' : ' ';
291 }
292
293 /* change date column(s) */
294 cvals[attnum[a_time_off] - 1] = newtimeoff; /* stop_date eq current date */
295 cnulls[attnum[a_time_off] - 1] = ' ';
296
297 if (!newtuple)
298 { /* DELETE */
299 if (argc == MaxAttrNum)
300 {
301 cvals[attnum[a_del_user] - 1] = newuser; /* set delete user */
302 cnulls[attnum[a_del_user] - 1] = ' ';
303 }
304 }
305
306 /*
307 * Construct ident string as TriggerName $ TriggeredRelationId and try to
308 * find prepared execution plan.
309 */
310 snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
311 plan = find_plan(ident, &Plans, &nPlans);
312
313 /* if there is no plan ... */
314 if (plan->splan == NULL)
315 {
316 SPIPlanPtr pplan;
317 Oid *ctypes;
318 char sql[8192];
319 char separ = ' ';
320
321 /* allocate ctypes for preparation */
322 ctypes = (Oid *) palloc(natts * sizeof(Oid));
323
324 /*
325 * Construct query: INSERT INTO _relation_ VALUES ($1, ...)
326 */
327 snprintf(sql, sizeof(sql), "INSERT INTO %s VALUES (", relname);
328 for (i = 1; i <= natts; i++)
329 {
330 ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
331 if (!(TupleDescAttr(tupdesc, i - 1)->attisdropped)) /* skip dropped columns */
332 {
333 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ, i);
334 separ = ',';
335 }
336 }
337 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")");
338
339 elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql);
340
341 /* Prepare plan for query */
342 pplan = SPI_prepare(sql, natts, ctypes);
343 if (pplan == NULL)
344 elog(ERROR, "timetravel (%s): SPI_prepare returned %s", relname, SPI_result_code_string(SPI_result));
345
346 /*
347 * Remember that SPI_prepare places plan in current memory context -
348 * so, we have to save plan in Top memory context for later use.
349 */
350 if (SPI_keepplan(pplan))
351 elog(ERROR, "timetravel (%s): SPI_keepplan failed", relname);
352
353 plan->splan = pplan;
354 }
355
356 /*
357 * Ok, execute prepared plan.
358 */
359 ret = SPI_execp(plan->splan, cvals, cnulls, 0);
360
361 if (ret < 0)
362 elog(ERROR, "timetravel (%s): SPI_execp returned %d", relname, ret);
363
364 /* Tuple to return to upper Executor ... */
365 if (newtuple)
366 { /* UPDATE */
367 int chnattrs = 0;
368 int chattrs[MaxAttrNum];
369 Datum newvals[MaxAttrNum];
370 char newnulls[MaxAttrNum];
371
372 newvals[chnattrs] = newtimeoff;
373 newnulls[chnattrs] = ' ';
374 chattrs[chnattrs] = attnum[a_time_on];
375 chnattrs++;
376
377 newvals[chnattrs] = NOEND_ABSTIME;
378 newnulls[chnattrs] = ' ';
379 chattrs[chnattrs] = attnum[a_time_off];
380 chnattrs++;
381
382 if (argc == MaxAttrNum)
383 {
384 /* set update_user value */
385 newvals[chnattrs] = newuser;
386 newnulls[chnattrs] = ' ';
387 chattrs[chnattrs] = attnum[a_upd_user];
388 chnattrs++;
389 /* clear delete_user value */
390 newvals[chnattrs] = nulltext;
391 newnulls[chnattrs] = 'n';
392 chattrs[chnattrs] = attnum[a_del_user];
393 chnattrs++;
394 /* set insert_user value */
395 newvals[chnattrs] = nulltext;
396 newnulls[chnattrs] = 'n';
397 chattrs[chnattrs] = attnum[a_ins_user];
398 chnattrs++;
399 }
400
401 /*
402 * Use SPI_modifytuple() here because we are inside SPI environment
403 * but rettuple must be allocated in caller's context.
404 */
405 rettuple = SPI_modifytuple(rel, newtuple, chnattrs, chattrs, newvals, newnulls);
406 }
407 else
408 /* DELETE case */
409 rettuple = trigtuple;
410
411 SPI_finish(); /* don't forget say Bye to SPI mgr */
412
413 pfree(relname);
414 return PointerGetDatum(rettuple);
415 }
416
417 /*
418 * set_timetravel (relname, on) --
419 * turn timetravel for specified relation ON/OFF
420 */
421 PG_FUNCTION_INFO_V1(set_timetravel);
422
423 Datum
424 set_timetravel(PG_FUNCTION_ARGS)
425 {
426 Name relname = PG_GETARG_NAME(0);
427 int32 on = PG_GETARG_INT32(1);
428 char *rname;
429 char *d;
430 char *s;
431 int32 ret;
432 TTOffList *prev,
433 *pp;
434
435 prev = NULL;
436 for (pp = TTOff; pp; prev = pp, pp = pp->next)
437 {
438 if (namestrcmp(relname, pp->name) == 0)
439 break;
440 }
441 if (pp)
442 {
443 /* OFF currently */
444 if (on != 0)
445 {
446 /* turn ON */
447 if (prev)
448 prev->next = pp->next;
449 else
450 TTOff = pp->next;
451 free(pp);
452 }
453 ret = 0;
454 }
455 else
456 {
457 /* ON currently */
458 if (on == 0)
459 {
460 /* turn OFF */
461 s = rname = DatumGetCString(DirectFunctionCall1(nameout, NameGetDatum(relname)));
462 if (s)
463 {
464 pp = malloc(offsetof(TTOffList, name) + strlen(rname) + 1);
465 if (pp)
466 {
467 pp->next = NULL;
468 d = pp->name;
469 while (*s)
470 *d++ = tolower((unsigned char) *s++);
471 *d = '\0';
472 if (prev)
473 prev->next = pp;
474 else
475 TTOff = pp;
476 }
477 pfree(rname);
478 }
479 }
480 ret = 1;
481 }
482 PG_RETURN_INT32(ret);
483 }
484
485 /*
486 * get_timetravel (relname) --
487 * get timetravel status for specified relation (ON/OFF)
488 */
489 PG_FUNCTION_INFO_V1(get_timetravel);
490
491 Datum
492 get_timetravel(PG_FUNCTION_ARGS)
493 {
494 Name relname = PG_GETARG_NAME(0);
495 TTOffList *pp;
496
497 for (pp = TTOff; pp; pp = pp->next)
498 {
499 if (namestrcmp(relname, pp->name) == 0)
500 PG_RETURN_INT32(0);
501 }
502 PG_RETURN_INT32(1);
503 }
504
505 static int
506 findTTStatus(char *name)
507 {
508 TTOffList *pp;
509
510 for (pp = TTOff; pp; pp = pp->next)
511 if (pg_strcasecmp(name, pp->name) == 0)
512 return 0;
513 return 1;
514 }
515
516 /*
517 AbsoluteTime
518 currabstime()
519 {
520 return GetCurrentAbsoluteTime();
521 }
522 */
523
524 static EPlan *
525 find_plan(char *ident, EPlan **eplan, int *nplans)
526 {
527 EPlan *newp;
528 int i;
529
530 if (*nplans > 0)
531 {
532 for (i = 0; i < *nplans; i++)
533 {
534 if (strcmp((*eplan)[i].ident, ident) == 0)
535 break;
536 }
537 if (i != *nplans)
538 return (*eplan + i);
539 *eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
540 newp = *eplan + i;
541 }
542 else
543 {
544 newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
545 (*nplans) = i = 0;
546 }
547
548 newp->ident = strdup(ident);
549 newp->splan = NULL;
550 (*nplans)++;
551
552 return newp;
553 }
554