The following bug has been logged online:
Bug reference: 5157
Logged by: Jeff Janes
Email address: jeff.janes@gmail.com
PostgreSQL version: 8.4.1
Operating system: Linux
Description: Hash index not concurrency safe
Details:
Hash index is not concurrency safe, starting in REL8_4_0 and up to HEAD.
T1:
create table foo (id int, x text);
create index asdlfkjsdf on foo using hash (id);
insert into foo select 1, 'xxxxxxxxxxx' from generate_series(1,100);
set enable_seqscan =off;
set enable_bitmapscan =off;
\timing on
select count(pg_sleep(.3)) from foo where id=1;
count
-------
100
(1 row)
Time: 30897.835 ms
select count(pg_sleep(.3)) from foo where id=1;
While that is running switch to T2:
insert into foo select generate_series, 'xxxxxxxxxxx' from
generate_series(1,100000);
Back in T1:
count
-------
8
(1 row)
Time: 2474.709 ms
The pg_sleep is simply there to give me time to force the two transactions
to overlap.
The problem is that hashgettuple releases the buffer content share lock when
it returns a tuple, so when it comes back to get another tuple the block may
have been rearranged by concurrent inserts. But the state of the scan
object, specifically so->hashso_curpos, makes no attempt to detect or
correct for this rearragement.