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
