X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 174929FA5CD for ; Thu, 20 Jul 2006 04:31:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 16190-09 for ; Thu, 20 Jul 2006 07:31:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx00.ext.bfk.de (mx00.ext.bfk.de [217.29.46.125]) by postgresql.org (Postfix) with ESMTP id D832F9FA502 for ; Thu, 20 Jul 2006 04:31:18 -0300 (ADT) Received: from fweimer by bfk.de with local id 1G3T08-0004bu-4M for pgsql-sql@postgresql.org; Thu, 20 Jul 2006 09:31:16 +0200 To: pgsql-sql@postgresql.org Subject: Re: INSERT/UPDATEs cycles and lack of phantom locking References: <82zmf60zf4.fsf@mid.bfk.de> <25634.1153319178@sss.pgh.pa.us> <827j291vo9.fsf@mid.bfk.de> From: Florian Weimer Date: Thu, 20 Jul 2006 09:31:16 +0200 In-Reply-To: <827j291vo9.fsf@mid.bfk.de> (Florian Weimer's message of "Wed, 19 Jul 2006 17:06:14 +0200") Message-ID: <823bcw20mz.fsf@mid.bfk.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/259 X-Sequence-Number: 25833 * Florian Weimer: > In addition, it occurred to me that I get the INSERT failure only if > there is a suitable PRIMARY KEY/UNIQUE constraint on the table. I > haven't got that in all cases, so I need that advisory locking anyway, It seems that LOCK TABLE ... IN EXCLUSIVE MODE does exactly what I need: it locks out itself (and write access), but not read access to the table. And deadlocks are detected as well. Yay! -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47 tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99