I have a question reading through this email chain. Does Large Objects table using these functions work like normal MVCC where there can be two versions of a large object in pg_largeobject . My gut says no as moving/copying potentially 4 TB of data would kill any IO.
I can not find any documentation discussing how these functions actually work with respect to Transaction Isolation, MVCC and Snapshots??
> Could you also please state the reason why is it happening in case > of large objects? Because concurrent transactions are very well > handled for other data types, but the same is not happening for > lobs. Is it because the fomer are stored in toast table and there is > no support for concurrent txns in pg_largeobject table?
Keeping in mind that large objects are not a datatype, but rather a functionality that is built on top of the bytea and oid datatypes plus a set of functions, I wouldn't say that concurrent writes would be better handled if you had a table: document(id serial, contents bytea) with "contents" being indeed toastable.
To illustrate with a basic example: transactions Tx1 and Tx2 want to update the contents of the same document concurrently, with this order of execution:
Tx1: begin Tx1: update document set contents=... where id=... Tx2: begin Tx2: update the same document (gets blocked) Tx1: commit Tx2: commit
If using the read committed isolation level, Tx2 will be put to wait until Tx1 commits, and then the update by Tx1 will be overwritten by Tx2. That's a well known anomaly known as a "lost update", and probably not what you want.
If using a better isolation level (repeatable read or serializable), the update by Tx2 will be rejected with a serialization failure, which, to me, seems the moral equivalent of the "Tuple concurrently updated" error you're reporting with large objects. When this occurs, your application can fetch the latest value in a new transaction and see how it can apply its change to the new value, unless another conflict arises and so on.
In short, the best the database can do in case of conflicting writes is to inform the application. It can't know which write should be prioritized or if the changes should be merged before being written.
Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
