The following bug has been logged online:
Bug reference: 2737
Logged by: Balazs Nagy
Email address: bnagy@thenewpush.com
PostgreSQL version: 8.1.5
Operating system: RHEL4
Description: hash indexing large table fails, while btree of same
index works
Details:
Postgres: 8.1.5
Database table size: ~60 million rows
Field to index: varchar 127
CREATE INDEX ... USING hash ...
fails with a file not found error (psql in verbose mode):
ERROR: 58P01: could not open segment 3 of relation 1663/16439/16509 (target
block 528283): No such file or directory
LOCATION: _mdfd_getseg, md.c:954
VACUUM, VACUUM FULL doesn't help, full dump and reload doesn't help either
CREATE INDEX ... USING btree ...
works fine. Could there be a bug in the hash algorithm's implementation?
System is x86_64 SMP 8 CPU core, 16GB RAM, Fiber channel SAN, kernel
2.6.9-42.0.3.ELsmp
I haven't tried the 8.2beta2 yet, but would be happy to try, as the hash
method is better suited for the kind of index I need...