I have just isolated a bug in doing a 'select for update'.  I have 
included a test case below that reproduces the problem on a 7.2 
database.  The bug is as follows: after issuing a select for update that 
blocks on a record being locked by a second process, when that second 
process commits, the first process begins using 100% of the cpu and 
never completes.

The test case is as follows:

drop table test1;
drop table test2;

create table test1 (cola int);
create unique index test1_u1 on test1(cola);
create table test2 (cola int);
create unique index test2_u1 on test2(cola);
insert into test1 values (1);
insert into test2 values (1);


Then in one psql session issue the following:

begin;
UPDATE TEST1 SET COLA = 1
WHERE COLA = 1;


Then in a second psql issue the following:

begin;
SELECT T1.COLA
FROM TEST1 T1, TEST2 T2
WHERE T1.COLA = T2.COLA
AND T2.COLA = 1 FOR UPDATE;

Now go back to the first psql and issue the following:

commit;

Now you will see the cpu usage spike up to 100% for the second process 
and it will never return.

Running analyze on these newly created tables will cause the indexes not 
to be used and will result in the select working correctly.  However in 
my real code the indexes are necessary and thus that isn't a workaround 
for me.

thanks,
--Barry


Sun, 10 Feb 2002 22:39:29 -0800
Barry Lind <barry@xythos.com>
bug with select for update
Barry Lind <barry@xythos.com> writes:
> I have just isolated a bug in doing a 'select for update'.

Nice example.  I believe I have a fix...

			regards, tom lane

Mon, 11 Feb 2002 14:04:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug with select for update 