Re: Large Objects versus transactional behavior

On Sat, Apr 30, 2011 at 2:58 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> This is related to the "SIREAD lock versus ACCESS EXCLUSIVE lock"
> thread, but seemed different enough to merit spinning off a new
> thread.
>
> Our shop hasn't used large objects so far because of the lack of
> security (until 9.1), so I never noticed the rather unusual
> transactional semantics of large objects.  From the devel
> documentation:
>
> http://developer.postgresql.org/pgdocs/postgres/lo-interfaces.html#LO-OPEN
>
> | [...] with INV_READ you cannot write on the descriptor, and the
> | data read from it will reflect the contents of the large object at
> | the time of the transaction snapshot that was active when lo_open
> | was executed, regardless of later writes by this or other
> | transactions. Reading from a descriptor opened with INV_WRITE
> | returns data that reflects all writes of other committed
> | transactions as well as writes of the current transaction. This is
> | similar to the behavior of REPEATABLE READ versus READ COMMITTED
> | transaction modes for ordinary SQL SELECT commands.
>
> Since Serializable Snapshot Isolation can only serialize behavior
> which is working within the semantics of snapshot isolation, it
> doesn't seem like SSI has any chance of serializing access to the
> contents of a large object while the current behavior stands.
> Modifications to the *references* to large objects within the bodies
> of normal tables is properly tracked by SSI, but no predicate locks
> are taken on the large object contents themselves, nor would
> modifications to the contents be able to generate a rw-conflict
> between transactions.
>
> In other words, I don't think there is any action item here for SSI
> in terms of C code for 9.1, but we may want to mention the unusual
> transaction-related behavior of large objects within the Concurrency
> Control chapter of the docs.
>
> Comments?

Well, in the long run, I think serializability ought to apply to large
objects along with everything else. But documenting it seems like a
reasonable approach for now.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company