On Tue, Sep 24, 2024 at 12:06 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 9/24/24 05:59, Ron Johnson wrote: > > On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston > > <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote: > > > > On Monday, September 23, 2024, Wizard Brony <wizardbr...@gmail.com > > <mailto:wizardbr...@gmail.com>> wrote: > > > > > https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ > < > https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ > > > > > > The PostgreSQL documentation for the Repeatable Read Isolation > > Level states the following: > > > > “UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE > > commands behave the same as SELECT in terms of searching for > > target rows: they will only find target rows that were committed > > as of the transaction start time.” > > > > What is defined as the "transaction start time?" When I first > > read the statement, I interpreted it as the start of the > > transaction: > > > > BEGIN; > > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > > > > But in my testing, I find that according to that statement, the > > transaction start time is actually "the start of the first > > non-transaction-control statement in the transaction" (as > > mentioned earlier in the section). Is my conclusion correct, or > > am I misunderstanding the documentation? > > > > > > Probably, since indeed the transaction cannot start at begin because > > once it does start it cannot be modified. > > > > Huh? > > BEGIN; > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > > I read it as the transaction does not start at BEGIN because if it did > you could not SET TRANSACTION to change it's characteristics. > > The docs go into more detail: > > https://www.postgresql.org/docs/current/sql-set-transaction.html > > The transaction isolation level cannot be changed after the first query > or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, > FETCH, or COPY) of a transaction has been executed. > > > So: > > begin ; > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > SET > SET TRANSACTION ISOLATION LEVEL READ COMMITTED; > SET > select * from csv_test ; > [...] > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query > Makes sense. Never would have occurred to me to try and change the isolation level using a second SET TRANSACTION statement, though. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> crustacean!
