@marsh5143: "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:
<url>
"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
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @sam: "<quote>
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.
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.
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  <url>
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @marsh5143: "<quote>
Definitely true, but I don't think it's really the issue here. The app
does have a hard real-time deadline, but the deadline is generally
quite "easy" for such a system. Like any web app, there will be a hard
deadline to meet before the browser times out, though the timeout
value 60 or more seconds.
There is a near-instantaneous context switch from the 'update' process
to the 'select' process, and I am wondering if some behaviors change
with that tight scheduling.
- Marsh
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @sam: "<quote>
Even then it's not useful to class it as real-time; nothing "bad"
happens if you don't get a response before timeout the user just gets an
error message.  Real-time applies when if you don't get a response the
plane crashes or a heart stops because the pacemaker hasn't put out a
signal in time.
  Sam  <url>
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @marsh5143: "<quote>
Think of a network router: it is neither a pacemaker nor an airplane
but is definitely a real-time system. If it cannot complete its
processing on time, not only is it useless, it may actually bring down
significant numbers of other systems.
- Marsh
=20
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @marsh5143: "<quote>
Do you know if this kind of concurrency test is included in pg's
regression tests?
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?
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.
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.
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 (<email_address>)
To make changes to your subscription:
<url>"
    @tgl: "Marsh Ray <email_address> writes:
<quote>
If the select is using a snapshot taken later than the commit, it will
see the effects of the update.
The point that the remark about "ill-defined" behavior is trying to make
is that the application could try to compare the results of queries done
using different snapshots, and those results might be different.
			regards, tom lane
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @marsh5143: "<quote>
Great! Just the kind of definitive answer I was looking for.
Now I just need to find a comprehensive list of all the things that
could cause an older snapshot to be retained, and ensure that none of
them could possibly be occurring on this connection.
This is a connection kept open for extended periods, and used
mutithreadedly for selects only. Do you suppose a long-running
concurrent select on another thread could be holding back the snapshot
for the whole connection? Hmm...
- Marsh
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @alvherre: "Marsh Ray escribi�:
<quote>
On a serializable transaction all queries will use the same snapshot
taken when the first query is executed.  Otherwise (read committed), a
new query always gets a fresh one.
(Old snapshots are also used for stuff like cursors that remain open,
but that's not the case here.)
Alvaro Herrera                                <url>
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @laurenzalbe: "<quote>
You cannot run two selects in one connection at the same time,
see <url>
One connection belongs to one backend process that can do one thing
at a time. If you want concurrency, you must use more than one
connection.
If the isolation mode is "read committed", then the snapshot of the
query will be taken at query start time.
So there is no need to worry.
Yours,
Laurenz Albe
=20
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
    @simon: "<quote>
I have some code that will allow you to reuse snapshots from one session
to another, which could help your multiple threads see a consistent view
of the database. It isn't updated as yet for 8.2+.
Please contact me off-list if you're interested.
I hope it will be part of PostgreSQL one day.
 Simon Riggs           <url>
 PostgreSQL Training, Services and Support
Sent via pgsql-general mailing list (<email_address>)
To make changes to your subscription:
<url>"
{'source': 'USENET', 'other_information': '{"reply_count": 10, "forum_name": "pgsql.general.20140811.mbox"}', 'file': 'data/output/usenet-pgsql.general.parquet'}