improper call to spi_printtup ???

Darko Prenosil <darko.prenosil@finteh.hr> writes:

Anyone knows what I'm doing wrong ?

Well, when you didn't show us the text of the function, no.

However, a reasonable bet would be that you used SPI inside the function
and did not use it correctly, leaving the SPI state corrupted when
control got back to plpgsql.

regards, tom lane

Here is the source, but now when You mentioned SPI state, I see that I put
SPI_finish stupidly after SRF_RETURN_NEXT(funcctx, result);
Could that be my problem ?

Regards !

PG_FUNCTION_INFO_V1(check_view);
Datum
check_view(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
MemoryContext oldcontext;
int spiRet=0;
PQExpBuffer queryBuff;
void *plan=0;
char *schemaName = 0;
char *viewName = 0;
int ret = -1;

uint call_cntr;
uint max_calls;
TupleTableSlot *slot;
AttInMetadata *attinmeta;

typedef struct sqlPlanInfo{
SPITupleTable* result;
List* targetList;
} sqlPlanInfo;

if ( SRF_IS_FIRSTCALL()){

schemaName = GET_STR(PG_GETARG_TEXT_P(0));
viewName = GET_STR(PG_GETARG_TEXT_P(1));
queryBuff = createPQExpBuffer();

if (schemaName == NULL)
elog(ERROR, "schemaName not set");

if (viewName == NULL)
elog(ERROR, "viewName not set");

if (_SPI_connected >=0){
elog(NOTICE, "ALREADY CONNECTED");
spiRet = _SPI_connected;
}else{
if ((spiRet = SPI_connect()) < 0)
elog(ERROR, "rlog: SPI_connect returned %d", spiRet);
}

funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

printfPQExpBuffer(queryBuff,
"SELECT definition"
" FROM pg_views WHERE schemaname='%s' "
" AND viewname ='%s';",schemaName,viewName
);

ret = SPI_exec(queryBuff->data,1);
if (ret == SPI_OK_SELECT){
if ( SPI_processed > 0 ){
TupleDesc tupdesc = SPI_tuptable->tupdesc;
printfPQExpBuffer(
queryBuff,
"%s",
SPI_getvalue(SPI_tuptable->vals[0],tupdesc,1)
);
}else{
elog(ERROR, "Unexisting view %s.%s", schemaName,viewName );
}
}else{
elog(ERROR, "Error executing %s", queryBuff->data );
}

plan = SPI_prepare(queryBuff->data, 0, 0);
if (!plan)
elog(ERROR, "Unable to create plan for %s", queryBuff->data );

ret = SPI_execp(plan,0, 0, 0);

if (ret < 0){
elog(ERROR, "Error executing %s", queryBuff->data );
}else{
List *raw_parsetree_list=pg_parse_query(queryBuff->data);
Node *parsetree = (Node *) lfirst(raw_parsetree_list);
List *query_list = pg_analyze_and_rewrite(parsetree,0,0);
Query *queryTree = (Query *) lfirst(query_list);
sqlPlanInfo* inf = (sqlPlanInfo*) palloc(sizeof(sqlPlanInfo));

inf->result = SPI_tuptable;
inf->targetList = queryTree->targetList;
funcctx->max_calls = inf->result->tupdesc->natts;

/*
* Generate attribute metadata needed later to produce tuples
*/
TupleDesc tupdescRes;
tupdescRes = CreateTemplateTupleDesc(18, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 1, "attrelid",OIDOID, -1, 0,
false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 2, "attname",NAMEOID, -1, 0,
false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 3, "atttypid",OIDOID, -1, 0,
false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 4, "attstattarget",
INT4OID, -1, 0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 5, "attlen",INT2OID, -1, 0,
false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 6, "attnum",INT2OID, -1, 0,
false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 7, "attndims",INT4OID, -1, 0,
false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 8, "attcacheoff",INT4OID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 9, "atttypmod", INT4OID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 10, "attbyval",BOOLOID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 11, "attstorage",CHAROID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 12, "attisset", BOOLOID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 13, "attalign",CHAROID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 14, "attnotnull",BOOLOID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 15, "atthasdef", BOOLOID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 16,
"attisdropped",BOOLOID, -1, 0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 17, "attislocal",BOOLOID, -1,
0, false);
TupleDescInitEntry(tupdescRes, (AttrNumber) 18,
"attinhcount",INT4OID, -1, 0, false);

slot = TupleDescGetSlot(tupdescRes);
funcctx->slot = slot;
attinmeta = TupleDescGetAttInMetadata(tupdescRes);
funcctx->attinmeta = attinmeta;
funcctx->user_fctx = inf;
}

MemoryContextSwitchTo(oldcontext);
}

funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx->call_cntr;
max_calls = funcctx->max_calls;
slot = funcctx->slot;
sqlPlanInfo* inf = (sqlPlanInfo*)funcctx->user_fctx;

if (call_cntr >= funcctx->max_calls)
SRF_RETURN_DONE(funcctx);

/* Do we have a non-resjunk tlist item? */
while (inf->targetList && ((TargetEntry *)
lfirst(inf->targetList))->resdom->resjunk)
inf->targetList = lnext(inf->targetList);

char **values;
HeapTuple tuple;
Datum result;
values = (char **) palloc(18 * sizeof(char *));

if (inf->targetList){
Resdom *res = ((TargetEntry *) lfirst(inf->targetList))->resdom;
inf->result->tupdesc->attrs[call_cntr]->attrelid = res->resorigtbl;
inf->result->tupdesc->attrs[call_cntr]->attnum = res->resorigcol;
inf->targetList = lnext(inf->targetList);
}

int cols=0;
for (cols=0; cols<18;cols++){
values[cols] = (char *) palloc(256);
}

sprintf(values[0], "%i", inf->result->tupdesc->attrs[call_cntr]->attrelid);
sprintf(values[1], "%s",
inf->result->tupdesc->attrs[call_cntr]->attname.data);
sprintf(values[2],"%i",inf->result->tupdesc->attrs[call_cntr]->atttypid);

sprintf(values[3],"%i",inf->result->tupdesc->attrs[call_cntr]->attstattarget
);
sprintf(values[4],"%i",inf->result->tupdesc->attrs[call_cntr]->attlen);
sprintf(values[5],"%i",inf->result->tupdesc->attrs[call_cntr]->attnum);
sprintf(values[6],"%i",inf->result->tupdesc->attrs[call_cntr]->attndims);

sprintf(values[7],"%i",inf->result->tupdesc->attrs[call_cntr]->attcacheoff);
sprintf(values[8],"%i",inf->result->tupdesc->attrs[call_cntr]->atttypmod);
sprintf(values[9],"%i",inf->result->tupdesc->attrs[call_cntr]->attbyval);

sprintf(values[10],"%c",inf->result->tupdesc->attrs[call_cntr]->attstorage);
sprintf(values[11],"%i",inf->result->tupdesc->attrs[call_cntr]->attisset);
sprintf(values[12],"%c",inf->result->tupdesc->attrs[call_cntr]->attalign);

sprintf(values[13],"%i",inf->result->tupdesc->attrs[call_cntr]->attnotnull);
sprintf(values[14],"%i",inf->result->tupdesc->attrs[call_cntr]->atthasdef);

sprintf(values[15],"%i",inf->result->tupdesc->attrs[call_cntr]->attisdropped
);

sprintf(values[16],"%i",inf->result->tupdesc->attrs[call_cntr]->attislocal);

sprintf(values[17],"%i",inf->result->tupdesc->attrs[call_cntr]->attinhcount)
;

/* build a tuple */
tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
/* make the tuple into a datum */
result = TupleGetDatum(slot, tuple);
SRF_RETURN_NEXT(funcctx, result);

SPI_finish();
}

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Darko Prenosil" <darko.prenosil@finteh.hr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, June 28, 2004 9:39 PM
Subject: Re: [HACKERS] improper call to spi_printtup ???

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Darko Prenosil" <darko.prenosil@finteh.hr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, June 28, 2004 9:39 PM
Subject: Re: [HACKERS] improper call to spi_printtup ???

Darko Prenosil <darko.prenosil@finteh.hr> writes:

Anyone knows what I'm doing wrong ?

Well, when you didn't show us the text of the function, no.

However, a reasonable bet would be that you used SPI inside the function
and did not use it correctly, leaving the SPI state corrupted when
control got back to plpgsql.

You figure it out right, SPI_finish was in the wrong place.
Thanks again.

Regards !