On Mon, May 18, 2009 at 5:14 PM, Sam Mason <sam@xxxxxxxxxxxxx> wrote: > 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. Do you know if this kind of concurrency test is included in pg's regression tests? >> 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. At some point, a committed update has got to show up in other connections' selects or users would obviously complain. However, is the lag guaranteed to be zero? >> "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. Yep, awkward city. I'm just trying to figure out what guarantees I do get out of pg in order to analyze it going forward. > Is there anyway to keep things "inside" the database, using NOTIFY or > somesuch? Unfortunately no, the db really is between external actors that also have their own kernel-object-fast signaling mechanism. The data currently being passed via the db could be duplicated over this side channel, but it would be far more interesting to learn if a basic assumption was wrong. > 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). It is real-time in the academic definition, but most deadlines are measured in seconds. Not too different than a web app really. - Marsh -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
