agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: andrey.sychev@cifrasoft.com
To: pgsql-sql@postgresql.org
Subject: Error: rows returned by function are not all of the same row type
Date: Thu, 4 Jul 2019 13:35:58 +0400
Message-ID: <1208914147.20190704133558@cifrasoft.com> (raw)

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.sychev@cifrasoft.com

view thread (2+ messages)  latest in thread

Message-ID: <1208914147.20190704133558@cifrasoft.com>
Permalink:  ../1208914147.20190704133558@cifrasoft.com/
Also on:    postgresql.org/message-id/1208914147.20190704133558@cifrasoft.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: andrey.sychev@cifrasoft.com
  Subject: Re: Error: rows returned by function are not all of the same row type
  In-Reply-To: <1208914147.20190704133558@cifrasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox