Thread: SET TRANSACTION SNAPSHOT sounds like replicated environment but isn't
SET TRANSACTION SNAPSHOT sounds like replicated environment but isn't
From
PG Doc comments form
Date:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/sql-set-transaction.html Description: Hello, the wording on the SET TRANSACTION SNAPSHOT left me a bit confused. It says "To begin a new transaction with the same snapshot as an already existing transaction" and it feels like basically taking over an existing session/transaction or being able to replicate a transaction from that snapshot on. But I found that a temporary table created in Session 1 with transaction A is not available from Session 2 with transaction B with snapshot taken over from transaction A. I guess "obviously", as using the snapshot does not give me the same permissions as the original transaction starter so I can't replicate everything that Session 1 would have been able to do in transaction A. Also I couldn't find what the snapshot includes. Maybe it includes said temporary table but the second session has no permissions to view it. Maybe not. But I guess that's not actually important. Maybe it is possible to add a sentence that starting from the snapshot of another transaction does not include inheriting any permissions (or temporary resources) on that snapshot. It's kinda obvious for different users with different permission levels but especially the temporary table case looks (at least with squinted eyes) like it could work. All the best Mario Wenzel
Re: SET TRANSACTION SNAPSHOT sounds like replicated environment but isn't
From
"David G. Johnston"
Date:
On Wed, May 11, 2022 at 7:33 AM PG Doc comments form <noreply@postgresql.org> wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/14/sql-set-transaction.html
Description:
Hello,
the wording on the SET TRANSACTION SNAPSHOT left me a bit confused. It says
"To begin a new transaction with the same snapshot as an already existing
transaction" and it feels like basically taking over an existing
session/transaction or being able to replicate a transaction from that
snapshot on.
I'm not sure what a final documentation patch might look like but this section can rightfully assume some prior knowledge about how the system works with regards to snapshots. In particular, a snapshot is effectively a single value that a session maintains that lets it compute whether a given transaction id is visible or not. So, "whenever you give me data only do so up to this point-in-time". Understanding that dynamic should lead one to conclude that just having a shared since of "point-in-time" doesn't have anything to do with permissions to interact with specific objects in the first place. And, for temporary tables, to break the session isolation that it is assumed the reader is aware of.
David J.