agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedError: rows returned by function are not all of the same row type
2+ messages / 2 participants
[nested] [flat]
* Error: rows returned by function are not all of the same row type
@ 2019-07-04 09:35 andrey.sychev@cifrasoft.com
2019-07-12 15:34 ` Re: Error: rows returned by function are not all of the same row type Ed Behn <ed@behn.us>
0 siblings, 1 reply; 2+ messages in thread
From: andrey.sychev@cifrasoft.com @ 2019-07-04 09:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Error: rows returned by function are not all of the same row type
2019-07-04 09:35 Error: rows returned by function are not all of the same row type andrey.sychev@cifrasoft.com
@ 2019-07-12 15:34 ` Ed Behn <ed@behn.us>
0 siblings, 0 replies; 2+ messages in thread
From: Ed Behn @ 2019-07-12 15:34 UTC (permalink / raw)
To: pgsql-sql
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.sychev@cifrasoft.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.sychev@cifrasoft.com
>
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-07-12 15:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-04 09:35 Error: rows returned by function are not all of the same row type andrey.sychev@cifrasoft.com
2019-07-12 15:34 ` Ed Behn <ed@behn.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox