On Mon, May 18, 2009 at 04:38:36PM -0500, Marsh Ray wrote:
> 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?
>
> 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".
For read committed that sounds like what I'd expect, row level locking
buys you a bit more in implementation terms but just complicates the
formal side as queries don't attempt to do any locking by default. I'm
not aware of any formally defined semantics that PG tries to be a
faithful implementation of---i.e. there may be bugs, but when they're
fixed where are we aiming for. If somebody could come up with a nice
set of inductive definitions I'd be interested in seeing what they
implied as well.
> 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."
Not sure if it's quite as bad as that; transactional semantics go a
long way to making large classes of problems simple. The interactions
between two independent systems that both have transactional semantics
get very awkward. Unbounded rollback being a term I remember, but can't
remember when/why it applies.
> "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.
Yes, things get awkward when you start doing this.
Is there anyway to keep things "inside" the database, using NOTIFY or
somesuch? Could you define what you mean by real-time, do you mean
the strict academic meaning or just that you want "interactive" things
happening and it would be annoying if they were delayed by a few tens of
milliseconds (as opposed to someone dieing because something got delayed
by a millisecond).
--
Sam http://samason.me.uk/