Thread: SPI_execute_with_args call

On 03/05/13 21:19, Yuriy Rusinov wrote:

Hello, colleagues !

I have to write random number generator state into database table

Table structure is 

table rand_state

{

    id serial not null primary key,

    state_rand bytea

};

In C-function I do

size_t nr_ins = strlen ("insert into rand_state (state_rand) values ($1);");

char * r_sql = (char *) palloc (nr_ins + 1);

strncpy (r_sql, "insert into rand_state (state_rand) values ($1);", nr_ins);

Oid * oids = (Oid *)palloc (sizeof (Oid));

Datum * val = PointerGetDatum (randBuf);

*oids = BYTEAOID;

const char * nulls = "NULL";

int rins = SPI_execute_with_args (r_sql, 1, oids, val, nulls, false, 1);

randBuf is a void * pointer that contains random number generator state,

when I try to execute SPI_execute_with_args (r_sql, 1, oids, val, nulls, false, 1); I receive error 

The connection to the server was lost. Attempting reset: Failed.

Could you give some work examples for SPI_execute_with_args because I didn't find them in documentation. 

Thanks a lot.

--
Best regards,
Sincerely yours,
Yuriy Rusinov.

I can't answer your question.

However, I can say that PRIMARY KEY implies NOT NULL (also an UNIQUE index), so you don't need to explicitly add NOT NULL when you are specifying PRIMARY KEY!


Cheers,
Gavin

Yuriy Rusinov <yrusinov@gmail.com> writes:
> In C-function I do
> size_t nr_ins = strlen ("insert into rand_state (state_rand) values ($1);");
> char * r_sql = (char *) palloc (nr_ins + 1);
> strncpy (r_sql, "insert into rand_state (state_rand) values ($1);", nr_ins);

This is a hard, error-prone, and ultimately incorrect way to do
pstrdup() --- you're not ensuring that the new string is
null-terminated.

> Datum * val = PointerGetDatum (randBuf);

Didn't your compiler give you a warning about that?  PointerGetDatum
produces a Datum, not a pointer to a Datum.  You'd need something more
like

        Datum val[1];
        val[0] = PointerGetDatum (randBuf);

This is assuming that randBuf is even of the right format to be a bytea
value, which is unclear from your extract.

> const char * nulls = "NULL";

And that's just wrong.  Personally I'd just pass NULL to
SPI_execute_with_args since you don't have any null values to pass, but
if you don't want to do that you'd need something more like

        char nulls[1];
        nulls[0] = ' ';

(hmm, it looks like the SPI documentation leaves something to be desired
here --- the SPI_execute_with_args page, at least, isn't explaining the
convention for elements of the nulls[] array)

> Could you give some work examples for SPI_execute_with_args because I
> didn't find them in documentation.

A quick grep says there's a usage in src/pl/plpgsql/src/pl_exec.c

                        regards, tom lane

Thanks a lot, I have corrected and bug was fixed.

On Fri, May 3, 2013 at 6:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yuriy Rusinov <yrusinov@gmail.com> writes:
> In C-function I do
> size_t nr_ins = strlen ("insert into rand_state (state_rand) values ($1);");
> char * r_sql = (char *) palloc (nr_ins + 1);
> strncpy (r_sql, "insert into rand_state (state_rand) values ($1);", nr_ins);

This is a hard, error-prone, and ultimately incorrect way to do
pstrdup() --- you're not ensuring that the new string is
null-terminated.

> Datum * val = PointerGetDatum (randBuf);

Didn't your compiler give you a warning about that?  PointerGetDatum
produces a Datum, not a pointer to a Datum.  You'd need something more
like

        Datum val[1];
        val[0] = PointerGetDatum (randBuf);

This is assuming that randBuf is even of the right format to be a bytea
value, which is unclear from your extract.

> const char * nulls = "NULL";

And that's just wrong.  Personally I'd just pass NULL to
SPI_execute_with_args since you don't have any null values to pass, but
if you don't want to do that you'd need something more like

        char nulls[1];
        nulls[0] = ' ';

(hmm, it looks like the SPI documentation leaves something to be desired
here --- the SPI_execute_with_args page, at least, isn't explaining the
convention for elements of the nulls[] array)

> Could you give some work examples for SPI_execute_with_args because I
> didn't find them in documentation.

A quick grep says there's a usage in src/pl/plpgsql/src/pl_exec.c

                        regards, tom lane

--
Best regards,
Sincerely yours,
Yuriy Rusinov.