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
);