Re: updatable cursors and ORDER BY

On Wed, May 9, 2018 at 7:43 PM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> 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.
> """
>
> Indeed, grouping in cursors declared FOR UPDATE is rejected:
>
> DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1 FOR UPDATE;
> ERROR: FOR UPDATE is not allowed with GROUP BY clause
>
> 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
>
> Is this note outdated?
>

I'd say the note covers its scope of influence properly. Any query can be
supplied to the cursor at declaration and it won't complain; since the
problematic structure of the source query only comes into play for UPDATE
and DELETE. If all the caller does is print the results it won't matter.

The error for GROUP BY is in the scope of the original select statement.
If the FOR UPDATE was omitted the query would work but, as the note says,
the subsequent UPDATE/DELETE would (likely?) fail.

A query with multiple tables would result in the same outcome - failure
only if UPDATE/DELETE.

I'm having trouble imaging a failure mode for ORDER BY in this context...or
how the other two options are plan-choice dependent.

I'm leaning toward the note needing to be made either more specific or more
generic. This attempt to clarify implementation details only in part seems
worse than just saying UPDATE/DELETE WHERE CURRENT OF requires a simply
updatable query (though it is not strictly enforced) and that FOR UPDATE
should be used unless the implications of the data behind the cursor are
well understood in the specific situation.

I don't quite understand enough to know what the "fully accurate detail"
version looks like and whether it is too much to include in the
documentation.

David J.