Re: Commit visibility guarantees

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?
>>
>> If the select is using a snapshot taken later than the commit, it will
>> see the effects of the update.
>
> 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...

You cannot run two selects in one connection at the same time,
see http://www.postgresql.org/docs/current/static/libpq-threading.html

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