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
Date: Thu, 21 May 2020 00:00:20 -0400
Message-ID: <26902.1590033620@sss.pgh.pa.us> (raw)
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>
"Albin, Lloyd P" <lalbin@scharp.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, lalbin@scharp.org, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16454: Mixed isolation levels inside transactions
In-Reply-To: <26902.1590033620@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox