Re: Sync Rep: First Thoughts on Code - Mailing list pgsql-hackers

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>