Re: out of memory for query result

On Sat, 2006-04-22 at 15:08 -0400, Tom Lane wrote:
> Francisco Reyes <lists(at)stringsutils(dot)com> writes:
> > What resource do I need to increase to avoid the error above?
>
> Process memory allowed to the client; this is not a server-side error.
>

I am experiencing an "out of memory" situation as well on large query
results, even with allowing 2G process memory to the client:

PostgreSQL 8.1.3, FreeBSD 6.1RC amd64, 8GB RAM.

Relevent configs:
# cat /boot/loader.conf
kern.maxdsiz="2147483648"
kern.dfldsiz="1073741824"

from the kernel config file:
options SYSVSHM # SYSV-style shared memory
options SYSVMSG # SYSV-style message queues
options SYSVSEM # SYSV-style semaphores
options SHMMAXPGS=131072
options SEMMNI=128
options SEMMNS=512
options SEMUME=100
options SEMMNU=256

work_mem = 64MB
maint_work_mem = 512MB

The query result contains about 7.5million rows and I am simply trying
to \o[utput] it to a file:

SELECT callstartdate, callenddate, callduration, calling_number,
called_number, dest_type, sessionrate, sessioncost,
quote_ident(callcenter) as callcenter from cdrs_local where callenddate
between '2006-04-01' and '2006-04-30 23:59:59' order by callstartdate;

When viewing the process in top, I see postgres and the psql client
using relatively little memory (I guess this is the disk read part).
Then I see the psql process eat up memory till it hits the 2G mark
(imposed by the loader.conf tuner) and then "out of memory".

Removing the order by clause doesn't help, nor does reducing work_mem to
8MB. I also tried disabling the bitmap scan and sequence scan to no
avail. I don't know if this is related to the pg_restore memory issues
discussed in another thread or not.

This same query running on FreeBSD i386 (P4 xeon) using PostgreSQL 8.0.x
did not experience this problem.

Any ideas? How can I view the memory allocation and heap management in
the logfiles? (what do I need to set in postgresql.conf).

Sven