Content-Type: text/plain;
  charset="iso-8859-1"
From: "D'Arcy J.M. Cain" <darcy@druid.net>
To: pgsql-sql@PostgreSQL.org
Subject: SELECT FOR UPDATE locks whole table
Date: Fri, 6 Dec 2002 14:03:02 -0500
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <20021206190303.98BD31BA6@druid.net>

We have the following query:

SELECT certificate_id
    INTO TEMP TABLE x_certs
    FROM certificate
    WHERE cert_status = 0 AND
        certificate_id BETWEEN 1111 AND 2222 AND
        client_id IN (1, 2, 3)
    ORDER BY certificate_id
    FOR UPDATE;

Is there any reason that this query should lock the entire certificate table? 
 Is there something strange because of the IN clause or because it is going 
into a temporary table?  This is a production server running 7.2.2 so perhaps 
it is fixed in 7.3.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.