Обсуждение: setFetchSize
Usage of setFetchSize() is described here: http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor These restrictions make it difficult to use. Many applications (including mine) use one connection for many tasks within a thread. So even after turning autocommit off, there is still a chance what while I am reading my result set, the same connection will be used somewhere else and commit() will be called which will break the result set. Clearly the ideal is to allow setFetchSize() to work across commits. I don't understand why this is a problem. Postgres allows one to declare cursors WITH HOLD to work across transactions. Why can't the JDBC driver use this? -- View this message in context: http://postgresql.1045698.n5.nabble.com/setFetchSize-tp4935215p4935215.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On 25 October 2011 19:45, fschmidt <fschmidt@gmail.com> wrote: > Clearly the ideal is to allow setFetchSize() to work across commits. I > don't understand why this is a problem. Postgres allows one to declare > cursors WITH HOLD to work across transactions. Why can't the JDBC driver > use this? Because the driver's not using a SQL-level cursor, it is using a protocol-level portal which can't be declared WITH HOLD. Oliver
Oliver Jowett wrote: > > Because the driver's not using a SQL-level cursor, it is using a > protocol-level portal which can't be declared WITH HOLD. > Where is the documentation for portals? -- View this message in context: http://postgresql.1045698.n5.nabble.com/setFetchSize-tp4935215p4937529.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
fschmidt <fschmidt@gmail.com> writes: > Oliver Jowett wrote: >> Because the driver's not using a SQL-level cursor, it is using a >> protocol-level portal which can't be declared WITH HOLD. > Where is the documentation for portals? http://developer.postgresql.org/pgdocs/postgres/protocol.html regards, tom lane
Okay I see the documentation saying portals die at the end of a transaction, here: http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY But looking at the JDBC code, the main use I see of portals is for setFetchSize(). And setFetchSize() is badly crippled in its current implementation. Is there any fundamental reason that portals can't be made to survive commits, like cursors WITH HOLD? This would result in a reasonable implementation of setFetchSize(). For now I have given up on setFetchSize() as useless for my application, and I use "select ... order by something-unique limit fetch-size offset how-much-has-been-read". This is a bad hack. I would much rather use a functional implementation of setFetchSize(). Is there any chance I will get one? -- View this message in context: http://postgresql.1045698.n5.nabble.com/setFetchSize-tp4935215p4938781.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On 26 October 2011 19:09, fschmidt <fschmidt@gmail.com> wrote: > But looking at the JDBC code, the main use I see of portals is for > setFetchSize(). And setFetchSize() is badly crippled in its current > implementation. Is there any fundamental reason that portals can't be made > to survive commits, like cursors WITH HOLD? This would result in a > reasonable implementation of setFetchSize(). That would be nice to have, yes. But it would require a protocol change which is quite a big thing to do. Oliver
On 10/25/11 11:09 PM, fschmidt wrote: > Okay I see the documentation saying portals die at the end of a transaction, > here: I'm surprised you can make /any/ other queries on that connection with an unfinished portal. I'm pretty sure if I needed to have an open transfer while executing another transaction, I'd use more than one connection. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 26 October 2011 19:37, John R Pierce <pierce@hogranch.com> wrote: > On 10/25/11 11:09 PM, fschmidt wrote: >> >> Okay I see the documentation saying portals die at the end of a >> transaction, >> here: > > I'm surprised you can make /any/ other queries on that connection with an > unfinished portal. > > I'm pretty sure if I needed to have an open transfer while executing another > transaction, I'd use more than one connection. You probably want to reread the protocol docs. Portals are very similar to SQL cursors and while the portal does indeed monopolize the connection while it is executing, you don't have to fetch all the results from a portal in one go. Oliver
On Wed, Oct 26, 2011 at 2:09 AM, fschmidt <fschmidt@gmail.com> wrote: > Okay I see the documentation saying portals die at the end of a transaction, > here: > > http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY > > But looking at the JDBC code, the main use I see of portals is for > setFetchSize(). And setFetchSize() is badly crippled in its current > implementation. Is there any fundamental reason that portals can't be made > to survive commits, like cursors WITH HOLD? This would result in a > reasonable implementation of setFetchSize(). > > For now I have given up on setFetchSize() as useless for my application, and > I use "select ... order by something-unique limit fetch-size offset > how-much-has-been-read". This is a bad hack. I would much rather use a > functional implementation of setFetchSize(). Is there any chance I will get > one? > There is little hope that you will get what you want. I'm not even sure I think it's a good idea as cursor with hold consumes resources on the server which multiplied by an unknown number of connections might end up being significant. FWIW the JDBC docs do not imply any contract for setFetchSize. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Dave Cramer-8 wrote: > > There is little hope that you will get what you want. I'm not even > sure I think it's a good idea as cursor with hold consumes resources > on the server which multiplied by an unknown number of connections > might end up being significant. > And how is it any better to force users to open a new connection to use setFetchSize? This would use just as much resources if not more, right? It is just much more inconvenient for programmers, especially in applications like mine that are designed to use one connection per thread. If there is little hope of getting what I want with Postgres, then I will eventually have to look for an alternative database. -- View this message in context: http://postgresql.1045698.n5.nabble.com/setFetchSize-tp4935215p4940158.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On Wed, Oct 26, 2011 at 11:48 AM, fschmidt <fschmidt@gmail.com> wrote: > > Dave Cramer-8 wrote: >> >> There is little hope that you will get what you want. I'm not even >> sure I think it's a good idea as cursor with hold consumes resources >> on the server which multiplied by an unknown number of connections >> might end up being significant. >> > And how is it any better to force users to open a new connection to use > setFetchSize? This would use just as much resources if not more, right? It > is just much more inconvenient for programmers, especially in applications > like mine that are designed to use one connection per thread. My understanding is this: Postgresql is an MVCC database and can easily provide you with a snapshot of a resultset inside a transaction. Outside a transaction my bet is that the data has to be copied to a temporary location somewhere, so if this is done over N connections the resources required could be substantial. > > If there is little hope of getting what I want with Postgres, then I will > eventually have to look for an alternative database. Well every database I know of has it's idiosyncrasies, an as I said the JDBC spec makes no guarantees of what setFetchSize should do if anything. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Dave Cramer-8 wrote: > > My understanding is this: Postgresql is an MVCC database and can > easily provide you with a snapshot of a resultset inside a > transaction. Outside a transaction my bet is that the data has to be > copied to a temporary location somewhere, so if this is done over N > connections the resources required could be substantial. > The solution is very simple. Just keep the version associated with the current resultset around until the resultset is closed. This is basically the same as having another connection, but in this case, each resultset on the connection should use the version that was associated with the connection when the resultset was opened. > Well every database I know of has it's idiosyncrasies, an as I said > the JDBC spec makes no guarantees of what setFetchSize should do if > anything. > The JDBC spec also makes no guarantees that the database shouldn't crash. The purpose of a spec isn't to limit what is provided to users, rather it is to provide the minimum functionality that users can assume across implementations. -- View this message in context: http://postgresql.1045698.n5.nabble.com/setFetchSize-tp4935215p4941054.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On 10/26/11 1:22 PM, fschmidt wrote: > Dave Cramer-8 wrote: >> > >> > My understanding is this: Postgresql is an MVCC database and can >> > easily provide you with a snapshot of a resultset inside a >> > transaction. Outside a transaction my bet is that the data has to be >> > copied to a temporary location somewhere, so if this is done over N >> > connections the resources required could be substantial. >> > > The solution is very simple. Just keep the version associated with the > current resultset around until the resultset is closed. This is basically > the same as having another connection, but in this case, each resultset on > the connection should use the version that was associated with the > connection when the resultset was opened. so now vacuum needs to pay attention to the oldest pending result set as well as the oldest transaction? hmmm. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 27 October 2011 09:22, fschmidt <fschmidt@gmail.com> wrote: > > Dave Cramer-8 wrote: >> >> My understanding is this: Postgresql is an MVCC database and can >> easily provide you with a snapshot of a resultset inside a >> transaction. Outside a transaction my bet is that the data has to be >> copied to a temporary location somewhere, so if this is done over N >> connections the resources required could be substantial. >> > The solution is very simple. Just keep the version associated with the > current resultset around until the resultset is closed. This is basically > the same as having another connection, but in this case, each resultset on > the connection should use the version that was associated with the > connection when the resultset was opened. All this has already been solved, since WITH HOLD is supported for cursors. (I think the actual implementation there copies out to temporary storage, so perhaps the MVCC approach you suggest is not so straightforward). Anyway, the missing part is a similar sort of support for portals at the protocol level - I suggest you aim your efforts at getting that supported on the server side, since the driver can't do anything until that is supported. >> Well every database I know of has it's idiosyncrasies, an as I said >> the JDBC spec makes no guarantees of what setFetchSize should do if >> anything. >> > The JDBC spec also makes no guarantees that the database shouldn't crash. > The purpose of a spec isn't to limit what is provided to users, rather it is > to provide the minimum functionality that users can assume across > implementations. I think Dave's point here is that the docs for setFetchSize() explicitly says that it's a hint to the driver and the driver is free to ignore it, so a portable application shouldn't be relying on particular behavior. Oliver
John R Pierce <pierce@hogranch.com> writes:
> On 10/26/11 1:22 PM, fschmidt wrote:
>> Dave Cramer-8 wrote:
>>> My understanding is this: Postgresql is an MVCC database and can
>>> easily provide you with a snapshot of a resultset inside a
>>> transaction. Outside a transaction my bet is that the data has to be
>>> copied to a temporary location somewhere, so if this is done over N
>>> connections the resources required could be substantial.
>> The solution is very simple. Just keep the version associated with the
>> current resultset around until the resultset is closed. This is basically
>> the same as having another connection, but in this case, each resultset on
>> the connection should use the version that was associated with the
>> connection when the resultset was opened.
> so now vacuum needs to pay attention to the oldest pending result set as
> well as the oldest transaction?
It's worse than that. If you have say "SELECT * FROM foo" as a
resultset, then to persist that resultset without making a copy of the
data, you not only need to keep vacuum from reclaiming the tuples
involved --- you also have to keep table foo from being dropped or
materially altered in structure. So it's not just a snapshot that has
to be hung onto, it's locks; and at that point the resultset is really
no more nor less than an open transaction. It's as complicated as one
and it has the same negative side-effects on concurrent operations as one.
We quite intentionally decided to implement held cursors by copying the
data to local storage, so that they would not need to hold onto any
shared resources after the originating transaction ends. We're not
likely to consider imposing that sort of overhead on protocol-level
portals --- they're meant to be lightweight objects.
Bottom line from a server-side point of view is that if you want that
overhead, you can ask for it, by opening a held cursor. It might be
sensible for JDBC to provide that functionality with something that
looks as much as possible like an ordinary resultset --- but I'm pretty
certain it shouldn't be the default behavior on the JDBC side either.
regards, tom lane
On 27 October 2011 12:18, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bottom line from a server-side point of view is that if you want that > overhead, you can ask for it, by opening a held cursor. It might be > sensible for JDBC to provide that functionality with something that > looks as much as possible like an ordinary resultset --- but I'm pretty > certain it shouldn't be the default behavior on the JDBC side either. We'd only do this if HOLD_CURSORS_OVER_COMMIT (which isn't the default) was explicitly set on the resultset. It really would be useful to specify something equivalent to WITH HOLD on a portal though - trying to work out how to transform an arbitrary query into a DECLARE CURSOR is the sort of SQL mangling the driver tries to avoid doing. (It did exactly that back in the 7.3 / protocol version 2 days and there were all sorts of nasty edge cases) Oliver