2
* timetravel.c -- function to get time travel feature
3
* using general triggers.
6
/* Modified by B�JTHE Zolt�n, Hungary, mailto:urdesobt@axelero.hu */
8
#include "executor/spi.h" /* this is what you need to work with SPI */
9
#include "commands/trigger.h" /* -"- and triggers */
10
#include "miscadmin.h" /* for GetPgUserName() */
11
#include <ctype.h> /* tolower () */
13
#define ABSTIMEOID 702 /* it should be in pg_type.h */
15
/* AbsoluteTime currabstime(void); */
16
Datum timetravel(PG_FUNCTION_ARGS);
17
Datum set_timetravel(PG_FUNCTION_ARGS);
18
Datum get_timetravel(PG_FUNCTION_ARGS);
26
static EPlan *Plans = NULL; /* for UPDATE/DELETE */
27
static int nPlans = 0;
29
typedef struct _TTOffList
31
struct _TTOffList *next;
35
static TTOffList TTOff = {NULL, {0}};
37
static int findTTStatus(char *name);
38
static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
42
* 1. IF an update affects tuple with stop_date eq INFINITY
43
* then form (and return) new tuple with start_date eq current date
44
* and stop_date eq INFINITY [ and update_user eq current user ]
45
* and all other column values as in new tuple, and insert tuple
46
* with old data and stop_date eq current date
47
* ELSE - skip updation of tuple.
48
* 2. IF an delete affects tuple with stop_date eq INFINITY
49
* then insert the same tuple with stop_date eq current date
50
* [ and delete_user eq current user ]
51
* ELSE - skip deletion of tuple.
52
* 3. On INSERT, if start_date is NULL then current date will be
53
* inserted, if stop_date is NULL then INFINITY will be inserted.
54
* [ and insert_user eq current user, update_user and delete_user
57
* In CREATE TRIGGER you are to specify start_date and stop_date column
60
* timetravel ('date_on', 'date_off' [,'insert_user', 'update_user', 'delete_user' ] ).
72
PG_FUNCTION_INFO_V1(timetravel);
74
Datum /* have to return HeapTuple to Executor */
75
timetravel(PG_FUNCTION_ARGS)
77
TriggerData *trigdata = (TriggerData *) fcinfo->context;
78
Trigger *trigger; /* to get trigger name */
80
char **args; /* arguments */
81
int attnum[MaxAttrNum]; /* fnumbers of start/stop columns */
88
Datum *cvals; /* column values */
89
char *cnulls; /* column nulls */
90
char *relname; /* triggered relation name */
91
Relation rel; /* triggered relation */
93
HeapTuple newtuple = NULL;
95
TupleDesc tupdesc; /* tuple description */
96
int natts; /* # of attributes */
97
EPlan *plan; /* prepared plan */
98
char ident[2 * NAMEDATALEN];
99
bool isnull; /* to know is some column NULL or not */
100
bool isinsert = false;
105
* Some checks first...
108
/* Called by trigger manager ? */
109
if (!CALLED_AS_TRIGGER(fcinfo))
110
elog(ERROR, "timetravel: not fired by trigger manager");
112
/* Should be called for ROW trigger */
113
if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
114
elog(ERROR, "timetravel: can't process STATEMENT events");
116
/* Should be called BEFORE */
117
if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
118
elog(ERROR, "timetravel: must be fired before event");
121
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
124
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
125
newtuple = trigdata->tg_newtuple;
127
trigtuple = trigdata->tg_trigtuple;
129
rel = trigdata->tg_relation;
130
relname = SPI_getrelname(rel);
132
/* check if TT is OFF for this relation */
133
if (0 == findTTStatus(relname))
135
/* OFF - nothing to do */
137
return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
140
trigger = trigdata->tg_trigger;
142
argc = trigger->tgnargs;
143
if (argc != MinAttrNum && argc != MaxAttrNum)
144
elog(ERROR, "timetravel (%s): invalid (!= %d or %d) number of arguments %d",
145
relname, MinAttrNum, MaxAttrNum, trigger->tgnargs);
147
args = trigger->tgargs;
148
tupdesc = rel->rd_att;
149
natts = tupdesc->natts;
151
for (i = 0; i < MinAttrNum; i++)
153
attnum[i] = SPI_fnumber(tupdesc, args[i]);
155
elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
156
if (SPI_gettypeid(tupdesc, attnum[i]) != ABSTIMEOID)
157
elog(ERROR, "timetravel (%s): attribute %s must be of abstime type",
160
for (; i < argc; i++)
162
attnum[i] = SPI_fnumber(tupdesc, args[i]);
164
elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
165
if (SPI_gettypeid(tupdesc, attnum[i]) != TEXTOID)
166
elog(ERROR, "timetravel (%s): attribute %s must be of text type",
170
/* create fields containing name */
171
newuser = DirectFunctionCall1(textin, CStringGetDatum(GetUserNameFromId(GetUserId())));
173
nulltext = (Datum) NULL;
178
int chattrs[MaxAttrNum];
179
Datum newvals[MaxAttrNum];
180
char newnulls[MaxAttrNum];
182
oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
185
newvals[chnattrs] = GetCurrentAbsoluteTime();
186
newnulls[chnattrs] = ' ';
187
chattrs[chnattrs] = attnum[a_time_on];
191
oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
194
if ((chnattrs == 0 && DatumGetInt32(oldtimeon) >= NOEND_ABSTIME) ||
195
(chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) >= NOEND_ABSTIME))
196
elog(ERROR, "timetravel (%s): %s is infinity", relname, args[a_time_on]);
197
newvals[chnattrs] = NOEND_ABSTIME;
198
newnulls[chnattrs] = ' ';
199
chattrs[chnattrs] = attnum[a_time_off];
204
if ((chnattrs == 0 && DatumGetInt32(oldtimeon) > DatumGetInt32(oldtimeoff)) ||
205
(chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) > DatumGetInt32(oldtimeoff)))
206
elog(ERROR, "timetravel (%s): %s gt %s", relname, args[a_time_on], args[a_time_off]);
211
return PointerGetDatum(trigtuple);
213
if (argc == MaxAttrNum)
215
/* clear update_user value */
216
newvals[chnattrs] = nulltext;
217
newnulls[chnattrs] = 'n';
218
chattrs[chnattrs] = attnum[a_upd_user];
220
/* clear delete_user value */
221
newvals[chnattrs] = nulltext;
222
newnulls[chnattrs] = 'n';
223
chattrs[chnattrs] = attnum[a_del_user];
225
/* set insert_user value */
226
newvals[chnattrs] = newuser;
227
newnulls[chnattrs] = ' ';
228
chattrs[chnattrs] = attnum[a_ins_user];
231
rettuple = SPI_modifytuple(rel, trigtuple, chnattrs, chattrs, newvals, newnulls);
232
return PointerGetDatum(rettuple);
237
oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
239
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
241
oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
243
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
246
* If DELETE/UPDATE of tuple with stop_date neq INFINITY then say
247
* upper Executor to skip operation for this tuple
249
if (newtuple != NULL)
251
newtimeon = SPI_getbinval(newtuple, tupdesc, attnum[a_time_on], &isnull);
253
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
255
newtimeoff = SPI_getbinval(newtuple, tupdesc, attnum[a_time_off], &isnull);
257
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
259
if (oldtimeon != newtimeon || oldtimeoff != newtimeoff)
260
elog(ERROR, "timetravel (%s): you can't change %s and/or %s columns (use set_timetravel)",
261
relname, args[a_time_on], args[a_time_off]);
263
if (oldtimeoff != NOEND_ABSTIME)
264
{ /* current record is a deleted/updated
267
return PointerGetDatum(NULL);
270
newtimeoff = GetCurrentAbsoluteTime();
272
/* Connect to SPI manager */
273
if ((ret = SPI_connect()) < 0)
274
elog(ERROR, "timetravel (%s): SPI_connect returned %d", relname, ret);
276
/* Fetch tuple values and nulls */
277
cvals = (Datum *) palloc(natts * sizeof(Datum));
278
cnulls = (char *) palloc(natts * sizeof(char));
279
for (i = 0; i < natts; i++)
281
cvals[i] = SPI_getbinval(trigtuple, tupdesc, i + 1, &isnull);
282
cnulls[i] = (isnull) ? 'n' : ' ';
285
/* change date column(s) */
286
cvals[attnum[a_time_off] - 1] = newtimeoff; /* stop_date eq current
288
cnulls[attnum[a_time_off] - 1] = ' ';
292
if (argc == MaxAttrNum)
294
cvals[attnum[a_del_user] - 1] = newuser; /* set delete user */
295
cnulls[attnum[a_del_user] - 1] = ' ';
300
* Construct ident string as TriggerName $ TriggeredRelationId and try
301
* to find prepared execution plan.
303
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
304
plan = find_plan(ident, &Plans, &nPlans);
306
/* if there is no plan ... */
307
if (plan->splan == NULL)
314
/* allocate ctypes for preparation */
315
ctypes = (Oid *) palloc(natts * sizeof(Oid));
318
* Construct query: INSERT INTO _relation_ VALUES ($1, ...)
320
snprintf(sql, sizeof(sql), "INSERT INTO %s VALUES (", relname);
321
for (i = 1; i <= natts; i++)
323
ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
324
if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */
326
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ, i);
330
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")");
332
elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql);
334
/* Prepare plan for query */
335
pplan = SPI_prepare(sql, natts, ctypes);
337
elog(ERROR, "timetravel (%s): SPI_prepare returned %d", relname, SPI_result);
340
* Remember that SPI_prepare places plan in current memory context
341
* - so, we have to save plan in Top memory context for latter
344
pplan = SPI_saveplan(pplan);
346
elog(ERROR, "timetravel (%s): SPI_saveplan returned %d", relname, SPI_result);
352
* Ok, execute prepared plan.
354
ret = SPI_execp(plan->splan, cvals, cnulls, 0);
357
elog(ERROR, "timetravel (%s): SPI_execp returned %d", relname, ret);
359
/* Tuple to return to upper Executor ... */
363
int chattrs[MaxAttrNum];
364
Datum newvals[MaxAttrNum];
365
char newnulls[MaxAttrNum];
367
newvals[chnattrs] = newtimeoff;
368
newnulls[chnattrs] = ' ';
369
chattrs[chnattrs] = attnum[a_time_on];
372
newvals[chnattrs] = NOEND_ABSTIME;
373
newnulls[chnattrs] = ' ';
374
chattrs[chnattrs] = attnum[a_time_off];
377
if (argc == MaxAttrNum)
379
/* set update_user value */
380
newvals[chnattrs] = newuser;
381
newnulls[chnattrs] = ' ';
382
chattrs[chnattrs] = attnum[a_upd_user];
384
/* clear delete_user value */
385
newvals[chnattrs] = nulltext;
386
newnulls[chnattrs] = 'n';
387
chattrs[chnattrs] = attnum[a_del_user];
389
/* set insert_user value */
390
newvals[chnattrs] = nulltext;
391
newnulls[chnattrs] = 'n';
392
chattrs[chnattrs] = attnum[a_ins_user];
396
rettuple = SPI_modifytuple(rel, newtuple, chnattrs, chattrs, newvals, newnulls);
399
* SPI_copytuple allocates tmptuple in upper executor context -
400
* have to free allocation using SPI_pfree
402
/* SPI_pfree(tmptuple); */
406
rettuple = trigtuple;
408
SPI_finish(); /* don't forget say Bye to SPI mgr */
411
return PointerGetDatum(rettuple);
415
* set_timetravel (relname, on) --
416
* turn timetravel for specified relation ON/OFF
418
PG_FUNCTION_INFO_V1(set_timetravel);
421
set_timetravel(PG_FUNCTION_ARGS)
423
Name relname = PG_GETARG_NAME(0);
424
int32 on = PG_GETARG_INT32(1);
432
for (pp = (p = &TTOff)->next; pp; pp = (p = pp)->next)
434
if (namestrcmp(relname, pp->name) == 0)
454
s = rname = DatumGetCString(DirectFunctionCall1(nameout, NameGetDatum(relname)));
457
pp = malloc(sizeof(TTOffList) + strlen(rname));
464
*d++ = tolower((unsigned char) *s++);
472
PG_RETURN_INT32(ret);
476
* get_timetravel (relname) --
477
* get timetravel status for specified relation (ON/OFF)
479
PG_FUNCTION_INFO_V1(get_timetravel);
482
get_timetravel(PG_FUNCTION_ARGS)
484
Name relname = PG_GETARG_NAME(0);
487
for (pp = TTOff.next; pp; pp = pp->next)
489
if (namestrcmp(relname, pp->name) == 0)
496
findTTStatus(char *name)
500
for (pp = TTOff.next; pp; pp = pp->next)
501
if (pg_strcasecmp(name, pp->name) == 0)
510
return (GetCurrentAbsoluteTime());
515
find_plan(char *ident, EPlan ** eplan, int *nplans)
522
for (i = 0; i < *nplans; i++)
524
if (strcmp((*eplan)[i].ident, ident) == 0)
529
*eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
534
newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
538
newp->ident = (char *) malloc(strlen(ident) + 1);
539
strcpy(newp->ident, ident);