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

On Sat, 25 Feb 2023 at 08:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yeah, Postgres has an analogous kind of problem. Our standard way to
> use "large objects" is to store their identifying OIDs in tables,
>
...

> 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 ...
>
>
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.

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).
H2 has a very simple client-server protocol, which means the client simply
sends a query and gets back a result-set stream, and there is no explicit
acknowledgement of when the client closes the resultset, which means that
the MVCC transaction is typically closed by the time the client even starts
reading the resultset.