Re: "ORDER BY" clause prevents "UPDATE WHERE CURRENT OF"

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Well, it's contrary to SQL spec, which says that sufficiently simple
>> cursors should be updatable even if they don't say FOR UPDATE.
>>
>> However ... the more I think about it, the more I wonder if we shouldn't
>> rip out the current search-the-plan-tree hack and allow WHERE CURRENT OF
>> *only* for cursors that say FOR UPDATE.

> It is tempting since it would make application code which looped updating
> WHERE CURRENT OF semantically equivalent to UPDATE FROM. It seems better to
> have one set of functionality rather than two similar but subtly different
> sets of functionality available depending on the coding style.

After playing around with this for awhile I realized that there really
would be a functional loss if we remove support for WHERE CURRENT OF
with non-FOR-UPDATE cursors. Namely, that a non-FOR-UPDATE cursor is
insensitive to subsequent updates, which sometimes is handy. There are
examples of both cases in the portals.sql regression test.

So what I now propose is:

1. If the cursor includes FOR UPDATE/FOR SHARE, use the ExecRowMark
technique to get the target row identity. In this path we fail if there
is not exactly one FOR UPDATE reference to the UPDATE's target table.
(An interesting property here is that you can update from a self-join,
if you mark only one arm of the join as FOR UPDATE. See example in
attached regression test additions.)

2. If the cursor doesn't have FOR UPDATE/SHARE, use the existing code.
In this path we fail if the plan is "too complicated". However, it
shouldn't fail for any case that is simply updatable according to the
SQL spec.

We should revise the documentation to make it very clear that FOR UPDATE
is the preferred way, but that sometimes you might need the other.

Attached is a draft patch that is code-complete but lacks documentation.
The patch is against CVS HEAD, ie, it assumes the SELECT FOR UPDATE
inheritance fixes I made earlier today.

regards, tom lane