Thread: SPI example does not work for 7.1beta4

Folks,

I am learning and using SPI. In PostgreSQL documentation chapter "Server
Programming Interface," there is a small example name "execq(text,
int)".

This example works as the document says on 7.0.3 and earlier version,
but this example DOES NOT work on my 7.1 beta4.

This is the error message I got.  I wonder does anyone test your SPI
functions on 7.1 beta?  Does anyone notice the problem I got here?
I know I should test this under beta6 before I post this message, but my
co-worker's anti-virus s/w gave him a virus warning message after him
finished the download of 7.1beta6 .gz!  :-)

------------
db1=# select execq('create table a (x int4)', 0);
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
------------

Thanks for your help

--
LM Liu

Limin Liu <limin@pumpkinnet.com> writes:
> I am learning and using SPI. In PostgreSQL documentation chapter "Server
> Programming Interface," there is a small example name "execq(text,
> int)".
> This example works as the document says on 7.0.3 and earlier version,
> but this example DOES NOT work on my 7.1 beta4.

Hm.  textout() can't be called that way anymore --- as indeed your compiler
should have told you, if it's any good at all.  I get

execq.c: In function `execq':
execq.c:13: warning: passing arg 1 of `textout' from incompatible pointer type
execq.c:13: warning: passing arg 1 of `SPI_exec' makes pointer from integer without a cast

Looks like the example is in need of update.  Thanks for the report.

            regards, tom lane

Thanks Tom,

By the way, did you change the whole archetecture of SPI invocation?

I put
---
   SPI_connect();
   SPI_exec("create temp table tbl_tmp (n int);",0);
   SPI_exec("insert into tbl_tmp values (1);",0);
   SPI_finish();
---
after InitPostgres and before setsigjmp().
This works perfectly in my 7.03 and earlier version.  That means, after authentication, we
will have a temp table per each db connection.  User can use the client program to "select"
or "insert" upon this temp table.

However, the same code does not work in 7.1.  Actually, I got some messages contradicting to
each other:
---
db1=> select * from tbl_tmp;
ERROR:  Relation 'tbl_tmp' does not exist
db1=> create temp table tbl_tmp (n int);
ERROR:  Relation 'tbl_tmp' already exists
db1=>
---

Can you please give us some hints on what's going on here?

Thanks

Tom Lane wrote:

> Limin Liu <limin@pumpkinnet.com> writes:
> > I am learning and using SPI. In PostgreSQL documentation chapter "Server
> > Programming Interface," there is a small example name "execq(text,
> > int)".
> > This example works as the document says on 7.0.3 and earlier version,
> > but this example DOES NOT work on my 7.1 beta4.
>
> Hm.  textout() can't be called that way anymore --- as indeed your compiler
> should have told you, if it's any good at all.  I get
>
> execq.c: In function `execq':
> execq.c:13: warning: passing arg 1 of `textout' from incompatible pointer type
> execq.c:13: warning: passing arg 1 of `SPI_exec' makes pointer from integer without a cast
>
> Looks like the example is in need of update.  Thanks for the report.
>
>                         regards, tom lane

--
LM Liu

Limin Liu <limin@pumpkinnet.com> writes:
> By the way, did you change the whole archetecture of SPI invocation?

No, I told you: what's broken here is the textout() call.  Attached
is the updated example.

> I put
> ---
>    SPI_connect();
>    SPI_exec("create temp table tbl_tmp (n int);",0);
>    SPI_exec("insert into tbl_tmp values (1);",0);
>    SPI_finish();
> ---
> after InitPostgres and before setsigjmp().

I doubt this will work correctly without a transaction around it ...

            regards, tom lane


#include "executor/spi.h"   /* this is what you need to work with SPI */

int execq(text *sql, int cnt);

int
execq(text *sql, int cnt)
{
    char *query;
    int ret;
    int proc;

    /* Convert given TEXT object to a C string */
    query = DatumGetCString(DirectFunctionCall1(textout,
                                                PointerGetDatum(sql)));

    SPI_connect();

    ret = SPI_exec(query, cnt);

    proc = SPI_processed;
    /*
     * If this is SELECT and some tuple(s) fetched -
     * returns tuples to the caller via elog (NOTICE).
     */
    if ( ret == SPI_OK_SELECT && SPI_processed > 0 )
    {
        TupleDesc tupdesc = SPI_tuptable->tupdesc;
        SPITupleTable *tuptable = SPI_tuptable;
        char buf[8192];
        int i,j;

        for (j = 0; j < proc; j++)
        {
            HeapTuple tuple = tuptable->vals[j];

            for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
                sprintf(buf + strlen (buf), " %s%s",
                        SPI_getvalue(tuple, tupdesc, i),
                        (i == tupdesc->natts) ? " " : " |");
            elog (NOTICE, "EXECQ: %s", buf);
        }
    }

    SPI_finish();

    pfree(query);

    return (proc);
}