Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 4FBA89F946B for ; Thu, 16 Nov 2006 18:48:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 74390-02 for ; Thu, 16 Nov 2006 18:48:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 235E09F9998 for ; Thu, 16 Nov 2006 18:48:19 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kAGMmGa9001885; Thu, 16 Nov 2006 17:48:16 -0500 (EST) To: "Simon Riggs" cc: "Balazs Nagy" , pgsql-bugs@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works In-reply-to: <1163233075.3634.944.camel@silverbirch.site> References: <200611051347.kA5DlVCk032222@wwwmaster.postgresql.org> <7530.1163202951@sss.pgh.pa.us> <1163233075.3634.944.camel@silverbirch.site> Comments: In-reply-to "Simon Riggs" message dated "Sat, 11 Nov 2006 08:17:54 +0000" Date: Thu, 16 Nov 2006 17:48:16 -0500 Message-ID: <1884.1163717296@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/117 X-Sequence-Number: 16252 "Simon Riggs" writes: > On Fri, 2006-11-10 at 18:55 -0500, Tom Lane wrote: >> One thought that comes to mind is to require hash to do an smgrextend() >> addressing the last block it intends to use whenever it allocates a new >> batch of blocks, whereupon md.c could adopt a saner API: allow >> smgrextend but not other calls to address blocks beyond the current EOF. > Yes, do it. I found out that it's easy to reproduce this failure in the regression tests, just by building with RELSEG_SIZE set to 128K instead of 1G: *** ./expected/create_index.out Sun Sep 10 13:44:25 2006 --- ./results/create_index.out Thu Nov 16 17:33:29 2006 *************** *** 323,328 **** --- 323,329 ---- -- CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); + ERROR: could not open segment 7 of relation 1663/16384/26989 (target block 145): No such file or directory CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops); -- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); AFAICS, any hash index exceeding a single segment is at serious risk. The fact that we've not heard gripes before suggests that no one is using gigabyte-sized hash indexes. But it seems mighty late in the beta cycle to be making subtle changes in the smgr API. What I'm inclined to do for now is to hack _hash_expandtable() to write a page of zeroes at the end of each file segment when an increment in hashm_ovflpoint causes the logical EOF to cross segment boundary(s). This is pretty ugly and nonmodular, but it will fix the bug without risking breakage of any non-hash code. I'll revisit the cleaner solution once 8.3 devel begins. Comments? regards, tom lane