Re: Trouble with bytea in SPI... - Mailing list pgsql-general

First at all, thanks a lot for your help with my trouble, it was very
helpfull...

This is my complete code:

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"

#include <gsl/gsl_rng.h>

PG_FUNCTION_INFO_V1(myspi);

Datum
myspi(PG_FUNCTION_ARGS)
{
 int ret;
 bool isnull;

 bytea *val;
 void *plan;
 Oid *karg;
 Datum newval[1];

 // -- La parte de numeros aleatorios va aki
 gsl_rng_type *T;
 gsl_rng *r;

 void *stat;
 int res;
 int num;

 num =  PG_GETARG_INT(0);
 if(PG_ARGISNULL(0)) {
  PG_RETURN_NULL();
 }

 gsl_rng_env_setup();
 T = gsl_rng_default;
 r = gsl_rng_alloc(T);

 ret = SPI_connect();
 karg = (Oid *) palloc(sizeof(Oid));

 ret = SPI_exec("SELECT st FROM rng_seeds", 1);
 if (ret == SPI_OK_SELECT && SPI_processed > 0) {
    TupleDesc tupdesc = SPI_tuptable->tupdesc;
    SPITupleTable *tuptable = SPI_tuptable;

    val = DatumGetByteaP(SPI_getbinval(tuptable->vals[0], tupdesc, 1,
&isnull));
    karg[0] = SPI_gettypeid(tupdesc, 1);
 }

 stat = r->state;
 memcpy(stat, VARDATA(val), gsl_rng_size(r));
 res = (int) gsl_rng_uniform_int(r, num);

 /* Aki retorno el valor modificado a su respectiva celda */
 memcpy(VARDATA(val), stat, gsl_rng_size(r));
 plan = SPI_prepare("UPDATE rng_seeds SET st=$1", 1, karg);
 if (!plan)
  elog(ERROR, "I don't know what happened!");
 plan = SPI_saveplan(plan);

 newval[0] = PointerGetDatum(val);
 ret = SPI_execp(plan, newval, NULL, 0);

 SPI_finish();
 gsl_rng_free(r);

 PG_RETURN_INT32(res);
}

And thanks to all of you it works as expected, the theory behind this is the
following:
gsl random library has a lot different kind of random number generators and
support for some random distributions, so I decide to implement it for a
project I've been working on. I can store the "state" of a random number to
use it to generate the next one. The state is a segment of the memory and it
is stored in a bytea field, so I decided to create a table and in the future
add a name field and handle it as a sequence (ala nextrandval('name'))...

Right now it works just with one field and I guess it is working well, but I
am very worried about the performance of SPI_execute() and SPI_execp(). I
read in the Developer FAQ something about accessing the data directly from
the backend code. If it is like this I would like to get more infor about
how to use SearchSysCache() and heap_beginscan().

Do you think I need to implement such thing to improve performance? any idea
in how to improve my approach to this trouble?

Thanks a lot for your answer!


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: "Cristian Prieto" <cristian@clickdiario.com>;
<pgsql-general@postgresql.org>
Sent: Thursday, September 01, 2005 9:51 PM
Subject: Re: [GENERAL] Trouble with bytea in SPI...


> Michael Fuhr <mike@fuhr.org> writes:
>> On Thu, Sep 01, 2005 at 08:23:31PM -0600, Cristian Prieto wrote:
>>> Hello, I've been working just a little with SPI in a few stored
>>> functions, this is a model of my SP:
>
>> Please post a real example instead of a "model."
>
> Also, it's good to make at least some minimal effort with gdb to find
> out where your code is crashing.  A backtrace from the core dump
> (or from catching the signal interactively) often tells a lot.
>
> regards, tom lane