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

From: Adrian Klaver <[email-masked]>
Date: Wed, 25 Sep 2024 10:44:42 -0700

On 9/25/24 10:22 AM, Greg Sabino Mullane wrote:
> On Tue, Sep 24, 2024 at 10:28 AM Tom Lane <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     It's even looser than that, really: it's the first statement that
>     requires an MVCC snapshot.
> 
> 
> Hm....so why does "SELECT 1;" work as a transaction start marker then, 
> as opposed to "SHOW work_mem;", which does not? Do we simply consider 
> anything with a SELECT as needing a snapshot?

SELECT some_func();

Where some_func() does something that requires a snapshot.

> Cheers,
> Greg

-- 
Adrian Klaver
[email-masked]

---

From: Ron Johnson <[email-masked]>
Date: Wed, 25 Sep 2024 13:50:33 -0400

> Where some_func() does something that requires a snapshot.
But why does "SELECT 1;" need a snapshot?  Heck, why does "SELECT
<immutable>;" need a snapshot?

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 25 Sep 2024 13:53:37 -0400

> <immutable>;" need a snapshot?

Because we're not going to analyze the statement in the amount of
depth needed to make that distinction before we crank up the
transactional machinery.  If it says SELECT, it gets a snapshot.

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Wed, 25 Sep 2024 13:56:51 -0400

> transactional machinery.  If it says SELECT, it gets a snapshot.

Perfectly reasonable.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 25 Sep 2024 16:22:31 -0400

On Wed, Sep 25, 2024 at 1:53 PM Tom Lane <[email-masked]> wrote:

> Because we're not going to analyze the statement in the amount of depth
> needed to make that distinction before we crank up the

Ok, thanks. So to the original poster's point, perhaps the path with the
least side effects / best Principle of Least Surprise (POLS) support is to
start the transaction, and immediately call a "SELECT 1;" or perhaps better
still, a 'SELECT timeofday();'

Cheers,
Greg

---

From: Ron Johnson <[email-masked]>
Date: Wed, 25 Sep 2024 16:36:20 -0400

> still, a 'SELECT timeofday();'

Since transactions should be "as short as possible, without being too
short", how much time is there between when you run "BEGIN;" and the first
"work statement"?

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 25 Sep 2024 16:49:34 -0400

>> short", how much time is there between when you run "BEGIN;" and the first
I don't know that it really matters. For something automated, it would be a
few milliseconds. Either way, I'm sure most people/apps already think of
the initial 'BEGIN ...' as the start of the transaction, and act
accordingly.

Maybe long-term something like

BEGIN   ISOLATION MODE REPEATABLE READ   SNAPSHOT NOW;

could be useful.

Cheers,
Greg

---

From: Christophe Pettus <[email-masked]>
Date: Wed, 25 Sep 2024 13:53:30 -0700

> On Sep 25, 2024, at 13:49, Greg Sabino Mullane <[email-masked]> wrote:

This might well be a failure of imagination on my part, but when would it pragmatically matter that the snapshot is taken at the first statement as opposed to at BEGIN?

---

From: Ron Johnson <[email-masked]>
Date: Wed, 25 Sep 2024 17:16:19 -0400

On Wed, Sep 25, 2024 at 4:50 PM Greg Sabino Mullane <[email-masked]>
wrote:

> I don't know that it really matters. For something automated, it would be
> a few milliseconds.
>

That's what I'm thinking, too.  It might cause a problem if you're typing
transaction commands in between drinking coffee and poking around other
PgAdmin tabs, but that's *your* fault, not PG's fault.

> Either way, I'm sure most people/apps already think of the initial 'BEGIN
> ...' as the start of the transaction, and act accordingly.
>

Without the "NOW", that's essentially the command used by the legacy rdbms
which I used to work on.

I'm trying to remember, though, if "SET TRANSACTION READ WRITE RESERVING
foo FOR <isolation level>;" (it's syntax for beginning a transaction)
started the transaction, or waited until an "action" statement.  Been too
long.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 25 Sep 2024 17:50:06 -0400

On Wed, Sep 25, 2024 at 4:54 PM Christophe Pettus <[email-masked]> wrote:

> This might well be a failure of imagination on my part, but when would it
> pragmatically matter that the snapshot is taken at the first statement as
> opposed to at BEGIN?

I could imagine lots of cases where you know something is about to happen
(say, a major delete), and you want to get a snapshot of the database as it
existed just before that point. Many people will (quite understandably)
assume that a BEGIN ISOLATION MODE <non read committed>; command would do
just that, and be quite surprised to find that when they actually query the
table in that first process, the rows are not there.

It's certainly a non-intuitive behavior. I understand why we do it this
way, but perhaps this warrants a stronger warning in the docs at least?
It's too late in the day for me to tackle that now, but I'll throw it out
there.

Cheers,
Greg

---

From: "Peter J. Holzer" <[email-masked]>
Date: Wed, 25 Sep 2024 23:55:54 +0200

On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
> This might well be a failure of imagination on my part, but when would
> it pragmatically matter that the snapshot is taken at the first
> statement as opposed to at BEGIN?

It may make a difference if you're comparing timestamps.

For example, if you're using isolation level REPEATABLE READ and
(mistakenly) assume that the snapshot is taken at BEGIN, you would
expect any  transaction_timestamp() written by a different transaction
and readable by this transaction to be earlier than the
transaction_timestamp() of this transaction.

But that's wrong because the other transaction could have happened
entirely in the time between your BEGIN and the statement which actually
triggers the snapshot.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"