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 (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs