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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

andrey.sychev
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 when
number of returning rows relatively large, sometimes
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

[hidden email]



Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Andrey Sychev <[hidden email]> writes:
> 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 do not think it's valid to return from your function with the SPI
context still open.  Probably, it seems to accidentally sort of work
as long as you don't return enough rows to cause the outer query to
do anything interesting like spill to disk.

Probably you should re-code this to execute just once and return
a tuplestore.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

andrey.sychev
Thank you very much for answering my question, Tom.
Yes,  I  have  always  assumed  that  returning  from function without
calling  SPI_freetuptable  is not good idea, but I do not know another
way to achieve same result.

As  I  am not expert in PostgreSQL internals and mostly work according
to official documentation, I never know about tuplestore before.

This look like pretty interesting idea.

I   have   searched   for   tuplestore   over  Internet and found some
topics, relative to subject:

1. https://postgres.cz/wiki/Iter%C3%A1tor_pole
2. https://www.postgresql.org/message-id/1073862553.1475.93.camel%40jeff
3.
https://www.postgresql.org/message-id/753432.21663.qm%40web65511.mail.ac4.yahoo.com
(with your comment)
4. http://web.mit.edu/ghudson/trac/attic/src/postgresql-7.4.5/contrib/tablefunc/tablefunc.c

I have examined this topics briefly and got some questions:

1. As I understand there are tests for SFRM_Materialize in code above.
In  my  case  a  caller  of my function is PL/pgSQL  procedure.
Does it accept this returning mode?

2. Our current production server versions is 9.1 and 9.6.
Do this versions support returning of tuplestore?

3. Currently my function defined as "RETURNS SETOF".
Does  definition  of  the function need to be changed if I rewrite code to
return tuplestore?


> Andrey Sychev <[hidden email]> writes:
>> 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 do not think it's valid to return from your function with the SPI
> context still open.  Probably, it seems to accidentally sort of work
> as long as you don't return enough rows to cause the outer query to
> do anything interesting like spill to disk.

> Probably you should re-code this to execute just once and return
> a tuplestore.

>                         regards, tom lane

--
Best regards,

Andrey Sychev

[hidden email]



Reply | Threaded
Open this post in threaded view
|

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

Joe Conway
On 7/5/19 5:39 AM, Andrey Sychev wrote:
> Thank you very much for answering my question, Tom.
> Yes,  I  have  always  assumed  that  returning  from function without
> calling  SPI_freetuptable  is not good idea, but I do not know another
> way to achieve same result.


Please do not top post on the Postgres lists.


> 1. As I understand there are tests for SFRM_Materialize in code above.
> In  my  case  a  caller  of my function is PL/pgSQL  procedure.
> Does it accept this returning mode?


See contrib/tablefunc/tablefunc.c crosstab_hash() as an example.

> 2. Our current production server versions is 9.1 and 9.6.
> Do this versions support returning of tuplestore?

Works since Postgres 7.3 if I recall correctly.

> 3. Currently my function defined as "RETURNS SETOF".
> Does  definition  of  the function need to be changed if I rewrite code to
> return tuplestore?

No

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

andrey.sychev

> Please do not top post on the Postgres lists.

Thank  you  for  the advice. I will take into account this rule in the
future.

> See contrib/tablefunc/tablefunc.c crosstab_hash() as an example.

Thank you. I hope this will be good starting point for me.

--
Best regards,

Andrey Sychev

 mailto:[hidden email]