Noel Grandin <noelgrandin@gmail.com> writes:
> On Fri, 24 Feb 2023 at 17:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Postgres doesn't really do LOB in the same sense that some other DBs
>> have, so you'd need to specify what you have in mind in Postgres
>> terms to get a useful answer.
> So, specifically, the primary problem we have is this:
> (1) A typical small query returns all of its data in a stream to the client
> (2) which means that, from the server's perspective, the transaction is
> closed the moment the last record in the stream is pushed to the client.
> (3) which means that, in the face of concurrent updates, the underlying
> MVCC data in the query might be long-gone from the server by the time the
> client has finished reading the result set.
> (4) However, with LOBs, the client doesn't get the LOB in the result set
> data stream, it gets a special identifier (a hash), which it uses to fetch
> LOB data from the server in chunks
> (5) Which means that the lifetime of an individual LOB is just horrible
> At the moment the implementation I have satisfies the needs of clients in
> terms of correctness (crosses fingers), but is horrible in terms of
> performance because of how long it has to keep LOB data around.
Yeah, Postgres has an analogous kind of problem. Our standard way to
use "large objects" is to store their identifying OIDs in tables,
fetch the desired OID with a regular SQL query, and then open and read
(or write) the large object using its OID. So you have a hazard of
time skew between what you saw in the table and what you see in the
large object. We pretty much lay that problem off on the clients: if
they want consistency of those views they need to make sure that the
same snapshot is used for both the SQL query and the large-object
read. That's not hard to do, but it isn't the default behavior,
and in particular they can *not* close the transaction that read the
OID if they'd like to read a matching state of the large object.
So far there's not been a lot of complaints about that ...
regards, tom lane