Re: Repeatable Read Isolation Level "transaction start time"

On Wed, Sep 25, 2024 at 4:54 PM Christophe Pettus <xof(at)thebuild(dot)com> wrote:

> On Sep 25, 2024, at 13:49, Greg Sabino Mullane <htamfids(at)gmail(dot)com> 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