Re: updatable cursors and ORDER BY

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> The DECLARE reference page says:
> """
> Another reason to use FOR UPDATE is that without it, a subsequent WHERE
> CURRENT OF might fail if the cursor query does not meet the SQL
> standard's rules for being “simply updatable” (in particular, the cursor
> must reference just one table and not use grouping or ORDER BY). Cursors
> that are not simply updatable might work, or might not, depending on
> plan choice details; so in the worst case, an application might work in
> testing and then fail in production.
> """

> But ORDER BY is allowed, contrary to what that note appears to say:
> DECLARE c CURSOR FOR SELECT f1, f2 FROM uctest ORDER BY f1 FOR UPDATE;
> -- no error, works fine

I think you misread that note: it says nothing about what is allowed
in DECLARE CURSOR per se. It is talking about whether you can apply
UPDATE/DELETE WHERE CURRENT OF to that cursor. Moreover, what it says
is that if you use FOR UPDATE then such an UPDATE/DELETE *will* work,
whereas without it we don't guarantee that.

> Is this note outdated? A brief look into history of
> CheckSelectLocking() suggests that it might never have been correct.

The code that's relevant to this is in execCurrentOf(): see the bit about

* We have two different strategies depending on whether the cursor uses
* FOR UPDATE/SHARE or not. The reason for supporting both is that the
* FOR UPDATE code is able to identify a target table in many cases where
* the other code can't, while the non-FOR-UPDATE case allows use of WHERE
* CURRENT OF with an insensitive cursor.

regards, tom lane