Re: BUG #13660: serializable snapshotting hangs

On Wednesday, October 7, 2015 9:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> The Serializable Snapshot Isolation techniques only work on top of
>> MVCC snapshot isolation.  At the time of the SSI patch, catalogs
>> were not accessed according to those rules, so we excluded them
>> from SSI handling -- it just could not have worked in a meaningful
>> way.  (So catalog access is currently identical under REPEATABLE
>> READ and SERIALIZABLE transaction isolation levels.)
>
> Really?  "SELECT * FROM pg_database" will ignore SSI?

Yes.  If that is run in a SERIALIZABLE transaction, the effect will
be the same as if it were run in a REPEATABLE READ transaction --
it will use the transaction snapshot but will not create
SIReadLocks or examine the MVCC state of the rows read to look for
read-write conflicts.

> I agree that the system's own catalog accesses don't pay attention
> to SSI, and probably can't because of concerns about needing to see
> fully up-to-date data.  But that's not my question.  My point is
> that userland snapshots can be used to examine shared catalogs,
> and even update them if you're a superuser.

In such situations a SERIALIZABLE transaction provides no
additional protections against serialization anomalies involving
the catalogs than a REPEATABLE READ transaction.

If we decided to support the distinction you suggest, I'm not sure
how we would distinguish changes made by DDL commands from changes
made by superuser DML to catalog tables when examining the row's
MVCC data during a scan.  I am also skeptical that the addition of
predicate locks (done from inside heap and index AMs) would have a
sensible way to distinguish between the two cases.  Finally, it's
not immediately obvious what the benefit would be of distinguishing
between the two forms of catalog modification.

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