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? A brief look into history of
CheckSelectLocking() suggests that it might never have been correct.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
