"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Some quick testing seems to show that for the rows on which we were
> blocking, all columns reflect all updates from the concurrent
> transaction on which we were waiting, including columns used in the
> WHERE clause. I'm not sure exactly what other tests might be
> necessary. I'm having trouble coming up with anything which doesn't
> involve a join or subquery, but that could be a failure of
> imagination.
The issue that I was thinking about is that there are actually two
rounds of WHERE testing involved in a READ COMMITTED update: first
we fetch a row that matches the WHERE clause *in the query snapshot*,
and then we fetch its most up-to-date version and recheck the WHERE
condition for that. If the updated version no longer satisfies WHERE
we ignore it. The trouble with this is that the same transaction that
changed that row to not satisfy WHERE might have also changed some
other row so that it now *does* satisfy WHERE, but we won't ever find
that other row because in the query snapshot it doesn't pass the WHERE.
The "website" example in the docs is meant to illustrate this hazard.
regards, tom lane
