Re: Commit visibility guarantees

Marsh Ray escribió:
> On Mon, May 18, 2009 at 6:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Marsh Ray <marsh5143(at)gmail(dot)com> writes:
> >> 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.

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 http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support