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