5
* timetravel.c -- function to get time travel feature
6
* using general triggers.
8
* Modified by Bļæ½JTHE Zoltļæ½n, Hungary, mailto:urdesobt@axelero.hu
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"
23
/* AbsoluteTime currabstime(void); */
24
Datum timetravel(PG_FUNCTION_ARGS);
25
Datum set_timetravel(PG_FUNCTION_ARGS);
26
Datum get_timetravel(PG_FUNCTION_ARGS);
34
static EPlan *Plans = NULL; /* for UPDATE/DELETE */
35
static int nPlans = 0;
37
typedef struct _TTOffList
39
struct _TTOffList *next;
43
static TTOffList TTOff = {NULL, {0}};
45
static int findTTStatus(char *name);
46
static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
50
* 1. IF an update affects tuple with stop_date eq INFINITY
51
* then form (and return) new tuple with start_date eq current date
52
* and stop_date eq INFINITY [ and update_user eq current user ]
53
* and all other column values as in new tuple, and insert tuple
54
* with old data and stop_date eq current date
55
* ELSE - skip updation of tuple.
56
* 2. IF an delete affects tuple with stop_date eq INFINITY
57
* then insert the same tuple with stop_date eq current date
58
* [ and delete_user eq current user ]
59
* ELSE - skip deletion of tuple.
60
* 3. On INSERT, if start_date is NULL then current date will be
61
* inserted, if stop_date is NULL then INFINITY will be inserted.
62
* [ and insert_user eq current user, update_user and delete_user
65
* In CREATE TRIGGER you are to specify start_date and stop_date column
68
* timetravel ('date_on', 'date_off' [,'insert_user', 'update_user', 'delete_user' ] ).
80
PG_FUNCTION_INFO_V1(timetravel);
82
Datum /* have to return HeapTuple to Executor */
83
timetravel(PG_FUNCTION_ARGS)
85
TriggerData *trigdata = (TriggerData *) fcinfo->context;
86
Trigger *trigger; /* to get trigger name */
88
char **args; /* arguments */
89
int attnum[MaxAttrNum]; /* fnumbers of start/stop columns */
96
Datum *cvals; /* column values */
97
char *cnulls; /* column nulls */
98
char *relname; /* triggered relation name */
99
Relation rel; /* triggered relation */
101
HeapTuple newtuple = NULL;
103
TupleDesc tupdesc; /* tuple description */
104
int natts; /* # of attributes */
105
EPlan *plan; /* prepared plan */
106
char ident[2 * NAMEDATALEN];
107
bool isnull; /* to know is some column NULL or not */
108
bool isinsert = false;
113
* Some checks first...
116
/* Called by trigger manager ? */
117
if (!CALLED_AS_TRIGGER(fcinfo))
118
elog(ERROR, "timetravel: not fired by trigger manager");
120
/* Should be called for ROW trigger */
121
if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
122
elog(ERROR, "timetravel: cannot process STATEMENT events");
124
/* Should be called BEFORE */
125
if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
126
elog(ERROR, "timetravel: must be fired before event");
129
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
132
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
133
newtuple = trigdata->tg_newtuple;
135
trigtuple = trigdata->tg_trigtuple;
137
rel = trigdata->tg_relation;
138
relname = SPI_getrelname(rel);
140
/* check if TT is OFF for this relation */
141
if (0 == findTTStatus(relname))
143
/* OFF - nothing to do */
145
return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
148
trigger = trigdata->tg_trigger;
150
argc = trigger->tgnargs;
151
if (argc != MinAttrNum && argc != MaxAttrNum)
152
elog(ERROR, "timetravel (%s): invalid (!= %d or %d) number of arguments %d",
153
relname, MinAttrNum, MaxAttrNum, trigger->tgnargs);
155
args = trigger->tgargs;
156
tupdesc = rel->rd_att;
157
natts = tupdesc->natts;
159
for (i = 0; i < MinAttrNum; i++)
161
attnum[i] = SPI_fnumber(tupdesc, args[i]);
163
elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
164
if (SPI_gettypeid(tupdesc, attnum[i]) != ABSTIMEOID)
165
elog(ERROR, "timetravel (%s): attribute %s must be of abstime type",
168
for (; i < argc; i++)
170
attnum[i] = SPI_fnumber(tupdesc, args[i]);
172
elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
173
if (SPI_gettypeid(tupdesc, attnum[i]) != TEXTOID)
174
elog(ERROR, "timetravel (%s): attribute %s must be of text type",
178
/* create fields containing name */
179
newuser = CStringGetTextDatum(GetUserNameFromId(GetUserId()));
181
nulltext = (Datum) NULL;
186
int chattrs[MaxAttrNum];
187
Datum newvals[MaxAttrNum];
188
char newnulls[MaxAttrNum];
190
oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
193
newvals[chnattrs] = GetCurrentAbsoluteTime();
194
newnulls[chnattrs] = ' ';
195
chattrs[chnattrs] = attnum[a_time_on];
199
oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
202
if ((chnattrs == 0 && DatumGetInt32(oldtimeon) >= NOEND_ABSTIME) ||
203
(chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) >= NOEND_ABSTIME))
204
elog(ERROR, "timetravel (%s): %s is infinity", relname, args[a_time_on]);
205
newvals[chnattrs] = NOEND_ABSTIME;
206
newnulls[chnattrs] = ' ';
207
chattrs[chnattrs] = attnum[a_time_off];
212
if ((chnattrs == 0 && DatumGetInt32(oldtimeon) > DatumGetInt32(oldtimeoff)) ||
213
(chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) > DatumGetInt32(oldtimeoff)))
214
elog(ERROR, "timetravel (%s): %s gt %s", relname, args[a_time_on], args[a_time_off]);
219
return PointerGetDatum(trigtuple);
221
if (argc == MaxAttrNum)
223
/* clear update_user value */
224
newvals[chnattrs] = nulltext;
225
newnulls[chnattrs] = 'n';
226
chattrs[chnattrs] = attnum[a_upd_user];
228
/* clear delete_user value */
229
newvals[chnattrs] = nulltext;
230
newnulls[chnattrs] = 'n';
231
chattrs[chnattrs] = attnum[a_del_user];
233
/* set insert_user value */
234
newvals[chnattrs] = newuser;
235
newnulls[chnattrs] = ' ';
236
chattrs[chnattrs] = attnum[a_ins_user];
239
rettuple = SPI_modifytuple(rel, trigtuple, chnattrs, chattrs, newvals, newnulls);
240
return PointerGetDatum(rettuple);
245
oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
247
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
249
oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
251
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
254
* If DELETE/UPDATE of tuple with stop_date neq INFINITY then say upper
255
* Executor to skip operation for this tuple
257
if (newtuple != NULL)
259
newtimeon = SPI_getbinval(newtuple, tupdesc, attnum[a_time_on], &isnull);
261
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
263
newtimeoff = SPI_getbinval(newtuple, tupdesc, attnum[a_time_off], &isnull);
265
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
267
if (oldtimeon != newtimeon || oldtimeoff != newtimeoff)
268
elog(ERROR, "timetravel (%s): you cannot change %s and/or %s columns (use set_timetravel)",
269
relname, args[a_time_on], args[a_time_off]);
271
if (oldtimeoff != NOEND_ABSTIME)
272
{ /* current record is a deleted/updated record */
274
return PointerGetDatum(NULL);
277
newtimeoff = GetCurrentAbsoluteTime();
279
/* Connect to SPI manager */
280
if ((ret = SPI_connect()) < 0)
281
elog(ERROR, "timetravel (%s): SPI_connect returned %d", relname, ret);
283
/* Fetch tuple values and nulls */
284
cvals = (Datum *) palloc(natts * sizeof(Datum));
285
cnulls = (char *) palloc(natts * sizeof(char));
286
for (i = 0; i < natts; i++)
288
cvals[i] = SPI_getbinval(trigtuple, tupdesc, i + 1, &isnull);
289
cnulls[i] = (isnull) ? 'n' : ' ';
292
/* change date column(s) */
293
cvals[attnum[a_time_off] - 1] = newtimeoff; /* stop_date eq current date */
294
cnulls[attnum[a_time_off] - 1] = ' ';
298
if (argc == MaxAttrNum)
300
cvals[attnum[a_del_user] - 1] = newuser; /* set delete user */
301
cnulls[attnum[a_del_user] - 1] = ' ';
306
* Construct ident string as TriggerName $ TriggeredRelationId and try to
307
* find prepared execution plan.
309
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
310
plan = find_plan(ident, &Plans, &nPlans);
312
/* if there is no plan ... */
313
if (plan->splan == NULL)
320
/* allocate ctypes for preparation */
321
ctypes = (Oid *) palloc(natts * sizeof(Oid));
324
* Construct query: INSERT INTO _relation_ VALUES ($1, ...)
326
snprintf(sql, sizeof(sql), "INSERT INTO %s VALUES (", relname);
327
for (i = 1; i <= natts; i++)
329
ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
330
if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */
332
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ, i);
336
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")");
338
elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql);
340
/* Prepare plan for query */
341
pplan = SPI_prepare(sql, natts, ctypes);
343
elog(ERROR, "timetravel (%s): SPI_prepare returned %d", relname, SPI_result);
346
* Remember that SPI_prepare places plan in current memory context -
347
* so, we have to save plan in Top memory context for latter use.
349
pplan = SPI_saveplan(pplan);
351
elog(ERROR, "timetravel (%s): SPI_saveplan returned %d", relname, SPI_result);
357
* Ok, execute prepared plan.
359
ret = SPI_execp(plan->splan, cvals, cnulls, 0);
362
elog(ERROR, "timetravel (%s): SPI_execp returned %d", relname, ret);
364
/* Tuple to return to upper Executor ... */
368
int chattrs[MaxAttrNum];
369
Datum newvals[MaxAttrNum];
370
char newnulls[MaxAttrNum];
372
newvals[chnattrs] = newtimeoff;
373
newnulls[chnattrs] = ' ';
374
chattrs[chnattrs] = attnum[a_time_on];
377
newvals[chnattrs] = NOEND_ABSTIME;
378
newnulls[chnattrs] = ' ';
379
chattrs[chnattrs] = attnum[a_time_off];
382
if (argc == MaxAttrNum)
384
/* set update_user value */
385
newvals[chnattrs] = newuser;
386
newnulls[chnattrs] = ' ';
387
chattrs[chnattrs] = attnum[a_upd_user];
389
/* clear delete_user value */
390
newvals[chnattrs] = nulltext;
391
newnulls[chnattrs] = 'n';
392
chattrs[chnattrs] = attnum[a_del_user];
394
/* set insert_user value */
395
newvals[chnattrs] = nulltext;
396
newnulls[chnattrs] = 'n';
397
chattrs[chnattrs] = attnum[a_ins_user];
401
rettuple = SPI_modifytuple(rel, newtuple, chnattrs, chattrs, newvals, newnulls);
404
* SPI_copytuple allocates tmptuple in upper executor context - have
405
* to free allocation using SPI_pfree
407
/* SPI_pfree(tmptuple); */
411
rettuple = trigtuple;
413
SPI_finish(); /* don't forget say Bye to SPI mgr */
416
return PointerGetDatum(rettuple);
420
* set_timetravel (relname, on) --
421
* turn timetravel for specified relation ON/OFF
423
PG_FUNCTION_INFO_V1(set_timetravel);
426
set_timetravel(PG_FUNCTION_ARGS)
428
Name relname = PG_GETARG_NAME(0);
429
int32 on = PG_GETARG_INT32(1);
437
for (pp = (p = &TTOff)->next; pp; pp = (p = pp)->next)
439
if (namestrcmp(relname, pp->name) == 0)
459
s = rname = DatumGetCString(DirectFunctionCall1(nameout, NameGetDatum(relname)));
462
pp = malloc(sizeof(TTOffList) + strlen(rname));
469
*d++ = tolower((unsigned char) *s++);
477
PG_RETURN_INT32(ret);
481
* get_timetravel (relname) --
482
* get timetravel status for specified relation (ON/OFF)
484
PG_FUNCTION_INFO_V1(get_timetravel);
487
get_timetravel(PG_FUNCTION_ARGS)
489
Name relname = PG_GETARG_NAME(0);
492
for (pp = TTOff.next; pp; pp = pp->next)
494
if (namestrcmp(relname, pp->name) == 0)
501
findTTStatus(char *name)
505
for (pp = TTOff.next; pp; pp = pp->next)
506
if (pg_strcasecmp(name, pp->name) == 0)
515
return (GetCurrentAbsoluteTime());
520
find_plan(char *ident, EPlan ** eplan, int *nplans)
527
for (i = 0; i < *nplans; i++)
529
if (strcmp((*eplan)[i].ident, ident) == 0)
534
*eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
539
newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
543
newp->ident = (char *) malloc(strlen(ident) + 1);
544
strcpy(newp->ident, ident);