Re: TR: [BUGS] BUG #8842: lo_open/fastpath transaction inconsistency

I encountered this problem while using exactly the pattern you describe!

"Always add the large object before setting a reference to it, and always eliminate references to a large object before deleting it."

The problem is that you cannot delete references that have been seen by transaction that started in the past ! So even with this pattern, it is important that older transaction can access LOB that are concurrently deleted/replaced. And this is only possible if they access it in readonly mode. (This is coherent with the fact that they won't be able to perform modification on it at that time)

My patch just make LOB access read only by default until a modification is required.

Ludovic

-----Message d'origine-----
De : Kevin Grittner [mailto:kgrittn(at)ymail(dot)com]
Envoyé : lundi 5 mai 2014 15:55
À : Ludovic POLLET; pgsql-jdbc(at)postgresql(dot)org
Objet : Re: [JDBC] TR: [BUGS] BUG #8842: lo_open/fastpath transaction inconsistency

Ludovic POLLET <ludovic(dot)pollet(at)kurmi-software(dot)com> wrote:

> We are facing a bug where our application cannot read a Lob
> because of concurrent removal. However, we are using the
> serializable isolation level and a correct transaction scope.
> We're using the latest JDBC driver snapshot (and hibernate but
> that does not really matter).
>
> The problematic use case is basically a thread that read the LOB
> while another thread replace it with a new one and release the
> old one. In this case, the reader will get a "large object xxx
> does not exist".
>
> From what I understand, it is due to the opening of the large
> object that is always done in read/write mode and thus behave as
> a read commited one (as written in the doc).
>
> This behaviour is at least very surprising especially when
> working in SERIALIZABLE concurrency level.
>
> The attached patch implements the following changes:
> -  Opening the lob in read mode until a write function is called.
> In such a case, the LOB will be reopened in read/write mode. It
> totally solves my problem since our application does not modify
> Lob, but creates new ones indeed.
> -  Deferring the first lo_open until a Blob function is really
> called (hibernate seems to be creating blob even if it does not
> even read them...)
>
> What do you think about these behaviors ?

As one of the developers of the implementation of serializable
transactions used by PostgreSQL, I remember that we needed to
exclude large objects from the transaction serialization because it
basically did not follow transactional semantics in any other
regard.  The only workaround I can think to suggest is to only
insert and delete large objects -- never update them, and rely on
the serialization of modifications to the OID columns which
*reference* the large objects.  Always add the large object before
setting a reference to it, and always eliminate references to a
large object before deleting it.  A crash at the wrong time could
leave a large object which is unreferenced, so you might want to
look at the vacuumlo executable:

http://www.postgresql.org/docs/current/static/vacuumlo.html

Unfortunately, I don't know how that approach interacts with
Hibernate or its use of the JDBC driver.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company