Re: Serializable read only deferrable- implications

[Michael Lewis <mlewis@xxxxxxxxxxx>]

On Tue, Mar 8, 2022 at 9:27 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

"PostgreSQL maintains this guarantee even when providing the strictest
level of transaction isolation through the use of an innovative
Serializable Snapshot Isolation (SSI) level."

Then:

https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE

and

https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY

Thanks to you both. If other concurrent sessions are using default isolation level of Read committed, would putting long running reports (read-only) into that read-only serializable deferrable mode be impactful at all?

The documentation says that a transaction ID is only assigned to a connection once a write is done, but is the assignment or not of a txn id actually impactful on anything? I ask partly because it doesn't seem possible to reset that once assigned, through discard all; or something else like that which might be used by a connection pooler such as pg bouncer. is there any way to check if a session has "done writes/updates up to this point"? It seems pg_my_temp_schema() also returns the same value even after 'discard temp' or 'discard all' is executed. That was surprising to me, but would it be considered an issue by anyone?