From: Tom Lane <tgl@sss.pgh.pa.us>
To: lalbin@scharp.org
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16454: Mixed isolation levels inside transactions
In-reply-to: <16454-9408996bb1750faf@postgresql.org>
References: <16454-9408996bb1750faf@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 21 May 2020 00:52:29 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <22442.1590027242.1@sss.pgh.pa.us>
Date: Wed, 20 May 2020 22:14:02 -0400
Message-ID: <22443.1590027242@sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I am seeing mixed/hybrid Isolation Levels when setting the isolation level
> to SERIALIZABLE READ ONLY DEFERRABLE, this also happens with REPEATABLE READ
> READ ONLY. The user tables sees only the data committed before the
> transaction begins but the system tables sees data committed by other
> transactions after the transaction begins. This means in my example the user
> tables are Serializable Isolation Level and the system tables are Read
> Committed Isolation Level.

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.

This does possibly result in some anomalies.  We've judged that living
with that is better than the alternatives, which would involve imposing
far-more-draconian table locking and forced-transaction-rollback
requirements than exist now for serializable transactions.  In the
example you give, the only practical alternative to the existing behavior
would be to fail the reader transaction as soon as it tries to access
the renamed schema.

			regards, tom lane