Thread: Error: rows returned by function are not all of the same row type
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
cannotaccept 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
Andrey Sychev <andrey.sychev@cifrasoft.com> 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
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 <andrey.sychev@cifrasoft.com> 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 andrey.sychev@cifrasoft.com
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
Attachment
> 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:andrey.sychev@cifrasoft.com