Thread: BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

From

Date:

14 February 2011, 10:40:42

The following bug has been logged online:

Bug reference:      5883
Logged by:
Email address:      vegard.bones@met.no
PostgreSQL version: 8.4.7
Operating system:   ubuntu lucid
Description:        Error when mixing SPI_returntuple with returning regular
HeapTuple
Details:

When creating a server-side C function, things go wrong when I (in the same
function) return some results via SPI_returntuple, and other results by
manually creating HeapTuples. This applies even if the source for both
returns are the same data in the same table.

I get an error message saying "rows returned by function are not all of the
same row type". In the attached example I would have expected to see the
same row twice.

Everything works as I expect if I try to do the same and stick to using only
one of the alternatives.


Example code:

SQL:

CREATE TABLE test (a int, b int);
INSERT INTO test VALUES (1, 2);
CREATE FUNCTION run_test() RETURNS SETOF test AS 'SOMEWHERE/something.so',
'run_test' LANGUAGE C VOLATILE;

SELECT * FROM run_test();


C:

#include <postgres.h>
#include <fmgr.h>
#include <funcapi.h>
#include <executor/spi.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

static const char * query = "SELECT a, b FROM test";

static void get_data_alternative_a(Datum * data_out, bool * isnull)
{
    SPI_execute(query, true, 1);
    data_out[0] = SPI_getbinval(* SPI_tuptable->vals, SPI_tuptable->tupdesc, 1,
& isnull[0]);
    data_out[1] = SPI_getbinval(* SPI_tuptable->vals, SPI_tuptable->tupdesc, 2,
& isnull[1]);
}

static Datum get_data_alternative_b()
{
    SPI_execute(query, true, 1);
    HeapTupleHeader ret = SPI_returntuple(* SPI_tuptable->vals,
SPI_tuptable->tupdesc);
    return PointerGetDatum(ret);
}

PG_FUNCTION_INFO_V1(run_test);
Datum run_test(PG_FUNCTION_ARGS)
{
    FuncCallContext * funcctx;
    int * return_count = NULL;

    if ( SRF_IS_FIRSTCALL() )
    {
        funcctx = SRF_FIRSTCALL_INIT();
        SPI_connect();

        MemoryContext oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        TupleDesc tupdesc;
        if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
            ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(
                    "function returning record called in context "
                        "that cannot accept type record")));

        funcctx->tuple_desc = BlessTupleDesc(tupdesc);

        return_count = (int *) palloc(sizeof(int));
        MemoryContextSwitchTo(oldcontext);

        * return_count = 0;
        funcctx->user_fctx = (void*) return_count;
    }
    funcctx = SRF_PERCALL_SETUP();
    return_count = (int *) funcctx->user_fctx;


    Datum ret[2];
    bool isnull[2];
    switch ( (* return_count) ++ )
    {
    case 0:
//        SRF_RETURN_NEXT(funcctx, get_data_alternative_b());
        get_data_alternative_a(ret, isnull);
        break;
    case 1:
        SRF_RETURN_NEXT(funcctx, get_data_alternative_b());
//        get_data_alternative_a(ret, isnull);
        break;
    default:
        SPI_finish();
        SRF_RETURN_DONE(funcctx);
    }
    HeapTuple heap_tuple = heap_form_tuple(funcctx->tuple_desc, ret, isnull);
    Datum packed_ret = HeapTupleGetDatum(heap_tuple);
    SRF_RETURN_NEXT(funcctx, packed_ret);
}

Re: BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

From

Tom Lane

Date:

14 February 2011, 15:39:31

"" <vegard.bones@met.no> writes:
> When creating a server-side C function, things go wrong when I (in the same
> function) return some results via SPI_returntuple, and other results by
> manually creating HeapTuples. This applies even if the source for both
> returns are the same data in the same table.

I think the part of this example that doesn't work is returning from the
function without having exited your SPI context.  If you need to produce
multiple rows from a single SPI query, the best solution is to collect
them all during a single call and return them in a tuplestore.  You can
*not* return without having called SPI_finish to balance your
SPI_connect.

            regards, tom lane

Re: BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

From

Vegard Bønes

Date:

15 February 2011, 06:59:49

Ok, I tried to modify the example functions get_data_alternative_a() and get_data_alternative_b(), so that they start
withSPI_connect, and end with SPI_finish. Also I removed SPI_connect and SPI_finish from the main function. As I
understandyou, that should have solved the problem. The result, however, is exactly the same as before. 

Please keep in mind that I can call either alternative a or alternative b as many times as I wish during the same sql
functioncall, but I can not mix them. 

VG

----- Original Message -----
Fra: "Tom Lane" <tgl@sss.pgh.pa.us>
Til: "vegard bones" <vegard.bones@met.no>
Kopi: pgsql-bugs@postgresql.org
Sendt: 14. februar 2011 17:39:07
Emne: Re: [BUGS] BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

"" <vegard.bones@met.no> writes:
> When creating a server-side C function, things go wrong when I (in the same
> function) return some results via SPI_returntuple, and other results by
> manually creating HeapTuples. This applies even if the source for both
> returns are the same data in the same table.

I think the part of this example that doesn't work is returning from the
function without having exited your SPI context.  If you need to produce
multiple rows from a single SPI query, the best solution is to collect
them all during a single call and return them in a tuplestore.  You can
*not* return without having called SPI_finish to balance your
SPI_connect.
        regards, tom lane

Re: BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

From

Robert Haas

Date:

17 February 2011, 00:41:56

On Tue, Feb 15, 2011 at 2:43 AM, Vegard B=F8nes <vegard.bones@met.no> wrote:
> Ok, I tried to modify the example functions get_data_alternative_a() and =
get_data_alternative_b(), so that they start with SPI_connect, and end with=
 SPI_finish. Also I removed SPI_connect and SPI_finish from the main functi=
on. As I understand you, that should have solved the problem. The result, h=
owever, is exactly the same as before.

You may get further if you post the modified code, instead of simply
asserting that some code we can't see doesn't work.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

From

Vegard Bønes

Date:

17 February 2011, 06:58:16

The updated example is pasted in below.

The given example will cause an error. You may (for example) change the code in the stwitch statement like this, and it
willwork like a charm: 

from:case 1:    SRF_RETURN_NEXT(funcctx, get_data_alternative_b());    //get_data_alternative_a(ret, isnull);
to:case 1:    //SRF_RETURN_NEXT(funcctx, get_data_alternative_b());    get_data_alternative_a(ret, isnull);


VG



#include <postgres.h>
#include <fmgr.h>
#include <funcapi.h>
#include <executor/spi.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/** CREATE TABLE test (a int, b int);* INSERT INTO test VALUES (1, 2);* CREATE FUNCTION run_test() RETURNS SETOF test
AS'SOMEWHERE/something.so', 'run_test' LANGUAGE C VOLATILE;** SELECT * FROM run_test();** ERROR:  rows returned by
functionare not all of the same row type**/ 

static const char * query = "SELECT a, b FROM test";

static void get_data_alternative_a(Datum * data_out, bool * isnull)
{SPI_connect();SPI_execute(query, true, 1);data_out[0] = SPI_getbinval(* SPI_tuptable->vals, SPI_tuptable->tupdesc, 1,
&isnull[0]);data_out[1] = SPI_getbinval(* SPI_tuptable->vals, SPI_tuptable->tupdesc, 2, & isnull[1]);SPI_finish(); 
}

static Datum get_data_alternative_b()
{SPI_connect();SPI_execute(query, true, 1);HeapTupleHeader ret = SPI_returntuple(* SPI_tuptable->vals,
SPI_tuptable->tupdesc);Datumd = PointerGetDatum(ret);SPI_finish();return d; 
}

PG_FUNCTION_INFO_V1(run_test);
Datum run_test(PG_FUNCTION_ARGS)
{FuncCallContext * funcctx;int * return_count = NULL;
if ( SRF_IS_FIRSTCALL() ){    funcctx = SRF_FIRSTCALL_INIT();    //SPI_connect();
    MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);    TupleDesc tupdesc;    if
(get_call_result_type(fcinfo,NULL, &tupdesc) != TYPEFUNC_COMPOSITE)        ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),errmsg(                "function returning record called in context "
        "that cannot accept type record"))); 
    funcctx->tuple_desc = BlessTupleDesc(tupdesc);
    return_count = (int *) palloc(sizeof(int));    MemoryContextSwitchTo(oldcontext);
    * return_count = 0;    funcctx->user_fctx = (void*) return_count;}funcctx = SRF_PERCALL_SETUP();return_count = (int
*)funcctx->user_fctx; 

Datum ret[2];bool isnull[2];switch ( (* return_count) ++ ){case 0:    //SRF_RETURN_NEXT(funcctx,
get_data_alternative_b());   get_data_alternative_a(ret, isnull);    break;case 1:    SRF_RETURN_NEXT(funcctx,
get_data_alternative_b());   //get_data_alternative_a(ret, isnull);    break;default:
SRF_RETURN_DONE(funcctx);}HeapTupleheap_tuple = heap_form_tuple(funcctx->tuple_desc, ret, isnull);Datum packed_ret =
HeapTupleGetDatum(heap_tuple);SRF_RETURN_NEXT(funcctx,packed_ret); 
}



----- Original Message -----
Fra: "Robert Haas" <robertmhaas@gmail.com>
Til: "Vegard Bønes" <vegard.bones@met.no>
Kopi: "Tom Lane" <tgl@sss.pgh.pa.us>, pgsql-bugs@postgresql.org
Sendt: 17. februar 2011 02:41:47
Emne: Re: [BUGS] BUG #5883: Error when mixing SPI_returntuple with returning regular HeapTuple

On Tue, Feb 15, 2011 at 2:43 AM, Vegard Bønes <vegard.bones@met.no> wrote:
> Ok, I tried to modify the example functions get_data_alternative_a() and get_data_alternative_b(), so that they start
withSPI_connect, and end with SPI_finish. Also I removed SPI_connect and SPI_finish from the main function. As I
understandyou, that should have solved the problem. The result, however, is exactly the same as before. 

You may get further if you post the modified code, instead of simply
asserting that some code we can't see doesn't work.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company