Re: change in LOCK behavior

On Wed, Oct 10, 2012 at 7:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah, I think that last is the key point: this patch was sold on the
> grounds that it wouldn't cause any interesting user-visible behavioral
> change, but your example blows that claim into tiny little pieces.
>
> I'm inclined to think we need to revert this.  The performance gain is
> not worth the prospect of breaking a lot of applications that used to
> work reliably.  Robert?

Yeah, I have to admit that I didn't consider the possibility that a
lock wait might intervene between the first and second snapshots.  But
something about this isn't making sense to me.  The whole idea of that
patch is that we reuse the parse/plan snapshot at execution time.  If
it's wrong to use a snapshot that might be stale at execution time,
then it's equally wrong to use it for parse/plan.  Surely, parse/plan
also requires a lock before doing anything interesting with the table,
so the problem here must be that we're taking the parse/plan snapshot,
then getting the lock (after waiting), then doing something with the
parse/plan snapshot that fails to notice that the snapshot is stale.
However, in this example, the parse/plan stuff we do with the stale
snapshot fails to be noticeable, but if we continue on to execution
then it is noticeable.

But that sounds more like a happy accident than anything else.
Andres' example upthread shows that a slightly different test case
breaks on 9.1 and 9.2, and I suspect it's possible to construct other
examples that behave in surprising ways on 9.1 but *not* on 9.2, by
looking for a scenario where the fact that parse/plan uses a different
snapshot from execution manifests itself as a user-visible
inconsistency.  Reverting the patch has the advantage of being simple,
not requiring a lot of thought, and restoring the historical behavior,
but I'm not that excited about it otherwise.  It seems to me that the
root of the issue here is that people is not that people expect two
snapshots -- indeed, a number of people strongly supported getting rid
of that behavior at the time -- but rather that they expect the
snapshot to be taken after locks are acquired.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company