contrib/spi/timetravel.c

   1 /*
   2  * $PostgreSQL$
   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
  21 PG_MODULE_MAGIC;
  22
  23 /* AbsoluteTime currabstime(void); */
  24 Datum           timetravel(PG_FUNCTION_ARGS);
  25 Datum           set_timetravel(PG_FUNCTION_ARGS);
  26 Datum           get_timetravel(PG_FUNCTION_ARGS);
  27
  28 typedef struct
  29 {
  30         char       *ident;
  31         SPIPlanPtr      splan;
  32 } EPlan;
  33
  34 static EPlan *Plans = NULL;             /* for UPDATE/DELETE */
  35 static int      nPlans = 0;
  36
  37 typedef struct _TTOffList
  38 {
  39         struct _TTOffList *next;
  40         char            name[1];
  41 } TTOffList;
  42
  43 static TTOffList TTOff = {NULL, {0}};
  44
  45 static int      findTTStatus(char *name);
  46 static EPlan *find_plan(char *ident, EPlan **eplan, int *nplans);
  47
  48 /*
  49  * timetravel () --
  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
  63  *                      eq NULL ]
  64  *
  65  * In CREATE TRIGGER you are to specify start_date and stop_date column
  66  * names:
  67  * EXECUTE PROCEDURE
  68  * timetravel ('date_on', 'date_off' [,'insert_user', 'update_user', 'delete_user' ] ).
  69  */
  70
  71 #define MaxAttrNum      5
  72 #define MinAttrNum      2
  73
  74 #define a_time_on       0
  75 #define a_time_off      1
  76 #define a_ins_user      2
  77 #define a_upd_user      3
  78 #define a_del_user      4
  79
  80 PG_FUNCTION_INFO_V1(timetravel);
  81
  82 Datum                                                   /* have to return HeapTuple to Executor */
  83 timetravel(PG_FUNCTION_ARGS)
  84 {
  85         TriggerData *trigdata = (TriggerData *) fcinfo->context;
  86         Trigger    *trigger;            /* to get trigger name */
  87         int                     argc;
  88         char      **args;                       /* arguments */
  89         int                     attnum[MaxAttrNum];             /* fnumbers of start/stop columns */
  90         Datum           oldtimeon,
  91                                 oldtimeoff;
  92         Datum           newtimeon,
  93                                 newtimeoff,
  94                                 newuser,
  95                                 nulltext;
  96         Datum      *cvals;                      /* column values */
  97         char       *cnulls;                     /* column nulls */
  98         char       *relname;            /* triggered relation name */
  99         Relation        rel;                    /* triggered relation */
 100         HeapTuple       trigtuple;
 101         HeapTuple       newtuple = NULL;
 102         HeapTuple       rettuple;
 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;
 109         int                     ret;
 110         int                     i;
 111
 112         /*
 113          * Some checks first...
 114          */
 115
 116         /* Called by trigger manager ? */
 117         if (!CALLED_AS_TRIGGER(fcinfo))
 118                 elog(ERROR, "timetravel: not fired by trigger manager");
 119
 120         /* Should be called for ROW trigger */
 121         if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
 122                 elog(ERROR, "timetravel: cannot process STATEMENT events");
 123
 124         /* Should be called BEFORE */
 125         if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
 126                 elog(ERROR, "timetravel: must be fired before event");
 127
 128         /* INSERT ? */
 129         if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
 130                 isinsert = true;
 131
 132         if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
 133                 newtuple = trigdata->tg_newtuple;
 134
 135         trigtuple = trigdata->tg_trigtuple;
 136
 137         rel = trigdata->tg_relation;
 138         relname = SPI_getrelname(rel);
 139
 140         /* check if TT is OFF for this relation */
 141         if (0 == findTTStatus(relname))
 142         {
 143                 /* OFF - nothing to do */
 144                 pfree(relname);
 145                 return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
 146         }
 147
 148         trigger = trigdata->tg_trigger;
 149
 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);
 154
 155         args = trigger->tgargs;
 156         tupdesc = rel->rd_att;
 157         natts = tupdesc->natts;
 158
 159         for (i = 0; i < MinAttrNum; i++)
 160         {
 161                 attnum[i] = SPI_fnumber(tupdesc, args[i]);
 162                 if (attnum[i] < 0)
 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",
 166                                  relname, args[i]);
 167         }
 168         for (; i < argc; i++)
 169         {
 170                 attnum[i] = SPI_fnumber(tupdesc, args[i]);
 171                 if (attnum[i] < 0)
 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",
 175                                  relname, args[i]);
 176         }
 177
 178         /* create fields containing name */
 179         newuser = CStringGetTextDatum(GetUserNameFromId(GetUserId()));
 180
 181         nulltext = (Datum) NULL;
 182
 183         if (isinsert)
 184         {                                                       /* INSERT */
 185                 int                     chnattrs = 0;
 186                 int                     chattrs[MaxAttrNum];
 187                 Datum           newvals[MaxAttrNum];
 188                 char            newnulls[MaxAttrNum];
 189
 190                 oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
 191                 if (isnull)
 192                 {
 193                         newvals[chnattrs] = GetCurrentAbsoluteTime();
 194                         newnulls[chnattrs] = ' ';
 195                         chattrs[chnattrs] = attnum[a_time_on];
 196                         chnattrs++;
 197                 }
 198
 199                 oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
 200                 if (isnull)
 201                 {
 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];
 208                         chnattrs++;
 209                 }
 210                 else
 211                 {
 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]);
 215                 }
 216
 217                 pfree(relname);
 218                 if (chnattrs <= 0)
 219                         return PointerGetDatum(trigtuple);
 220
 221                 if (argc == MaxAttrNum)
 222                 {
 223                         /* clear update_user value */
 224                         newvals[chnattrs] = nulltext;
 225                         newnulls[chnattrs] = 'n';
 226                         chattrs[chnattrs] = attnum[a_upd_user];
 227                         chnattrs++;
 228                         /* clear delete_user value */
 229                         newvals[chnattrs] = nulltext;
 230                         newnulls[chnattrs] = 'n';
 231                         chattrs[chnattrs] = attnum[a_del_user];
 232                         chnattrs++;
 233                         /* set insert_user value */
 234                         newvals[chnattrs] = newuser;
 235                         newnulls[chnattrs] = ' ';
 236                         chattrs[chnattrs] = attnum[a_ins_user];
 237                         chnattrs++;
 238                 }
 239                 rettuple = SPI_modifytuple(rel, trigtuple, chnattrs, chattrs, newvals, newnulls);
 240                 return PointerGetDatum(rettuple);
 241                 /* end of INSERT */
 242         }
 243
 244         /* UPDATE/DELETE: */
 245         oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
 246         if (isnull)
 247                 elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
 248
 249         oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
 250         if (isnull)
 251                 elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
 252
 253         /*
 254          * If DELETE/UPDATE of tuple with stop_date neq INFINITY then say upper
 255          * Executor to skip operation for this tuple
 256          */
 257         if (newtuple != NULL)
 258         {                                                       /* UPDATE */
 259                 newtimeon = SPI_getbinval(newtuple, tupdesc, attnum[a_time_on], &isnull);
 260                 if (isnull)
 261                         elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
 262
 263                 newtimeoff = SPI_getbinval(newtuple, tupdesc, attnum[a_time_off], &isnull);
 264                 if (isnull)
 265                         elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
 266
 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]);
 270         }
 271         if (oldtimeoff != NOEND_ABSTIME)
 272         {                                                       /* current record is a deleted/updated record */
 273                 pfree(relname);
 274                 return PointerGetDatum(NULL);
 275         }
 276
 277         newtimeoff = GetCurrentAbsoluteTime();
 278
 279         /* Connect to SPI manager */
 280         if ((ret = SPI_connect()) < 0)
 281                 elog(ERROR, "timetravel (%s): SPI_connect returned %d", relname, ret);
 282
 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++)
 287         {
 288                 cvals[i] = SPI_getbinval(trigtuple, tupdesc, i + 1, &isnull);
 289                 cnulls[i] = (isnull) ? 'n' : ' ';
 290         }
 291
 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] = ' ';
 295
 296         if (!newtuple)
 297         {                                                       /* DELETE */
 298                 if (argc == MaxAttrNum)
 299                 {
 300                         cvals[attnum[a_del_user] - 1] = newuser;        /* set delete user */
 301                         cnulls[attnum[a_del_user] - 1] = ' ';
 302                 }
 303         }
 304
 305         /*
 306          * Construct ident string as TriggerName $ TriggeredRelationId and try to
 307          * find prepared execution plan.
 308          */
 309         snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
 310         plan = find_plan(ident, &Plans, &nPlans);
 311
 312         /* if there is no plan ... */
 313         if (plan->splan == NULL)
 314         {
 315                 SPIPlanPtr      pplan;
 316                 Oid                *ctypes;
 317                 char            sql[8192];
 318                 char            separ = ' ';
 319
 320                 /* allocate ctypes for preparation */
 321                 ctypes = (Oid *) palloc(natts * sizeof(Oid));
 322
 323                 /*
 324                  * Construct query: INSERT INTO _relation_ VALUES ($1, ...)
 325                  */
 326                 snprintf(sql, sizeof(sql), "INSERT INTO %s VALUES (", relname);
 327                 for (i = 1; i <= natts; i++)
 328                 {
 329                         ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
 330                         if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */
 331                         {
 332                                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ, i);
 333                                 separ = ',';
 334                         }
 335                 }
 336                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")");
 337
 338                 elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql);
 339
 340                 /* Prepare plan for query */
 341                 pplan = SPI_prepare(sql, natts, ctypes);
 342                 if (pplan == NULL)
 343                         elog(ERROR, "timetravel (%s): SPI_prepare returned %d", relname, SPI_result);
 344
 345                 /*
 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.
 348                  */
 349                 pplan = SPI_saveplan(pplan);
 350                 if (pplan == NULL)
 351                         elog(ERROR, "timetravel (%s): SPI_saveplan returned %d", relname, SPI_result);
 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                 rettuple = SPI_modifytuple(rel, newtuple, chnattrs, chattrs, newvals, newnulls);
 402
 403                 /*
 404                  * SPI_copytuple allocates tmptuple in upper executor context - have
 405                  * to free allocation using SPI_pfree
 406                  */
 407                 /* SPI_pfree(tmptuple); */
 408         }
 409         else
 410                 /* DELETE case */
 411                 rettuple = trigtuple;
 412
 413         SPI_finish();                           /* don't forget say Bye to SPI mgr */
 414
 415         pfree(relname);
 416         return PointerGetDatum(rettuple);
 417 }
 418
 419 /*
 420  * set_timetravel (relname, on) --
 421  *                                      turn timetravel for specified relation ON/OFF
 422  */
 423 PG_FUNCTION_INFO_V1(set_timetravel);
 424
 425 Datum
 426 set_timetravel(PG_FUNCTION_ARGS)
 427 {
 428         Name            relname = PG_GETARG_NAME(0);
 429         int32           on = PG_GETARG_INT32(1);
 430         char       *rname;
 431         char       *d;
 432         char       *s;
 433         int32           ret;
 434         TTOffList  *p,
 435                            *pp;
 436
 437         for (pp = (p = &TTOff)->next; pp; pp = (p = pp)->next)
 438         {
 439                 if (namestrcmp(relname, pp->name) == 0)
 440                         break;
 441         }
 442         if (pp)
 443         {
 444                 /* OFF currently */
 445                 if (on != 0)
 446                 {
 447                         /* turn ON */
 448                         p->next = pp->next;
 449                         free(pp);
 450                 }
 451                 ret = 0;
 452         }
 453         else
 454         {
 455                 /* ON currently */
 456                 if (on == 0)
 457                 {
 458                         /* turn OFF */
 459                         s = rname = DatumGetCString(DirectFunctionCall1(nameout, NameGetDatum(relname)));
 460                         if (s)
 461                         {
 462                                 pp = malloc(sizeof(TTOffList) + strlen(rname));
 463                                 if (pp)
 464                                 {
 465                                         pp->next = NULL;
 466                                         p->next = pp;
 467                                         d = pp->name;
 468                                         while (*s)
 469                                                 *d++ = tolower((unsigned char) *s++);
 470                                         *d = '\0';
 471                                 }
 472                                 pfree(rname);
 473                         }
 474                 }
 475                 ret = 1;
 476         }
 477         PG_RETURN_INT32(ret);
 478 }
 479
 480 /*
 481  * get_timetravel (relname) --
 482  *      get timetravel status for specified relation (ON/OFF)
 483  */
 484 PG_FUNCTION_INFO_V1(get_timetravel);
 485
 486 Datum
 487 get_timetravel(PG_FUNCTION_ARGS)
 488 {
 489         Name            relname = PG_GETARG_NAME(0);
 490         TTOffList  *pp;
 491
 492         for (pp = TTOff.next; pp; pp = pp->next)
 493         {
 494                 if (namestrcmp(relname, pp->name) == 0)
 495                         PG_RETURN_INT32(0);
 496         }
 497         PG_RETURN_INT32(1);
 498 }
 499
 500 static int
 501 findTTStatus(char *name)
 502 {
 503         TTOffList  *pp;
 504
 505         for (pp = TTOff.next; pp; pp = pp->next)
 506                 if (pg_strcasecmp(name, pp->name) == 0)
 507                         return 0;
 508         return 1;
 509 }
 510
 511 /*
 512 AbsoluteTime
 513 currabstime()
 514 {
 515         return (GetCurrentAbsoluteTime());
 516 }
 517 */
 518
 519 static EPlan *
 520 find_plan(char *ident, EPlan **eplan, int *nplans)
 521 {
 522         EPlan      *newp;
 523         int                     i;
 524
 525         if (*nplans > 0)
 526         {
 527                 for (i = 0; i < *nplans; i++)
 528                 {
 529                         if (strcmp((*eplan)[i].ident, ident) == 0)
 530                                 break;
 531                 }
 532                 if (i != *nplans)
 533                         return (*eplan + i);
 534                 *eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
 535                 newp = *eplan + i;
 536         }
 537         else
 538         {
 539                 newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
 540                 (*nplans) = i = 0;
 541         }
 542
 543         newp->ident = (char *) malloc(strlen(ident) + 1);
 544         strcpy(newp->ident, ident);
 545         newp->splan = NULL;
 546         (*nplans)++;
 547
 548         return (newp);
 549 }