From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Albin, Lloyd P" <lalbin@scharp.org>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16454: Mixed isolation levels inside transactions
In-reply-to: 
 <MWHPR11MB1901DA729F658DC74976F9B9B1B70@MWHPR11MB1901.namprd11.prod.outlook.com>
References: <16454-9408996bb1750faf@postgresql.org>
 <22443.1590027242@sss.pgh.pa.us>
 <MWHPR11MB1901DA729F658DC74976F9B9B1B70@MWHPR11MB1901.namprd11.prod.outlook.com>
Comments: In-reply-to "Albin, Lloyd P" <lalbin@scharp.org>
	message dated "Thu, 21 May 2020 02:51:34 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <26901.1590033620.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 21 May 2020 00:00:20 -0400
Message-ID: <26902.1590033620@sss.pgh.pa.us>
Precedence: bulk

"Albin, Lloyd P" <lalbin@scharp.org> writes:
>> As a general rule, all internal catalog accesses use latest-committed d=
ata (so effectively Read Committed) =

>> regardless of what the user-level visibility semantics are.  This has t=
o be so, because it just wouldn't do to =

>> not be working with the latest data.  As an example, you don't get to i=
gnore 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 the=
n you are using the READ WRITE property, BUT this transaction was set to u=
se the READ ONLY property. The READ ONLY property would be better served t=
o by matching data and system tables instead of using the mixed/hybrid iso=
lation 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