Thread: Trouble with bytea in SPI...

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."  The code you
posted fails to compile, with errors and warnings like the following:

spitest.c: In function `myspi':
spitest.c:18: `ret' undeclared (first use in this function)
spitest.c:18: (Each undeclared identifier is reported only once
spitest.c:18: for each function it appears in.)
spitest.c:39: warning: passing arg 2 of `SPI_execp' from incompatible pointer type
spitest.c:41: warning: implicit declaration of function `SPI_finnish'
spitest.c:16: warning: unused variable `res'

Since what you posted doesn't compile, it can't be what you're
really doing; that means we have to guess at what the real code
looks like.  It would be easier to help if we could see the real
thing so we don't have to guess.

>     // This is where the SP and the connection dies!
>     ret = SPI_execp(plan, val, NULL, 1);

val is a bytea * but the second argument to SPI_execp() is a Datum *
(the compiler warning hints that something's wrong here).  Try
something like this:

    Datum  values[1];

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

That works for me in simple tests.  If anybody sees a problem with
it then please make corrections.

--
Michael Fuhr

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

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

On Fri, Sep 02, 2005 at 12:17:09PM -0600, Cristian Prieto wrote:

Hey Cristian,

> 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?

While SPI does impose some overhead, code-wise it is certainly
appropiate for what you are doing.  SearchSysCache() and the like is
reserved for system catalogs, and I don't think you want to recompile
the whole of Postgres just to get some improvement there.  No need to
mention the fact that your Postgres would be incompatible with everyone
else's, and un-backup-able.  Certainly not a road I'd go.

Using heap_beginscan et al would be almost the same as using SPI.  I
doubt there's a lot of performance to be gained that way ... or maybe
there is, but you'd pay in maintenability and obscure bugs, and you'll
lose the future improvements to the optimizer, etc.

Just be sure to use VACUUM and ANALYZE appropiately, keep well defined
indexes, and you shouldn't need much else.

--
Alvaro Herrera -- Valdivia, Chile         Architect, www.EnterpriseDB.com
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them."    (Freeman Dyson)