Re: SPI problem

From: "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr>
To: "Alastair Bell Turner" <bell(at)tangent(dot)co(dot)za>, <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: SPI problem
Date: 2004-06-29 22:54:46
Message-ID: 002101c45e2c$2ce2ce90$0b89bfd5@darko
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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(at)tangent(dot)co(dot)za>
To: <pgsql-interfaces(at)postgresql(dot)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
>

In response to

  • SPI problem at 2004-06-23 14:48:01 from Alastair Bell Turner

Browse pgsql-interfaces by date

  From Date Subject
Next Message Greg Markham 2004-06-30 00:04:57 Embedded SQL timestamp Question (ECPG)
Previous Message Darko Prenosil 2004-06-29 17:56:29 Re: : query using kylix (delphi)