Serializable read only deferrable- implications

On Tue, 2022-03-08 at 06:29 -0700, Michael Lewis wrote:

"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;

A transaction started with the first statement will not take any SI locks, nor
will it ever receive a serialization error.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

A transaction started with the first statement will not take any SI locks,
nor
will it ever receive a serialization error.

What is the meaning of SI? Anything you are aware of in source code or a
blog post that discusses this?

Michael Lewis <mlewis@entrata.com> writes:

A transaction started with the first statement will not take any SI locks,
nor will it ever receive a serialization error.

What is the meaning of SI? Anything you are aware of in source code or a
blog post that discusses this?

There's src/backend/storage/lmgr/README-SSI, which might or might
not be the level of detail you are looking for.

regards, tom lane

On 3/8/22 08:21, Tom Lane wrote:

Michael Lewis <mlewis@entrata.com> writes:

A transaction started with the first statement will not take any SI locks,
nor will it ever receive a serialization error.

What is the meaning of SI? Anything you are aware of in source code or a
blog post that discusses this?

There's src/backend/storage/lmgr/README-SSI, which might or might
not be the level of detail you are looking for.

If you want to ease into the above:

https://www.postgresql.org/docs/current/mvcc-intro.html

"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

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Mar 8, 2022 at 9:27 AM Adrian Klaver <adrian.klaver@aklaver.com>
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?

On 3/8/22 10:47 AM, Michael Lewis wrote:

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?

I'm not following what you are asking or trying to achieve. For instance
how pg_my_temp_schema() fits into this? You will need to provide a more
complete description of what it is you are doing.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Mar 8, 2022 at 11:47 AM Michael Lewis <mlewis@entrata.com> wrote:

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?

I'm tending to agree that this documentation could be improved (I too am
not totally fluent on how the different modes interact with each other).
In this specific case the docs say:
"When all three of these properties are selected for a transaction, the
transaction may block when first acquiring its snapshot,..."
They fail to say what they are blocking on and thus what has to occur in
the system before the lock can be acquired and the transaction continues
onward. I suspect it is simply that any not yet committed transactions
become committed or reverted. Whether those transactions were run under
serializable, read committed, or repeatable read would be immaterial.

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?

I'm getting an impression that you have confusion regarding transactions
and sessions, since all of this concurrency stuff only cares about the
transactions but you now start talking about session state and comparing
what you would see at initial login versus what you see after resetting.

The temporary schema for a session is assigned once the first temporary
object is created. At which point that assignment of the random temporary
schema is fixed for the duration of the session. It never gets reset as
there is no point.

Whether a session has done "write/updates up to this point" is likewise not
something that can be readily ascertained nor, from what I can tell, would
doing so be a useful exercise. There may be some reason to inspect whether
a transaction has performed a write/update, but even that seems unlikely.

David J.

Sorry for the confusion I caused. The question about connection management
and pg bouncer was a distraction and should have been addressed separately.

When having a mixture of OLTP and OLAP on the same primary databases, is
there any benefit to declaring long running report type connections
as SERIALIZABLE READ ONLY DEFERRABLE in terms of impact on logical or
physical replication, autovacuum, etc even if the much heavier OLTP
traffic is still running as the default read committed mode?

If the OLAP queries are moved to a physical read replica, I understand from
this post (
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
) that there are chances that a query will be killed on the replica even
with hot_standby_feedback is turned on. With them running on the same
server, is the main concern (other than load) that vacuum type cleanup is
delayed?

Maybe to sum up- If a long running report type query is run in default
"read committed" mode and uses no temp tables / does no writes, would there
be a benefit or change in behavior when using SERIALIZABLE READ ONLY
DEFERRABLE mode?