Re: updatable cursors and ORDER BY

Peter Eisentraut <peter.eisentraut@2ndquadrant.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