X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 79C335E37CB for ; Tue, 17 Aug 2004 11:04:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86330-01 for ; Tue, 17 Aug 2004 14:04:06 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 90C2D5E3639 for ; Tue, 17 Aug 2004 11:04:03 -0300 (ADT) Received: (qmail 16052 invoked by uid 500); 17 Aug 2004 14:12:29 -0000 Date: Tue, 17 Aug 2004 09:12:29 -0500 From: Bruno Wolff III To: Markus Bertheau Cc: pgsql-sql@postgresql.org Subject: Re: SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo Message-ID: <20040817141229.GA15770@wolff.to> Mail-Followup-To: Markus Bertheau , pgsql-sql@postgresql.org References: <1092740863.2627.17.camel@dicaprio.akademie1.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1092740863.2627.17.camel@dicaprio.akademie1.de> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/164 X-Sequence-Number: 18661 On Tue, Aug 17, 2004 at 13:07:43 +0200, Markus Bertheau 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.