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.
Can we add something to check for uniqueness of the join and throw an
error when we detect this situation? Seems like a better general
solution.
> I do not see any very good way around #2. I'm tempted to propose
> that we just forbid SRFs in the targetlist of a FOR UPDATE query.
> This could be justified on the same grounds that we forbid aggregate
> functions there, ie, they destroy the one-to-one correspondence between
> table rows and SELECT output rows. If you really had to have it you
> could do something like
Until we have a way to specify that the return set from an SRF is
unique, that seems the only way. That would be desirable because it
would allow FunctionScans to be join removed as well.
-- Simon Riggs www.2ndQuadrant.com