Mark Mielke wrote:
> 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?
Yes.
In a nutshell, commit works like this:
1. Write and flush WAL record about the commit
2. Mark the transaction as committed in clog
3. Remove the xid from the shared memory ProcArray.
4. Release locks and other resources
5. Reply to client that the transaction has been committed.
After step 3, any backend taking a snapshot will see the transaction as
committed. Since we only reply to the client at step 5, it is guaranteed
that a transaction beginning after step 5, as well as an already open
transaction taking a new snapshot (ie. running a new command in read
committed mode) after that will see the transaction as committed.
The relevant code is in CommitTransaction() in xact.c.
> To me, the question is relevant in terms of the expectations of a
> multi-replica solution. We know people have the expectation.
Yeah, I think Robert is right. We should reserve the term "synchronous
replication" for the mode where that guarantee holds for the slave as well.
In fact, waiting for reply from standby server before acknowledging a
commit to the client is a bit pointless otherwise. It puts you in a
strange situation, where you're waiting for the commits in normal
operation, but if there's a network glitch or the standby goes down,
you're willing to go ahead without it. You get a high guarantee that
your data is up-to-date in the standby, except when it isn't. Which
isn't much of a guarantee.
But with hot standby, it makes a lot of sense. The guarantee is that if
the standby is accepting queries, it's up-to-date with the primary.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com