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