Re: Repeatable Read Isolation Level "transaction start time"

On 10/5/24 15:25, Peter J. Holzer wrote: On 2024-10-05 17:03:08 -0400, Tom Lane wrote: "Peter J. Holzer" writes: Again, I'm not arguing for such a change, but I'm wondering if recording transaction_timestamp just after the snapshot might be a safe change or whether that might break some assump

Re: Repeatable Read Isolation Level "transaction start time"

On 2024-10-05 17:03:08 -0400, Tom Lane wrote: > "Peter J. Holzer" writes: > > Again, I'm not arguing for such a change, but I'm wondering if recording > > transaction_timestamp just after the snapshot might be a safe change or > > whether that might break some assumption that programmers can curre

Re: Repeatable Read Isolation Level "transaction start time"

"Peter J. Holzer" writes: > Again, I'm not arguing for such a change, but I'm wondering if recording > transaction_timestamp just after the snapshot might be a safe change or > whether that might break some assumption that programmers can currently > make. As I mentioned upthread, we currently pr

Re: Repeatable Read Isolation Level "transaction start time"

On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote: > On 10/5/24 02:14, Peter J. Holzer wrote: > > On 2024-09-25 18:09:44 -0400, Tom Lane wrote: > > > "Peter J. Holzer" writes: > > > Admittedly, that would normally not be a very long interval if BEGIN > > > did both things ... but on a busy system

Re: Repeatable Read Isolation Level "transaction start time"

Greg Sabino Mullane writes: > All we can guarantee > via pg_stat_activity is that if xact_start and query_start *are* identical, > no snapshot has been granted yet, Surely that's not true either. xact_start = query_start implies that the current statement is the first in its transaction (assumin

Re: Repeatable Read Isolation Level "transaction start time"

While working on a doc patch for this, I realized that the situation is worse than I originally thought. This means that anyone relying on pg_stat_activity.xact_start is not really seeing the time of the snapshot. They are seeing the time that BEGIN was issued. Further, there is no way to tell (AFA

Re: Repeatable Read Isolation Level "transaction start time"

On 10/5/24 02:14, Peter J. Holzer wrote: On 2024-09-25 18:09:44 -0400, Tom Lane wrote: "Peter J. Holzer" writes: Admittedly, that would normally not be a very long interval if BEGIN did both things ... but on a busy system you could lose the CPU for awhile in between. Assuming that the sy

Re: Repeatable Read Isolation Level "transaction start time"

On 2024-09-25 18:09:44 -0400, Tom Lane wrote: > "Peter J. Holzer" writes: > > 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 op

Re: Repeatable Read Isolation Level "transaction start time"

"Peter J. Holzer" writes: > 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

Re: Repeatable Read Isolation Level "transaction start time"

On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote: > > On Sep 25, 2024, at 13:49, Greg Sabino Mullane wrote: > > BEGIN ISOLATION MODE REPEATABLE READ SNAPSHOT NOW; > > This might well be a failure of imagination on my part, but when would > it pragmatically matter that the snapshot is tak

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 4:54 PM Christophe Pettus wrote: > On Sep 25, 2024, at 13:49, Greg Sabino Mullane wrote: > > BEGIN ISOLATION MODE REPEATABLE READ SNAPSHOT NOW; > > This might well be a failure of imagination on my part, but when would it > pragmatically matter that the snapshot is ta

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 4:50 PM Greg Sabino Mullane wrote: > 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"? >>> >> > I don't know that it really matters. For something autom

Re: Repeatable Read Isolation Level "transaction start time"

> On Sep 25, 2024, at 13:49, Greg Sabino Mullane wrote: > BEGIN ISOLATION MODE REPEATABLE READ SNAPSHOT NOW; 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?

Re: Repeatable Read Isolation Level "transaction start time"

> > 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"? >> > I don't know that it really matters. For something automated, it would be a few milliseconds. Either way, I'm sure most p

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 4:23 PM Greg Sabino Mullane wrote: > On Wed, Sep 25, 2024 at 1:53 PM Tom Lane 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 >> transactional machinery. If it says SELECT, it g

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 1:53 PM Tom Lane 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 > transactional machinery. If it says SELECT, it gets a snapshot. > Ok, thanks. So to the original poster's point, p

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 1:53 PM Tom Lane wrote: > Ron Johnson writes: > > But why does "SELECT 1;" need a snapshot? Heck, why does "SELECT > > ;" 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

Re: Repeatable Read Isolation Level "transaction start time"

Ron Johnson writes: > But why does "SELECT 1;" need a snapshot? Heck, why does "SELECT > ;" 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 snaps

Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 1:45 PM Adrian Klaver wrote: > > > On 9/25/24 10:22 AM, Greg Sabino Mullane wrote: > > On Tue, Sep 24, 2024 at 10:28 AM Tom Lane > > wrote: > > > > It's even looser than that, really: it's the first statement that > > requires an MVCC sn

Re: Repeatable Read Isolation Level "transaction start time"

On 9/25/24 10:22 AM, Greg Sabino Mullane wrote: On Tue, Sep 24, 2024 at 10:28 AM Tom Lane > wrote: It's even looser than that, really: it's the first statement that requires an MVCC snapshot. Hmso why does "SELECT 1;" work as a transaction start marker

Re: Repeatable Read Isolation Level "transaction start time"

On Tue, Sep 24, 2024 at 10:28 AM Tom Lane wrote: > It's even looser than that, really: it's the first statement that > requires an MVCC snapshot. Hmso 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

Re: Repeatable Read Isolation Level "transaction start time"

On 9/24/24 09:12, Ron Johnson wrote: Makes sense.  Never would have occurred to me to try and change the isolation level using a second SET TRANSACTION statement, though. From the docs: https://www.postgresql.org/docs/current/sql-set-transaction.html The SET TRANSACTION command sets the char

Re: Repeatable Read Isolation Level "transaction start time"

On Tue, Sep 24, 2024 at 12:06 PM Adrian Klaver wrote: > On 9/24/24 05:59, Ron Johnson wrote: > > On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston > > mailto:david.g.johns...@gmail.com>> wrote: > > > > On Monday, September 23, 2024, Wizard Brony > > wrote: >

Re: Repeatable Read Isolation Level "transaction start time"

On 9/24/24 05:59, Ron Johnson wrote: On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Monday, September 23, 2024, Wizard Brony mailto:wizardbr...@gmail.com>> wrote: https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABL

Re: Repeatable Read Isolation Level "transaction start time"

Wizard Brony writes: > 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

Re: Repeatable Read Isolation Level "transaction start time"

On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, September 23, 2024, Wizard Brony wrote: > >> >> https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ >> >> The PostgreSQL documentation for the Repeatable Read Isolation Lev

Re: Repeatable Read Isolation Level "transaction start time"

On Monday, September 23, 2024, Wizard Brony 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

Repeatable Read Isolation Level "transaction start time"

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 ta