Serializable read only deferrable- implications

From: Michael Lewis <mlewis(at)entrata(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Serializable read only deferrable- implications
Date: 2022-03-08 13:29:05
Message-ID: CAHOFxGrrab_d3sOWy-jZWYVf=teP+u_c2KXYaZeFTqwhOrVS=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

https://www.postgresql.org/docs/14/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."

Could anyone expound on the above? What are the real life differences
between the two commands below? Would this be applicable at all to sessions
on a physical replica?

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
READ ONLY DEFERRABLE;

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

*Michael Lewis | Database Engineer*
*Entrata*

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-03-08 16:03:20 Re: A simple question: Why 'pg_ctl: command not found...' ?
Previous Message Laurenz Albe 2022-03-08 09:50:04 Re: A simple question: Why 'pg_ctl: command not found...' ?