Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

On Tue, Aug 17, 2004 at 13:07:43 +0200, Markus Bertheau <twanger@bluetwanger.de> wrote:
> Hi,
> 
> why is the following query not allowed:
> 
> SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
> 'foo' FOR UPDATE OF classes) AS foo
> 
> It's clear which rows should be locked here, I think.

Even if it was allowed, it probably wouldn't be good enough because it won't
protect against newly inserted records. You really want to lock the table
against concurrent updates when doing this if concurrent updates can cause
a problem. You really want predicate locking, to lock any old or new rows
with name = 'foo', but postgres doesn't have that capability.