On Thu, Nov 16, 2017 at 12:36 PM, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > On 11/16/17 07:04, legrand legrand wrote: >> We are just opening the "close cursors on/at commit" specification ;o) >> >> - MS SQL server: cursor_close_on_commit >> - Firebird: close_cursors_at_commit >> - DB2: "with hold" syntax >> - ... >> >> I think it a plus to support keeping opened cursors at commit time, >> but impacts have to be checked in details ... > > I think the facilities to support this in PostgreSQL are already there. > We'd just have to tweak PL/pgSQL to make some of its internal portals > "held" and then clean them up manually at some later point. So I think > this is a localized detail, not a fundamental problem.
Automatically persisting cursors (WITH HOLD) can have some very surprising performance considerations, except when the current code execution depends on that particular cursor, in which case the current behavior of raising a (hopefully better worded-) error seems appropriate. Cursors based on temporary tables could be exempt from having to be closed or checked on COMMIT. plpgsql does not have the facility to create held cursors FWICT...automatic promotion seems pretty dubious. It could certainly be added, and cursors so held could be exempt from being force closed/errored as well. In lieu of that, having users materialize data in to temp tables for such cases seems reasonable. merlin
