RE: BUG #16454: Mixed isolation levels inside transactions

> PG Bug reporting form <noreply(at)postgresql(dot)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.

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.

Per the docs https://www.postgresql.org/docs/current/sql-set-transaction.html

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups.

Due to the above statement, I presume backups are using the SERIALIZABLE READ ONLY DEFERRABLE, which means this is also a failure point for backups. This could also be fixed by having READ ONLY property use the matching system tables.

Lloyd Albin