Re: [HACKERS] an older problem? hash table out of memory

Michael Contzen <mcontzen@dohle.com> writes:
> I=B4ve just downloaded the snapshot-version april, 28th. Doing a join
> between two tables, I got the message:
> ERROR:  hash table out of memory. Use -B parameter to increase buffers.

I saw this too over the weekend, but didn't have time to look into it.

After a quick eyeballing of nodeHash.c, I have a question for anyone
who's worked on the hashjoin code before: why is the sizing of the
hash table driven off -B in the first place?  It looks like the table
was once allocated in shared buffer memory, but it ain't anymore; it's
just palloc'd.  Offhand it seems like the -S (sort space) parameter
might be a better thing to use as the hashtable size control.

That specific error message comes out if the hashtable "overflow area"
fills up because too many tuples landed in the same hashbucket.  So you
can provoke it easily with a test case where a table contains a few
thousand identical rows (which is in fact what my test data looked like;
dunno about Michael's).  In real life it'd have a small but definitely
not zero probability of happening.  I'm surprised that we have not seen
this complaint more before.  It's possible that the recent work on the
optimizer has made it more likely to choose hashjoin than it used to be.
Anyway, I think we'd better invest the work to make the overflow area
expansible.

> Well, increasing the buffers to -B256 results to:
> pqReadData() -- backend closed the channel unexpectedly.

Hmm, I didn't try that.  There must be some other problem as well.
Will look into it.
        regards, tom lane