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 <m...@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 <m...@mielke.cc>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
