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
