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

Noel Grandin <noelgrandin(at)gmail(dot)com> writes:
> Hacker from another open-source DB here (h2database.com).

> How does postgresql handle the following situation?

> (1) a table containing a LOB column

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.

We do have a concept of "large objects" named by OIDs, but they're
much more of a manually-managed, nontransparent feature than typical
LOB implementations. I don't think our JDBC driver implements the
sort of syntax you sketch (I could be wrong though, not much of a
JDBC guy).

Having said that ...

> In the face of concurrent updates that might overwrite the existing LOB
> data, how does PostgresQL handle this?

... reading from a large object follows the same MVCC rules we use
for all other data. We allow multiple versions of a tuple to exist
on-disk, and we don't clean out old versions until no live transaction
can "see" them anymore. So data consistency is just a matter of using
the same "snapshot" (which selects appropriate tuple versions) across
however many queries you want consistent results from. If somebody
writes new data meanwhile, it doesn't matter because that tuple version
is invisible to your snapshot.

> Or does it impose some extra constraint on the client side? e.g..
> explicitly opening and closing a transaction, and only wipe the "old" LOB
> data when the transaction is closed?

From a client's perspective, the two options are "snapshots last for
one query" and "snapshots last for one transaction". You signify which
one you want by selecting a transaction isolation mode when you begin
the transaction.

regards, tom lane