Re: BUG #16454: Mixed isolation levels inside transactions

"Albin, Lloyd P" <lalbin(at)scharp(dot)org> writes:
>> As a general rule, all internal catalog accesses use latest-committed data (so effectively Read Committed)
>> regardless of what the user-level visibility semantics are. This has to be so, because it just wouldn't do to
>> not be working with the latest data. As an example, you don't get to ignore a CHECK constraint just
>> because it got added after your transaction started.

> I agree with your CHECK constraint use case, IF you were adding data then you are using the READ WRITE property, BUT this transaction was set to use the READ ONLY property. The READ ONLY property would be better served to by matching data and system tables instead of using the mixed/hybrid isolation level.

[ shrug... ] You are trying to wish away a very complicated set of
problems. The CHECK case was merely an easy-to-grasp example; it's
by no means the only issue. As a different example, suppose that
someone DROPs an enormous table. Should we block the drop from
really happening (and hence not reclaim the disk space) because
somewhere there is a serializable transaction that could perhaps
try to read that table later?

You might say "I'm okay with having the serializable transaction
fail with a serialization error if it does try to access such a
table later". But then we'd be looking at trying to classify each
internal catalog access as to how it should be handled in a
serializable transaction, maybe with different rules for read-only
vs read-write transactions. That would be a herculean task fraught
with possibilities for error, and it might well require major code
refactoring to ensure that there was a unique answer for each case.
I don't think anybody's interested in tackling that job for such small
returns in terms of practical usefulness of the system. (Admittedly,
whether the returns are small is a judgment call ... but you've not
even tried to make a case for why this anomaly is a problem for you,
or why you couldn't dodge it by doing things differently.)

I do concede that this isn't adequately documented.
https://www.postgresql.org/docs/current/mvcc-caveats.html
talks about some holes in the MVCC semantics model, but it
doesn't mention this set of issues.

regards, tom lane