Re: Sync Rep: First Thoughts on Code

It's a real promise. The reason you're getting hand-wavy answers is
because it's such a basic requirement that I'm trying to point out
just how fundamental a requirement it is.

If you want to see the actual code which guarantees this take a look
around the code for procarray - in particular the code for taking a
snapshot. There are comments there about what locks are needed when
committing and when taking a snapshot and why. But it's quite technical.

--
Greg

On 15 Dec 2008, at 02:03, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:

> Greg Stark wrote:
>> When the database says the data is committed it has to mean the
>> data is really committed. Imagine if you looked at a bank account
>> balance after withdrawing all the money and saw a balance which
>> didn't reflect the withdrawal and allowed you to withdraw more
>> money again...
>
> Within the same session - sure. From different sessions? PostgeSQL
> MVCC let's you see an older snapshot, although it does prefer to
> have the latest snapshot with each command.
>
> For allowing to withdraw more money again, I would expect some sort
> of locking "SELECT ... FOR UPDATE;" to be used. This lock then
> forces the two transactions to become serialized and the second will
> either wait for the first to complete or fail. Any banking program
> that assumed that it could SELECT to confirm a balance and then
> UPDATE to withdraw the money as separate instructions would be a bad
> banking program. To exploit it, I would just have to start both
> operations at the same time - they both SELECT, they both see I have
> money, they both give me the money and UPDATE, and I get double the
> money (although my balance would show a big negative value - but I'm
> already gone...). Database 101.
>
> When I asked for "does PostgreSQL guarantee this?" I didn't mean
> hand waving examples or hand waving expectations. I meant a pointer
> into the code that has some comment that says "we want to guarantee
> that a commit in one session will be immediately visible to other
> sessions, and that a later select issued in the other sessions will
> ALWAYS see the commit whether 1 nanosecond later or 200 seconds
> later" Robert's expectation and yours seem like taking this
> "guarantee" for granted rather than being justified with design
> intent and proof thus far. :-) Given my experiment to try and force
> it to fail, I can see why this would be taken for granted. Is this a
> real promise, though? Or just a unlikely scenario that never seems
> to be hit?
>
> To me, the question is relevant in terms of the expectations of a
> multi-replica solution. We know people have the expectation. We know
> it can be convenient. Is the expectation valid in the first place?
>
> I've probably drawn this question out too long and should do my own
> research and report back... Sorry... :-)
>
> Cheers,
> mark
>
> --
> Mark Mielke <mark(at)mielke(dot)cc>
>