postgres=# CREATE FUNCTION func() RETURNS VOID AS $$
declare
cur CURSOR IS SELECT generate_series(1,10) AS a;
BEGIN
FOR erec IN cur LOOP
raise notice 'row %', erec.a ;
IF (erec.a = 5) THEN CLOSE cur; END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT func();
NOTICE: row 1
NOTICE: row 2
NOTICE: row 3
NOTICE: row 4
NOTICE: row 5
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Reproducible on 8.4 and CVS HEAD, the "FOR cursor" statement didn't
exist on earlier versions.
The problem is that exec_stmt_forc keeps using a pointer to the Portal,
which becomes invalid if the cursor is closed in the middle. Patch
attached, will apply..
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 0f52eae..80fc40d 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -1899,6 +1899,7 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt)
{
PLpgSQL_var *curvar;
char *curname = NULL;
+ const char *portalname;
PLpgSQL_expr *query;
ParamListInfo paramLI;
Portal portal;
@@ -1982,6 +1983,7 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt)
if (portal == NULL)
elog(ERROR, "could not open cursor: %s",
SPI_result_code_string(SPI_result));
+ portalname = portal->name;
/* don't need paramlist any more */
if (paramLI)
@@ -2000,11 +2002,16 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt)
rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal, false);
/* ----------
- * Close portal, and restore cursor variable if it was initially NULL.
+ * Close portal. The statements executed in the loop might've closed the
+ * cursor already, rendering our portal pointer invalid, so we mustn't
+ * trust that.
* ----------
*/
- SPI_cursor_close(portal);
+ portal = SPI_cursor_find(portalname);
+ if (portal != NULL)
+ SPI_cursor_close(portal);
+ /* Restore cursor variable if it was initially NULL. */
if (curname == NULL)
{
free_var(curvar);
@@ -4267,6 +4274,13 @@ exec_run_select(PLpgSQL_execstate *estate,
* exec_for_query --- execute body of FOR loop for each row from a portal
*
* Used by exec_stmt_fors, exec_stmt_forc and exec_stmt_dynfors
+ *
+ * If the portal is for a cursor that's visible to user code, the statements
+ * we execute might move or close the cursor. You must pass prefetch_ok=false
+ * in that case to disable optimizations that rely on the portal staying
+ * unchanged over execution of the user statements.
+ * NB: With prefetch_ok=false, the portal pointer might point to garbage
+ * after the call. Caller beware!
*/
static int
exec_for_query(PLpgSQL_execstate *estate, PLpgSQL_stmt_forq *stmt,
@@ -4278,6 +4292,10 @@ exec_for_query(PLpgSQL_execstate *estate, PLpgSQL_stmt_forq *stmt,
bool found = false;
int rc = PLPGSQL_RC_OK;
int n;
+ const char *portalname;
+
+ /* Remember portal name so that we can re-find it */
+ portalname = portal->name;
/*
* Determine if we assign to a record or a row
@@ -4386,8 +4404,22 @@ exec_for_query(PLpgSQL_execstate *estate, PLpgSQL_stmt_forq *stmt,
/*
* Fetch more tuples. If prefetching is allowed, grab 50 at a time.
+ * Otherwise the statements executed in the loop might've moved or
+ * even closed the cursor, so check that the cursor is still open,
+ * and fetch only one row at a time.
*/
- SPI_cursor_fetch(portal, true, prefetch_ok ? 50 : 1);
+ if (prefetch_ok)
+ SPI_cursor_fetch(portal, true, 50);
+ else
+ {
+ portal = SPI_cursor_find(portalname);
+ if (portal == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_CURSOR),
+ errmsg("cursor \"%s\" closed unexpectedly",
+ portalname)));
+ SPI_cursor_fetch(portal, true, 1);
+ }
tuptab = SPI_tuptable;
n = SPI_processed;
}
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
