Re: EvalPlanQual seems a tad broken

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Mon, 2009-10-12 at 11:42 -0400, Tom Lane wrote:
>> The problem is that execMain.c is set up to pull as many rows as it can
>> from execution of an EvalPlanQual query. Then, once that's exhausted,
>> it steps to the next result from the original query. So if a row that
>> requires locking joins to more than one row in some other table, you
>> get duplicated output rows.

> Surely the SQL Standard recognises such queries as failing test 23b) on
> 7.12 <query specification> (p379, SQL2008 Foundation). So the query is
> not potentially updateable and should give a runtime error using a FOR
> UPDATE.

You're confusing our implementation of FOR UPDATE with the spec.
As I said earlier, they are only very loosely related. In particular,
our reading of FOR UPDATE is to lock the referenced rows, not to enforce
that they are referenced only once.

regards, tom lane