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