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@mark.mielke.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@mielke.cc>
>