Hello,
I´ve 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.
Well, increasing the buffers to -B256 results to:
pqReadData() -- backend closed the channel unexpectedly.
The log says:
FATAL: s_lock(4015e1c5) at bufmgr.c:1949, stuck spinlock. Aborting.
FATAL: s_lock(4015e1c5) at bufmgr.c:1949, stuck spinlock. Aborting.
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 12803 exited with status 6
The exact query looks like:
CREATE TABLE "egal1" (
"lfnr" character varying,
"artnr" character varying);
CREATE TABLE "egal2" (
"lfnr" character varying,
"name1" character varying(21),
"eknr" int2);
COPY "egal1" FROM stdin;
-- a lot of data (130000 records)
COPY "egal2" FROM stdin;
-- a lot of data (12000 records)
SELECT b.lfnr, b.eknr, b.name1 FROM egal1 a, egal2 b WHERE a.lfnr = b.lfnr;
An EXPLAIN of this query
Hash Join (cost=11167.46 rows=138130 width=38)
-> Seq Scan on egal1 a (cost=5644.26 rows=138129 width=12)
-> Hash (cost=507.47 rows=11984 width=26)
-> Seq Scan on egal2 b (cost=507.47 rows=11984 width=26)
EXPLAIN
I think to remember some similar error on an older version, right?
Kind regards,
Michael Contzen
Dohle Systemberatung GmbH
Germany