Thread: SPI problem

SPI problem

From

"Alastair Bell" Turner ""

Date:

29 June 2004, 19:55:52

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

Re: SPI problem

From

"Darko Prenosil"

Date:

29 June 2004, 22:54:27

I had a stupid error today, because I did not call a SPI_finish on function
exit. I can't see it in Your code either. Too late for analyze code, my ayes
are closing...
Regards !

----- Original Message -----
From: "Alastair Bell Turner" <bell@tangent.co.za>
To: <pgsql-interfaces@postgresql.org>
Sent: Wednesday, June 23, 2004 4:48 PM
Subject: [INTERFACES] SPI problem


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: SPI problem

From

Tom Lane

Date:

30 June 2004, 03:04:30

"Alastair Bell" Turner "" <bell@tangent.co.za> writes:
> I'm trying to build a c trigger with the Server Programming Interface and
> not having a lot of joy.

I think what you need to return from a trigger is PointerGetDatum() of a
HeapTuple.  TupleGetDatum is a different animal that is *not* used for
trigger results, for mostly-historical reasons.

There are several working examples in the contrib/ modules.
        regards, tom lane