> 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.
I agree with your CHECK constraint use case, IF you were adding data then you are using the READ WRITE property, BUT
thistransaction was set to use the READ ONLY property. The READ ONLY property would be better served to by matching
dataand 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
threeof these properties are selected for a transaction, the transaction may block when first acquiring its snapshot,
afterwhich it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of
contributingto 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
afailure point for backups. This could also be fixed by having READ ONLY property use the matching system tables.
Lloyd Albin
