Re: how does postgresql handle LOB/CLOB/BLOB column data that dies before the query ends

Noel Grandin <noelgrandin(at)gmail(dot)com> writes:
> OK, so it seems like so far my design is not far off the PostgreSQL design
> (which is very comforting).

> I wonder if the difference is in the client<->server protocol.

That could be a piece of the puzzle, yeah.

> Does PostgreSQL hold the transaction open until the client side has closed
> the resultset (or the query object possibly, not sure about the PostgreSQL
> API here).

We use single-threaded server processes, so we couldn't close the
transaction (or more to the point, drop the query's snapshot) until
we've computed and sent the whole resultset. I should think that
there's a similar requirement even if multi-threaded: if you do MVCC
at all then you have to hold your snapshot (or whatever mechanism
you use) until the resultset is all computed, or else later rows
in the query result might be wrong.

In the scenario I'm describing with a query fetching some large
object OID(s) followed by separate queries retrieving those large
objects, we put it on the client to create an explicit transaction
block around those queries (ie send BEGIN and COMMIT commands),
and to select a transaction mode that causes the same snapshot to
be used across the whole transaction. If the client fails to do
this, there could be concurrency anomalies. Any one of those
queries will still deliver self-consistent results, but they
might not match up with earlier or later queries.

regards, tom lane