cursors outside transactions - Mailing list pgsql-hackers

Folks,

I'm currently working on an implementation of cursors that can function
outside the transaction that created them (the SQL spec calls them
"holdable cursors"). I can see 2 main ways to implement this:

(1) During the transaction that created the holdable cursor, don't do
anything special. When that transaction ends (and we'd normally be
cleaning up cursor resources), fetch all the rows from the cursor and
store them in a Tuplestore. When subsequent FETCHs for the cursor are
received, handle them by retrieving rows from the Tuplestore.

Pros:

- simple to implement
- doesn't acquire locks (etc.) on any database objects queried by the
cursor, so later database operations can continue in parallel with the
retrieval of rows from the holdable cursor

Cons:

- doesn't allow for updates to the cursor
- doesn't allow for sensitive/asensitive cursors (i.e. the cursor cannot
witness changes made to its result set by other clients -- see 4.34 of
SQL 2003)
- inefficient if the result set the cursor is fetching is enormous, as
it must be stored on disk prior to committing the transaction

(2) Use MVCC to ensure that the snapshot of the database that the
transaction had is still valid, even after the transaction itself has
committed. This would require:

(a) changing VACUUM so that it's aware the tuples visible to the cursor
can't be removed yet

(b) holding locks on database objects, so that future database
operations don't cause problems for the cursor (e.g. you can't allow
someone to drop a table still in use by a holdable cursor). Another
example is the row-level locks used for updated tuples, if updatedable
cursors are implemented -- they would be need to be held for much longer
than normal.

(c) probably more changes: the assumption that a transaction's resources
can be cleaned up once it commits is a fairly fundamental one, so there
are surely additional things that will need to be kept locked while the
holdable cursor is still valid (likely, until the client connection is
terminated).

Pros:

- efficient for large result sets (just like normal cursors)
- updateable and sensitive cursors would be easier to implement

Cons:

- really complex, difficult to get right
- would hurt concurrent performance, due to long-term locks

I'm currently planning to implement (1), as it is sufficient for the
immediate need that I'm facing.

Any comments? Is there another way to implement this that I'm not
seeing?

In particular, I'd like to know if the list would object to integrating
(1) into the mainline sources (perhaps until someone gets around to
doing something similar to (2), which may be never).

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC