Skip site navigation (1) Skip section navigation (2)

SPI problem

From: "Alastair Bell" Turner "" <bell(at)tangent(dot)co(dot)za>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: SPI problem
Date: 2004-06-23 14:48:01
Message-ID: 12482.209.212.106.190.1088002081.squirrel@mail.tangent.co.za (view raw or flat)
Thread:
Lists: pgsql-interfaces
I'm trying to build a c trigger with the Server Programming Interface and
not having a lot of joy.

The code is based mainly on the example code on the documentation site.

If it return trigdata->tg_trigtuple with PointerGetDatum it seems to work.
Trying to return TempTuple with PointerGetDatum gives me no error at
execution but I get an error the next time I try to select from the table
which I have to DROP DATABASE to get rid of which leads me to believe that
it returns bad data. If I try to return trigdata->tg_trigtuple or
TempTuple using TupleGetDatum it fails at insert time with 'server closed
the connection unexpectedly ...'.

I can't see how my code doesn't match the prototypes, hopefully it's a
dumb error that someone can point out to me quickly ... :)

+  //Trigger to add hashes to rows on insert
+
+  #include "postgres.h"
+  #include "funcapi.h"
+  #include "executor/spi.h"
+  #include "commands/trigger.h"
+  #include <openssl/sha.h>
+
+  extern Datum hashtrigger(PG_FUNCTION_ARGS);
+
+  //Field number of versioning data
+  #define VERSIONFIELD 5
+  #define WHOLEHASHFIELD 36
+  #define ETLHASHFIELD 37
+
+  PG_FUNCTION_INFO_V1(hashtrigger);
+
+  Datum hashtrigger(PG_FUNCTION_ARGS) {
+   TriggerData *trigdata = (TriggerData *) fcinfo->context;
+   HeapTuple TempTuple;
+   TupleTableSlot *TupleSlot;
+   TupleDesc TupleDescription;
+   int Looper;
+
+   int Oops = SPI_connect();
+
+   static char *hexdigits = "0123456789abcdef";
+   static int HashColumns[] = {6, 7};
+   static int ColumnsAlteredVersioning[] = {1, 5, 6, 7};
+   //Suggested change here - insert checks for if this is null and only
do the first operation of it is
+   static int ColumnsAlteredETL[] = {1, 3, 5, 6, 7};
+
+   // make sure it's called as a trigger at all
+   if (!CALLED_AS_TRIGGER(fcinfo))
+    elog(ERROR, "hashtrigger: not called by trigger manager");
+
+   //palloc memory for hashes
+   char **hashes = (char **) palloc(4 * sizeof(char *));
+   //The hashes will be put into the 20 arrays, and then expanded into th
40s into hex
+   hashes[0] = (char *) palloc(20 * sizeof(char));
+   hashes[1] = (char *) palloc(40 * sizeof(char));
+   hashes[2] = (char *) palloc(40 * sizeof(char));
+
+   //Palloc array values for nulling out unused fields in tuple
+   Datum *NewColumnValues = (Datum *) palloc(sizeof(ColumnsAlteredETL) /
sizeof(int) * sizeof(Datum));
+
+   //Since I cant get the nulls parameter of SPI_modifytuple to work it
appears we're going to have to overwrite all the entries we want null
+   for (Looper = 0; Looper < (sizeof(ColumnsAlteredETL) / sizeof(int));
Looper++) {
+    NewColumnValues[Looper] = PointerGetDatum("");
+   }
+
+   //Use SPI_modifytuple to null out versioning field
+   TempTuple = SPI_modifytuple(trigdata->tg_relation,
trigdata->tg_trigtuple, sizeof(ColumnsAlteredVersioning) / sizeof(int),
ColumnsAlteredVersioning, NewColumnValues, NULL);
+
+   //Determine hash of TempTuple->t_len bytes at pointer
TempTuple->t_data and store the first element of allocated array
+   SHA1((const unsigned char *) TempTuple->t_data, TempTuple->t_len,
hashes[0]);
+   for (Looper = 0; Looper < 20; Looper++) {
+    //Spread hash out into hex coded form
+    hashes[1][Looper * 2] = hexdigits[(hashes[0][Looper] >> 4) & 0x0f];
+    hashes[1][(Looper * 2) + 1] = hexdigits[hashes[0][Looper] & 0x0f];
+   }
+
+   //Display hash for debug
+   elog (INFO, "hashtrigger returns %s for versionless hash of row",
hashes[1]);
+
+   //Use SPI_modifytuple to null out non etl fields
+   TempTuple = SPI_modifytuple(trigdata->tg_relation,
trigdata->tg_trigtuple, sizeof(ColumnsAlteredETL) / sizeof(int),
ColumnsAlteredETL, NewColumnValues, NULL);
+
+   //Determine hash of TempTuple->t_len bytes at pointer
TempTuple->t_data and store the first element of allocated array
+   SHA1((const unsigned char *) TempTuple->t_data, TempTuple->t_len,
hashes[0]);
+   for (Looper = 0; Looper < 20; Looper++) {
+    //Spread hash out into hex coded form
+    hashes[2][Looper * 2] = hexdigits[(hashes[0][Looper] >> 4) & 0x0f];
+    hashes[2][(Looper * 2) + 1] = hexdigits[hashes[0][Looper] & 0x0f];
+   }
+
+   //Display hash for debug
+   elog (INFO, "hashtrigger returns %s for material hash of row",
hashes[2]);
+
+   //Use SPI_modifytuple to write hash into fields on record into result
tuple
+  // NewColumnValues[0] = PointerGetDatum(hashes[1]);
+  // NewColumnValues[1] = PointerGetDatum(hashes[2]);
+
+   TempTuple = SPI_modifytuple(trigdata->tg_relation,
trigdata->tg_trigtuple, 2, HashColumns, NewColumnValues, NULL);
+
+   //Free allocated memory
+
+   //Return result tuple
+   TupleSlot = TupleDescGetSlot(trigdata->tg_relation->rd_att);
+  // return TupleGetDatum(TupleDestination, TempTuple);
+   return TupleGetDatum(TupleSlot, trigdata->tg_trigtuple);
+  // return PointerGetDatum(trigdata->tg_trigtuple);
+  }

Thank you very much

Alastair "Bell" Turner

Responses

pgsql-interfaces by date

Next:From: Michael MeskesDate: 2004-06-27 12:33:03
Subject: Re: ECPG scoping
Previous:From: G. van WieringenDate: 2004-06-23 06:23:16
Subject: Re: Borland PQexec error in Windows XP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group