From: Tom Lane <tgl@sss.pgh.pa.us>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Balazs Nagy <bnagy@thenewpush.com>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works
Date: Thu, 16 Nov 2006 17:48:16 -0500
Message-ID: <1884.1163717296@sss.pgh.pa.us> (raw)
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>
"Simon Riggs" <simon@2ndquadrant.com> 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:
inline patch 1: ./results/create_index.out Thu Nov 16 17:33:29 2006 (+1/-0 lines) download inline-1.patch*** ./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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, simon@2ndquadrant.com, bnagy@thenewpush.com, pgsql-bugs@postgresql.org
Subject: Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works
In-Reply-To: <1884.1163717296@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox