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:
>> Aside from the above issue, there's an already known and documented risk if
>> you omit FOR UPDATE, which is that your WHERE CURRENT OF update silently
>> becomes a no-op if someone else has already updated the target row since
>> your query started. It seems like not using FOR UPDATE is sufficiently
>> dangerous practice that requiring it wouldn't be doing our users a
>> disservice.

> Could we implicitly add FOR UPDATE when planning and executing a cursor of a
> sufficiently simple query?

No, not unless you want plain SELECTs to suddenly start blocking each
other.

>> There is one thing we lack in order to go that far, though: the current
>> implementation of WHERE CURRENT OF can cope with inheritance queries,

> How would this implementation relate to the issues described in
> inheritance_planner (which always seemed strange):

Yeah, it is very tempting to think about getting rid of all the
inherited-target cruft (both in the planner, and in the executor's weird
interactions between nodeAppend and execMain) in favor of using a
tableoid junk column to figure out which target rel to update.
However there's one other nasty problem to fix, which is that in an
inherited UPDATE you may need a different update targetlist for each
target relation. I'm not seeing a solution for that yet in the context
of this simplified approach.

regards, tom lane