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 <andrey(dot)sychev(at)cifrasoft(dot)com> 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
>
> andrey(dot)sychev(at)cifrasoft(dot)com
>
>
>
>