Re: Sync Rep: First Thoughts on Code

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>