From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200212061910.gB6JAkY06339@candle.pha.pa.us>
Subject: Re: SELECT FOR UPDATE locks whole table
In-Reply-To: <20021206190303.98BD31BA6@druid.net>
To: "D'Arcy J.M. Cain" <darcy@druid.net>
Date: Fri, 6 Dec 2002 14:10:46 -0500 (EST)
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


It should lock only the rows you retrieved, but I have no idea how FOR
UPDATE and INTO TEMP behave.  My guess is that it should work fine, but
I have never seen those two used together before.

---------------------------------------------------------------------------

D'Arcy J.M. Cain wrote:
> 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.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073