Re: Serializable read only deferrable- implications

On Tue, Mar 8, 2022 at 11:47 AM Michael Lewis <mlewis(at)entrata(dot)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.