Re: Error: rows returned by function are not all of the same row type

I took at cursory look at your function I didn't notice anything obviously
wrong. I have, however, encountered a similar problem with my own C code in
the past. It would work fine for some number of rows and then not.

I don't recall the precise bug. The broad stroke was that I was assuming a
memory location would be static from one call to the next. It was for a
while but at some point, postgres would move it. I had to be sure to get
the location from the appropriate macro with each call instead of simply
retaining my own point from one call to the next.

I don't know if that may be the problem you are encountering.
           -Ed

On Thu, Jul 4, 2019 at 5:36 AM <[email protected]> wrote:

> Hi, everyone,
>
> I have written C-language function that returns
> multiple composite rows.
>
> Generally function works as expected, but sometimes problem takes place.
> At  rough  guess  the  problem  occurs  when  number of returning rows
> relatively large (more than 100K - 1M).
>
> I have added some checkpoints.
> P5 and P6 are present in snippet.
>
> The  function  always  reaches checkpoint P5, but before P6 it returns
> error:
>
> "rows returned by function are not all of the same row type"
>
> Supposedly, at some iteration on SRF_RETURN_NEXT
>
> Any ideas?
>
> Below is a snippet of code:
>
> #include "postgres.h"
> #include "funcapi.h"
> #include "executor/spi.h"
>
> Datum my_func(PG_FUNCTION_ARGS);
>
> PG_FUNCTION_INFO_V1(show_eudc);
>
> Datum
> my_eudc(PG_FUNCTION_ARGS)
> {
>   FuncCallContext     *funcctx;
>   int                  call_cntr;
>   int                  max_calls;
>   TupleDesc   tupleDesc;
>
>   /* Build a tuple descriptor for our result type */
>   if(get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
>   {
>     ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("Function returning record called in context that cannot accept type
> record")));
>   }
>
>   if(SRF_IS_FIRSTCALL())
>   {
>     MemoryContext oldcontext;
>     funcctx = SRF_FIRSTCALL_INIT();
>     oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>
>     My_SPI_call_context ctx;
>     memset(&ctx, 0, sizeof(My_SPI_call_context));
>
>     int ret;
>
>     /* Connect to SPI manager */
>     if((ret = SPI_connect()) < 0)
>     {
>       /* internal error */
>       elog(ERROR, "spi_match: SPI_connect returned %d", ret);
>       SPI_finish();
>       PG_RETURN_VOID();
>     }
>
>     /* some setup code */
>
>     const char* stSQLDef_0[1] = {
>     "CREATE TEMPORARY TABLE results (v1 BIGINT NOT NULL, v2 INTEGER NOT
> NULL)",
>     };
>
>     for(int k=0; k<1; k++)
>     {
>       ret = SPI_exec(stSQLDef_0[k], 0);
>       if(ret != SPI_OK_UTILITY)
>       {
>         elog(ERROR, "SPI_exec (0)-(%d) returned %d", k, ret);
>         my_spi_free_context(&ctx);
>         PG_RETURN_VOID();
>       }
>     }
>
>     /* many code */
>
>     const char* stSQLResultsInsert = "INSERT INTO results (v1, v2) VALUES
> (%ld, %d)";
>
>     for(int k=0; k<N; k++)
>     {
>       memset(ctx.stSQL, 0, SQL_BUFFER_LENGTH * sizeof(char));
>       sprintf(ctx.stSQL, stSQLResultsInsert, v1, v2);
>       ret = SPI_exec(ctx.stSQL, 0);
>       proc_0 = SPI_processed;
>       if(ret != SPI_OK_INSERT || proc_0 <= 0)
>       {
>         elog(ERROR, "spi_match: SPI_execute (8_H) returned %d", ret);
>         my_spi_free_context(&ctx);
>         PG_RETURN_VOID();
>       }
>     }
>
>     /* some code with aggregation of data from TEMP TABLE results */
>
>     memset(ctx.stSQL, 0, SQL_BUFFER_LENGTH * sizeof(char));
>     sprintf(ctx.stSQL, "SELECT v1, v2 FROM results");
>     ret = SPI_execute(ctx.stSQL, false, 0);
>     proc = SPI_processed;
>
>     ereport(NOTICE, (errmsg("P5: [%s]-(%d)", (const char*)__FUNCTION__,
> proc)));
>
>     if(ret != SPI_OK_SELECT || proc <= 0)
>     {
>       funcctx->max_calls = 0;
>       funcctx->user_fctx = NULL;
>       if(proc <= 0) ereport(NOTICE, (errmsg("SPI_execute (10) returned
> %d", ret)));
>     }
>     else if(proc)
>     {
>       spi_tuptable = SPI_tuptable;
>
>       funcctx->max_calls = proc;
>       funcctx->user_fctx = spi_tuptable;
>     }
>
>     my_spi_free_context(&ctx);
>
>     tupleDesc = BlessTupleDesc(tupleDesc);
>     funcctx->tuple_desc = tupleDesc;
>     MemoryContextSwitchTo(oldcontext);
>
>   }
>
>   funcctx = SRF_PERCALL_SETUP();
>
>   call_cntr = funcctx->call_cntr;
>   max_calls = funcctx->max_calls;
>
>   if(call_cntr < max_calls)
>   {
>     SPITupleTable* table = (SPITupleTable*)funcctx->user_fctx;
>     Datum       results; /* Results tuple */
>     Datum       column[2];
>     bool        isColumnNull[2];
>     HeapTuple   tuple;
>     int m;
>
>     if(table)
>     {
>       for(m=0; m<2; m++)
>       {
>         column[m] = SPI_getbinval(table->vals[call_cntr], table->tupdesc,
> m+1, &isColumnNull[m]);
>       }
>
>       tuple = heap_form_tuple(funcctx->tuple_desc, column, isColumnNull);
>       results = HeapTupleGetDatum(tuple);
>       SRF_RETURN_NEXT(funcctx, results);
>     }
>   }
>   else
>   {
>     int ret;
>     SPITupleTable* table = (SPITupleTable*)funcctx->user_fctx;
>     if(table)
>     {
>       SPI_freetuptable(table);
>     }
>
>     ereport(NOTICE, (errmsg("P6: [%s]-(%d)", (const char*)__FUNCTION__,
> max_calls)));
>
>     ret = SPI_exec("DROP TABLE results", 0);
>     if(ret != SPI_OK_UTILITY)
>     {
>       elog(ERROR, "spi_match: SPI_exec (20) returned %d", ret);
>     }
>
>     SPI_finish();
>     SRF_RETURN_DONE(funcctx);
>   }
>
>   PG_RETURN_VOID();
> }
>
> --
> Best regards,
>
> Andrey Sychev
>
> [email protected]
>
>
>
>