Re: cursors outside transactions

I think (1) is fine. When I used Informix, we did lots of huge cursors
that we pulled from for reports, and they consumed huge amounts of RAM
before we could do a fetch --- and we expected that. It doesn't seem
worth adding complexity to avoid that, especially since even if (2) was
done, there would be downsides to it.

One question is how sensitive these cursors should be. Actually,
looking at the DECLARE manual page, I see:

<varlistentry>
<term>INSENSITIVE</term>
<listitem>
<para>
<acronym>SQL92</acronym> keyword indicating that data retrieved
from the cursor should be unaffected by updates from other processes or cursors.
Since cursor operations occur within transactions
in <productname>PostgreSQL</productname> this is always the case.
This keyword has no effect.
</para>
</listitem>
</varlistentry>

which seems inaccurate. Surely we see commits of other transactions
during our multi-statement transaction in the default READ COMMITTED
isolation level, so why do the docs say insensitive is meaningless for
us? Does sensitivity only apply outside the transaction somehow?

So, my question is how do cursors behave now? Do they see commits by
other transactions while in a multi-statement transaction? (1) is
predictable in terms of sensitivity, or at least frozen at commit.

---------------------------------------------------------------------------

Neil Conway wrote:
> 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(at)samurai(dot)com> || PGP Key ID: DB3C29FC
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073