Hello Everyone,
I'm looking at an easy real-time application using PostgreSQL.
Looking at the pg docs, I see lots of discussion about cases where
MVCC may reflect different versions of the data to different
connections. For example:
http://www.postgresql.org/docs/8.1/static/mvcc.html
"while querying a database each transaction sees a snapshot of data (a
database version) as it was some time ago, regardless of the current
state of the underlying data"
"Read Committed is the default isolation level ... a SELECT query sees
only data committed before the query began; it never sees either
uncommitted data or changes committed during query execution by
concurrent transactions. (However, the SELECT does see the effects of
previous updates executed within its own transaction, even though they
are not yet committed.) In effect, a SELECT query sees a snapshot of
the database as of the instant that that query begins to run."
However, I don't actually see any statements giving guarantees about
when the updated data _does_ become visible.
The central question: So if I successfully commit an update
transaction on one connection, then instantaneously issue a select on
another previously-opened connection, under what circumstances am I
guaranteed that the select will see the effects of the update?
The db is in the default read committed mode. The select is being done
on another long-running connection which has never done any updates,
just selects within its implicit transaction.
Maybe this is the statement I'm looking for: "in Read Committed mode
each new command starts with a new snapshot that includes all
transactions committed up to that instant, subsequent commands in the
same transaction will see the effects of the committed concurrent
transaction". But this statement is just an aside when making a
different point, and I see other statements like "So the whole concept
of "now" is somewhat ill-defined anyway."
"This is not normally a big problem if the client applications are
isolated from each other, but if the clients can communicate via
channels outside the database then serious confusion may ensue." And
communication via outside channels is exactly what the app is doing.
Thanks,
- Marsh