Thread: Trouble with bytea in SPI...

Trouble with bytea in SPI...

From
"Cristian Prieto"
Date:
Hello, I've been working just a little with SPI in a few stored functions, this is a model of my SP:
 
PG_FUNCTION_INFO_V1(myspi);
 
Datum
myspi(PG_FUNCTION_ARGS)
{
    bool isnull;
    bytea *val;
    Oid *karg;
    void *plan;
    int res;
 
    ret = SPI_connect();
    karg = (Oid *) palloc(sizeof(Oid));
    ret = SPI_exec("SELECT bytevalue FROM table1", 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);
    }
 
    // Here I use and modified the val bytea value, but it stills as a bytea kind of data
 
    // Now I need to return the bytea value to the table
 
    plan = SPI_prepare("UPDATE table1 SET bytevalue=$1", 1, karg);
    if(!plan)
        elog(ERROR, "I don't know what happened!");
    plan = SPI_saveplan(plan);
 
    // This is where the SP and the connection dies!
    ret = SPI_execp(plan, val, NULL, 1);
 
    SPI_finnish();
    PG_RETURN_INT32(1);
}
 
Well, the funcion is compiled and it loads correctly using
 
create or replace function myspi() returns integer as '/usr/lib/pgsql/myspi.so' language 'c';
 
but when I did a
 select myspi();
 
I get the non-friendly message "The server closed the connection unexpectedly..." blah blah blah... and the !> indicator...
 
 
I checked out all the contrib dir (contrib/fulltextindex/fti.c, contrib/tsearch2/dict.c, and similar) and I guess my code is similar to them in that area, what did I do wrong???
 
Thanks a lot for your help!!!

Re: Trouble with bytea in SPI...

From
Michael Fuhr
Date:
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

Re: Trouble with bytea in SPI...

From
Tom Lane
Date:
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

Re: Trouble with bytea in SPI...

From
"Cristian Prieto"
Date:
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


Re: Trouble with bytea in SPI...

From
Alvaro Herrera
Date:
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)