Noel Grandin <noelgran...@gmail.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