Subject: Re: Repeatable Read Isolation Level "transaction start time"

From: Ron Johnson <[email-masked]>
Date: Tue, 24 Sep 2024 08:59:01 -0400

On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston <
[email-masked]> wrote:

> On Monday, September 23, 2024, Wizard Brony <[email-masked]> wrote:
>
>>
>> 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?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 24 Sep 2024 09:06:43 -0700

On 9/24/24 05:59, Ron Johnson wrote:
> On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     On Monday, September 23, 2024, Wizard Brony <[email-masked]
>     <mailto:[email-masked]>> 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;
>         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;

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

-- 
Adrian Klaver
[email-masked]

---

From: Ron Johnson <[email-masked]>
Date: Tue, 24 Sep 2024 12:12:07 -0400

On Tue, Sep 24, 2024 at 12:06 PM Adrian Klaver <[email-masked]>
wrote:

> <

Makes sense.  Never would have occurred to me to try and change the
isolation level using a second SET TRANSACTION statement, though.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 24 Sep 2024 09:16:50 -0700

> isolation level using a second SET TRANSACTION statement, though.

 From the docs:

The SET TRANSACTION command sets the characteristics of the current 
transaction. It has no effect on any subsequent transactions. SET 
SESSION CHARACTERISTICS sets the default transaction characteristics for 
subsequent transactions of a session. These defaults can be overridden 
by SET TRANSACTION for an individual transaction.