public inbox for [email protected]help / color / mirror / Atom feed
Re: Repeatable Read Isolation Level "transaction start time" 11+ messages / 6 participants [nested] [flat]
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 17:44 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Adrian Klaver @ 2024-09-25 17:44 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; Tom Lane <[email protected]>; +Cc: Wizard Brony <[email protected]>; [email protected] On 9/25/24 10:22 AM, Greg Sabino Mullane wrote: > On Tue, Sep 24, 2024 at 10:28 AM Tom Lane <[email protected] > <mailto:[email protected]>> 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 protected] ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 17:50 Ron Johnson <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Ron Johnson @ 2024-09-25 17:50 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Wed, Sep 25, 2024 at 1:45 PM Adrian Klaver <[email protected]> wrote: > > > On 9/25/24 10:22 AM, Greg Sabino Mullane wrote: > > On Tue, Sep 24, 2024 at 10:28 AM Tom Lane <[email protected] > > <mailto:[email protected]>> 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. > > 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! ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 17:53 Tom Lane <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 2 replies; 11+ messages in thread From: Tom Lane @ 2024-09-25 17:53 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> Ron Johnson <[email protected]> writes: > But why does "SELECT 1;" need a snapshot? Heck, why does "SELECT > <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 ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 17:56 Ron Johnson <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Ron Johnson @ 2024-09-25 17:56 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Wed, Sep 25, 2024 at 1:53 PM Tom Lane <[email protected]> wrote: > Ron Johnson <[email protected]> writes: > > But why does "SELECT 1;" need a snapshot? Heck, why does "SELECT > > <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. > Perfectly reasonable. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> crustacean! ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 20:22 Greg Sabino Mullane <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 11+ messages in thread From: Greg Sabino Mullane @ 2024-09-25 20:22 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Wed, Sep 25, 2024 at 1:53 PM Tom Lane <[email protected]> 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, 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 ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 20:36 Ron Johnson <[email protected]> parent: Greg Sabino Mullane <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Ron Johnson @ 2024-09-25 20:36 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Wed, Sep 25, 2024 at 4:23 PM Greg Sabino Mullane <[email protected]> wrote: > On Wed, Sep 25, 2024 at 1:53 PM Tom Lane <[email protected]> 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, 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();' > 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"? -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> crustacean! ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 20:49 Greg Sabino Mullane <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 2 replies; 11+ messages in thread From: Greg Sabino Mullane @ 2024-09-25 20:49 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> > > 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 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 ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 20:53 Christophe Pettus <[email protected]> parent: Greg Sabino Mullane <[email protected]> 1 sibling, 2 replies; 11+ messages in thread From: Christophe Pettus @ 2024-09-25 20:53 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> > On Sep 25, 2024, at 13:49, Greg Sabino Mullane <[email protected]> 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? ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 21:16 Ron Johnson <[email protected]> parent: Greg Sabino Mullane <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Ron Johnson @ 2024-09-25 21:16 UTC (permalink / raw) To: pgsql-general On Wed, Sep 25, 2024 at 4:50 PM Greg Sabino Mullane <[email protected]> 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 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. > > Maybe long-term something like > > BEGIN ISOLATION MODE REPEATABLE READ SNAPSHOT NOW; > 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. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> crustacean! ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 21:50 Greg Sabino Mullane <[email protected]> parent: Christophe Pettus <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Greg Sabino Mullane @ 2024-09-25 21:50 UTC (permalink / raw) To: Christophe Pettus <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Wed, Sep 25, 2024 at 4:54 PM Christophe Pettus <[email protected]> wrote: > On Sep 25, 2024, at 13:49, Greg Sabino Mullane <[email protected]> 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? 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 ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time" @ 2024-09-25 21:55 Peter J. Holzer <[email protected]> parent: Christophe Pettus <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Peter J. Holzer @ 2024-09-25 21:55 UTC (permalink / raw) To: [email protected] On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote: > > On Sep 25, 2024, at 13:49, Greg Sabino Mullane <[email protected]> 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? 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 protected] | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-09-25 21:55 UTC | newest] Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-25 17:44 Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]> 2024-09-25 17:50 ` Ron Johnson <[email protected]> 2024-09-25 17:53 ` Tom Lane <[email protected]> 2024-09-25 17:56 ` Ron Johnson <[email protected]> 2024-09-25 20:22 ` Greg Sabino Mullane <[email protected]> 2024-09-25 20:36 ` Ron Johnson <[email protected]> 2024-09-25 20:49 ` Greg Sabino Mullane <[email protected]> 2024-09-25 20:53 ` Christophe Pettus <[email protected]> 2024-09-25 21:50 ` Greg Sabino Mullane <[email protected]> 2024-09-25 21:55 ` Peter J. Holzer <[email protected]> 2024-09-25 21:16 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox