contrib/spi/timetravel.c

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