Re: Tuple concurrency issue in large objects

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

On Wed, Dec 18, 2019 at 10:05 AM Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

> Shalini wrote:
>
> > 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
>
>
>