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.

Fri, 6 Dec 2002 14:03:02 -0500
"D'Arcy J.M. Cain" <darcy@druid.net>
SELECT FOR UPDATE locks whole table

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

Fri, 6 Dec 2002 14:10:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SELECT FOR UPDATE locks whole table
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> 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?

It should only lock the selected rows ... and does, in a quick test
here.  Would you provide the test case that makes you think it's doing
otherwise?

			regards, tom lane

Fri, 06 Dec 2002 18:00:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SELECT FOR UPDATE locks whole table 
On December 6, 2002 02:10 pm, Bruce Momjian wrote:
> 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.

Turns out that it wasn't the SELECT ... FOR UPDATE that was causing the 
problem.  I did a test like this.

> > 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;

Basically this query after a BEGIN TRANSACTION except without the INTO part.  
Then I went to another window and tried to update two certificates, one 
inside and one outside the range.  The first failed and the second succeeded 
as expected.  I then updated one of the certs in the range.  After that I 
could not update any certificates until I closed the transaction.  Very weird.

I then built a new database and repeated the experiment with fresh, simple 
tables and was able to confirm that normally PostgreSQL does NOT have this 
behaviour so then I started thinking about differences between the simple 
setup and our real production setup.  One thing that I thought of was that 
the real database has this trigger on certificate.

CREATE TRIGGER mk_cardnum
    BEFORE INSERT OR UPDATE ON certificate
    FOR EACH ROW
    EXECUTE PROCEDURE mk_cardnum (cardnum, certificate_id, validation);

mk_cardnum is a C function that reads certificate_id and validation and 
writes something into cardnum.  My understanding is that this only affects 
the row(s) being updated.  It must since this table has over seven million 
records and we would notice if it took minutes to do a simple update.

I couldn't find anything in the docs or web specifically about this.  Does 
anyone have any ideas?

Adding hackers as this may be an internal issue.

-- 
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.

Sat, 7 Dec 2002 08:17:48 -0500
"D'Arcy J.M. Cain" <darcy@druid.net>
Re: [SQL] SELECT FOR UPDATE locks whole table