Yessica Brinkmann escribió:
> Buenos días,
> Quisiera hacer una consulta al grupo por favor.
> Alguien ha usado SPI_connect() para conectarse a Postgresql desde un
> programa?
> Saben por si acaso en qué casos da error?
Hmm, si estás invocando tu función desde otra función que ya hace
SPI_connect, entonces tienes que hacer SPI_push antes de invocar el
SPI_connect, y un SPI_pop después del SPI_finish. Así creas un nuevo
"contexto" para que SPI pueda procesar limpiamente tu consulta. Me
parece que el ejemplo claro es que get_distinct está llamando a
get_columnnames sin un SPI_push.
Consejo aparte: no necesitas el snprintf con strlen etc; eso es lo que
appendStringInfo() puede hacer por ti en forma más sencilla.
(Para mostrar código fuente recomiendo ponerlo en un archivo adjunto en
vez de copiar/pegar al mail, porque el programa de mail lo va a
reformatear y queda ilegible. Para poder leer tu código tuve que
pasarlo por pgindent ... va de vuelta como archivo adjunto.)
Saludos
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
/**
* save_advice
* for every candidate insert an entry into IND_ADV_TABL
*/
static void
save_advice(List *candidates)
{
StringInfoData query; /* string for Query */
StringInfoData cols; /* string for Columns */
Oid advise_oid;
ListCell *cell;
elog(DEBUG3, "IND ADV: save_advice: ENTER");
Assert(list_length(candidates) != 0);
/*
* Minimal check: check that IND_ADV_TABL is at least visible to us. There
* are a lot more checks we should do in order to not let the INSERT fail,
* like permissions, datatype mis-match, etc., but we leave those checks
* upto the executor.
*/
/* find a relation named IND_ADV_TABL on the search path */
advise_oid = RelnameGetRelid(IND_ADV_TABL);
if (advise_oid != InvalidOid)
{
#if 1
Relation advise_rel = relation_open(advise_oid, AccessShareLock);
if (advise_rel->rd_rel->relkind != RELKIND_RELATION
&& advise_rel->rd_rel->relkind != RELKIND_VIEW)
{
relation_close(advise_rel, AccessShareLock);
/* FIXME: add errdetail() and/or errcontext() calls here. */
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg(IND_ADV_ERROR_NTV)));
}
relation_close(advise_rel, AccessShareLock);
#else
/*
* heap_open() makes sure that the oid does not represent an INDEX or
* a COMPOSITE type, else it will raise an ERROR, which is exactly
* what we want. The comments above heap_open() ask the caller not to
* assume any storage since the returned relation may be a VIEW; but
* we don't mind, since the user may have defined some rules on it to
* make the INSERTs work smoothly! If not, we leave it upto the
* executor to raise ERROR.
*/
PG_TRY();
{
heap_close(heap_open(advise_oid, AccessShareLock), AccessShareLock);
}
PG_CATCH();
{
errmsg(IND_ADV_ERROR_NTV);
PG_RE_THROW();
}
PG_END_TRY();
#endif
}
else
{
/* FIXME: add errdetail() and/or errcontext() calls here. */
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_TABLE),
errmsg(IND_ADV_ERROR_NE)));
}
initStringInfo(&query);
initStringInfo(&cols);
foreach(cell, candidates)
{
int i;
IndexCandidate *idxcd = (IndexCandidate *) lfirst(cell);
if (!idxcd->idxused)
continue;
/*
* pfree() the memory allocated for the previous candidate. FIXME:
* Avoid meddling with the internals of a StringInfo, and try to use
* an API.
*/
if (cols.len > 0)
{
pfree(cols.data);
cols.data = NULL;
}
initStringInfo(&cols);
for (i = 0; i < idxcd->ncols; ++i)
appendStringInfo(&cols, "%s%d", (i > 0 ? "," : ""), idxcd->varattno[i]);
/* FIXME: Mention the column names explicitly after the table name. */
appendStringInfo(&query, "insert into \"" IND_ADV_TABL "\" values"
"( %d, array[%s], %f, %d, %d, now());",
idxcd->reloid,
cols.data,
idxcd->benefit,
idxcd->pages * BLCKSZ / 1024, /* in KBs */
MyProcPid);
} /* foreach cell in candidates */
if (query.len > 0) /* if we generated any SQL */
{
if (SPI_connect() == SPI_OK_CONNECT)
{
if (SPI_execute(query.data, false, 0) != SPI_OK_INSERT)
elog(WARNING, "IND ADV: SPI_execute failed while saving advice.");
if (SPI_finish() != SPI_OK_FINISH)
elog(WARNING, "IND ADV: SPI_finish failed while saving advice.");
}
else
elog(WARNING, "IND ADV: SPI_connect failed while saving advice.");
}
/* TODO: Propose to -hackers to introduce API to free a StringInfoData . */
if (query.len > 0)
pfree(query.data);
if (cols.len > 0)
pfree(cols.data);
elog(DEBUG3, "IND ADV: save_advice: EXIT");
}
/**
* get_distinct
* for every candidate get an entry into IndexCandidate
*/
static void
get_ndistinct(List *candidates)
{
int proc;
StringInfoData query; /* string for Query */
StringInfoData cols; /* string for Columns */
Oid advise_oid;
ListCell *cell;
SPITupleTable *SPI_tuptable;
TupleDesc tupdesc;
elog(DEBUG3, "IND ADV: get_distinct: ENTER");
Assert(list_length(candidates) != 0);
/*
* Minimal check: check that IND_ADV_TABL is at least visible to us. There
* are a lot more checks we should do in order to not let the INSERT fail,
* like permissions, datatype mis-match, etc., but we leave those checks
* upto the executor.
*/
/* find a relation named IND_ADV_TABL on the search path */
advise_oid = RelnameGetRelid(IND_ADV_TABL);
if (advise_oid != InvalidOid)
{
#if 1
Relation advise_rel = relation_open(advise_oid, AccessShareLock);
if (advise_rel->rd_rel->relkind != RELKIND_RELATION
&& advise_rel->rd_rel->relkind != RELKIND_VIEW)
{
relation_close(advise_rel, AccessShareLock);
/* FIXME: add errdetail() and/or errcontext() calls here. */
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg(IND_ADV_ERROR_NTV)));
}
relation_close(advise_rel, AccessShareLock);
#else
/*
* heap_open() makes sure that the oid does not represent an INDEX or
* a COMPOSITE type, else it will raise an ERROR, which is exactly
* what we want. The comments above heap_open() ask the caller not to
* assume any storage since the returned relation may be a VIEW; but
* we don't mind, since the user may have defined some rules on it to
* make the INSERTs work smoothly! If not, we leave it upto the
* executor to raise ERROR.
*/
PG_TRY();
{
heap_close(heap_open(advise_oid, AccessShareLock), AccessShareLock);
}
PG_CATCH();
{
errmsg(IND_ADV_ERROR_NTV);
PG_RE_THROW();
}
PG_END_TRY();
#endif
}
else
{
/* FIXME: add errdetail() and/or errcontext() calls here. */
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_TABLE),
errmsg(IND_ADV_ERROR_NE)));
}
initStringInfo(&query);
initStringInfo(&cols);
foreach(cell, candidates) /* foreach cell in candidates */
{
int i;
IndexCandidate *idxcd = (IndexCandidate *) lfirst(cell);
get_columnnames(idxcd);
if (!idxcd->idxused)
continue;
/*
* pfree() the memory allocated for the previous candidate. FIXME:
* Avoid meddling with the internals of a StringInfo, and try to use
* an API.
*/
if (cols.len > 0)
{
pfree(cols.data);
cols.data = NULL;
}
appendStringInfo(&query, "select n_distintic from pg_stats where ");
for (i = 0; i < idxcd->ncols; ++i)
{
appendStringInfo(&cols, "attname=%s%d", (i > 0 ? "OR" : ""),
idxcd->varattnombres[i]);
} /* foreach col in varattno */
/* FIXME: Mention the column names explicitly after the table name. */
appendStringInfo(&query, "%s;", cols.data);
elog(INFO, "QUERY:%s", query.data);
elog(INFO, "LONGITUD:%d", query.len);
if (query.len > 0) /* if we generated any SQL */
{
if (SPI_connect() == SPI_OK_CONNECT)
{
if (SPI_execute(query.data, true, 0) != SPI_OK_SELECT)
elog(WARNING, "IND ADV: SPI_execute failed while select.");
else /* SPI_OK_SELECT */
{
proc = SPI_processed;
tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
char buf[8192];
int i,
j;
for (j = 0; j < proc; j++)
{
/* cada fila */
HeapTuple tuple = tuptable->vals[j];
for (i = 0, buf[0] = 0; i < tupdesc->natts; i++)
{
/* cada columna de cada fila */
char *data;
data = SPI_getvalue(tuple, tupdesc, i);
/*
* falta imprimir data y guardar el dato
* correspondiente en la estructura del índice
*/
snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), "%s %s", data, (i == tupdesc->natts) ? "" : "|");
}
elog(INFO, "EXECQ:%s", buf);
strcpy(idxcd->ndistinct, buf);
}
}
if (SPI_finish() != SPI_OK_FINISH)
elog(WARNING, "IND ADV: SPI_finish failed while select.");
}
else /* SPI_connect() != SPI_OK_CONNECT */
elog(WARNING, "IND ADV: SPI_connect failed while select.");
}
if (query.len > 0)
pfree(query.data);
} /* foreach cell in candidates */
elog(DEBUG3, "IND ADV: select: EXIT");
}
static void
get_columnnames(IndexCandidate * idx)
{
int proc;
StringInfoData query; /* string for Query */
StringInfoData cols; /* string for Columns */
Oid advise_oid;
ListCell *cell;
SPITupleTable *SPI_tuptable;
TupleDesc tupdesc;
elog(DEBUG3, "IND ADV: get_column_names: ENTER");
initStringInfo(&query);
initStringInfo(&cols);
int i;
IndexCandidate *idxcd = idx;
/*
* pfree() the memory allocated for the previous candidate. FIXME: Avoid
* meddling with the internals of a StringInfo, and try to use an API.
*/
if (cols.len > 0)
{
pfree(cols.data);
cols.data = NULL;
} /* IF col.len>0 */
appendStringInfo(&query, "select a.attname from pg_class c,pg_attribute a where c.oid=%d AND a.attrelid = c.oid AND ", idxcd->reloid);
for (i = 0; i < idxcd->ncols; ++i)
{
appendStringInfo(&cols, "a.attnum=%s%d", (i > 0 ? "AND" : ""),
idxcd->varattno[i]);
} /* foreach col in varattno */
/* FIXME: Mention the column names explicitly after the table name. */
appendStringInfo(&query, "%s;", cols.data);
elog(INFO, "QUERY:%s", query.data);
elog(INFO, "LONGITUD:%d", query.len);
if (query.len > 0) /* if we generated any SQL */
{
if (SPI_connect() == SPI_OK_CONNECT)
{
if (SPI_execute(query.data, true, 0) != SPI_OK_SELECT)
elog(WARNING, "IND ADV: SPI_execute failed while select.");
else /* ok */
{
proc = SPI_processed;
tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
char buf[8192];
int i,
j;
for (j = 0; j < proc; j++)
{
/* cada fila */
HeapTuple tuple = tuptable->vals[j];
for (i = 0, buf[0] = 0; i < tupdesc->natts; i++)
{
/* cada columna de cada fila */
char *data;
data = SPI_getvalue(tuple, tupdesc, i);
idxcd->varattnombres[i] = data;
} /* (i=0,buf[0]=0;i<tupdesc->natts;i++) */
} /* (j=0;j<proc;j++) */
} /* else ok */
if (SPI_finish() != SPI_OK_FINISH)
elog(WARNING, "IND ADV: SPI_finish failed while select.");
} /* if( SPI_connect() == SPI_OK_CONNECT ) */
else
elog(WARNING, "IND ADV: SPI_connect failed while select.");
} /* if( query.len > 0 ) */
/* TODO: Propose to -hackers to introduce API to free a StringInfoData . */
if (query.len > 0)
pfree(query.data);
elog(DEBUG3, "IND ADV: select: EXIT");
}
