Re: [HACKERS] cursors outside transactions
On Tue, 2003-03-18 at 19:00, Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Tom Lane wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > > > The question here is do we want to offer a half-baked solution, > > > recognizing that it's some improvement over no solution at all? > > > Or do we feel it doesn't meet our standards? > > > > My question is how would you do this if you need this > > functionality and you don't have WITH HOLD cursors? > > ODBC(maybe JDBC also) has cross-transaction result sets > (rather than cursors) since long by simply holding all > results for a query at client side. JDBC is running into problems with this. Large queries cause out of memory exceptions. > Why are cursors outside transactions expected eagerly ? > Because it's very hard (almost impossible) for clients > to provide a functionality to edit(display/scroll/update > etc) large result sets effectively. > > I don't object to a half-baked solution if there's a > prospect of a real solution. However, I've never seen > it and I have little time to investigate it unfortunately. > > regards, > Hiroshi Inoue > http://www.geocities.jp/inocchichichi/psqlodbc/ > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cursors outside transactions
On Wednesday 19 March 2003 04:33 am, you wrote: > Dave Cramer <[EMAIL PROTECTED]> writes: > > On Tue, 2003-03-18 at 19:00, Hiroshi Inoue wrote: > >> ODBC(maybe JDBC also) has cross-transaction result sets > >> (rather than cursors) since long by simply holding all > >> results for a query at client side. > > > > JDBC is running into problems with this. Large queries cause out of > > memory exceptions. > > Cursors implemented as Neil suggests would cause out-of-disk exceptions. > The limit is presumably further away than out-of-memory, but not any the > less real. I'm concerned about this because, in my mind, one of the > principal uses of cursors is to deal with too-huge-to-materialize result > sets. > > Still, given that we have no prospect of a "real" solution any time > soon, a limited solution might be a reasonable thing to offer for now. > We have got more disk space than internal memory. Similar argument would be valid for swap in operating systems, but all operating systems have got swap and large results sets use swap, probably. 'Out of disk' is small problem - we can add more disk easy. Cursors out of a transaction are great features - the most important for clients in Java, but for other clients. regards Haris Peco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] cursors outside transactions
On Tue, 2003-03-18 at 19:00, Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Tom Lane wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > > > The question here is do we want to offer a half-baked solution, > > > recognizing that it's some improvement over no solution at all? > > > Or do we feel it doesn't meet our standards? > > > > My question is how would you do this if you need this > > functionality and you don't have WITH HOLD cursors? > > ODBC(maybe JDBC also) has cross-transaction result sets > (rather than cursors) since long by simply holding all > results for a query at client side. JDBC is running into problems with this. Large queries cause out of memory exceptions. > Why are cursors outside transactions expected eagerly ? > Because it's very hard (almost impossible) for clients > to provide a functionality to edit(display/scroll/update > etc) large result sets effectively. > > I don't object to a half-baked solution if there's a > prospect of a real solution. However, I've never seen > it and I have little time to investigate it unfortunately. > > regards, > Hiroshi Inoue > http://www.geocities.jp/inocchichichi/psqlodbc/ > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer <[EMAIL PROTECTED]> -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] cursors outside transactions
Neil Conway writes: > I'm currently planning to implement (1), as it is sufficient for the > immediate need that I'm facing. What need are you facing, and why is it not sufficient to explicitly store the query results in a temporary table? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cursors outside transactions
Bruce Momjian <[EMAIL PROTECTED]> writes: > Why don't you like (1)? It seems fine to me, and I don't see how we are > magically going to do any better in the future. The restrictions of (1) seem pretty obvious to me ... but I don't see any prospect of doing better in the near future, either. Cross-transaction cursors are a *hard* problem for us. The question here is do we want to offer a half-baked solution, recognizing that it's some improvement over no solution at all? Or do we feel it doesn't meet our standards? I could be talked into seeing it either way ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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: INSENSITIVE SQL92 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 PostgreSQL this is always the case. This keyword has no effect. 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 <[EMAIL PROTECTED]> || 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] cursors outside transactions
On Mon, Mar 17, 2003 at 10:26:07PM -0500, Neil Conway wrote: > On Mon, 2003-03-17 at 22:01, Alvaro Herrera wrote: > > What about opening a pseudo-transaction that exists only to serve the > > cursor? > > What exactly do you mean by a pseudo-transaction? Assign an xid, create the transaction (create a pg_clog entry), open the cursor using that xid, and put that xid into some table so it can be ended when the cursor is closed. That's why I said you'll probably need to access the lowlevel routines for transactions. OTOH I can hear objections to that idea right now... > Keep in mind we don't have nested transactions (yet?), I'm looking at the issues about this. I don't think that facility will help you, since you have to end the inner transactions before you end the outer ones. That is, you can't create a normal subtransaction to hold the cursor and expect it to live longer than the outer one. -- Alvaro Herrera () Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] cursors outside transactions
On Mon, 2003-03-17 at 22:01, Alvaro Herrera wrote: > What about opening a pseudo-transaction that exists only to serve the > cursor? What exactly do you mean by a pseudo-transaction? Keep in mind we don't have nested transactions (yet?), and that the holdable cursor needs to be accessible both inside and outside its creating transaction. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cursors outside transactions
On Mon, Mar 17, 2003 at 09:48:34PM -0500, Neil Conway wrote: > (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. What about opening a pseudo-transaction that exists only to serve the cursor? That frees you from modifying VACUUM and resource management. The transaction should be commited (aborted?) when the cursor is closed. Maybe you can call the lowlevel transaction routines directly. -- Alvaro Herrera () "Postgres is bloatware by design: it was built to house PhD theses." (Joe Hellerstein, SIGMOD annual conference 2002) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] cursors outside transactions
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 <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
