On Sat, 2006-04-22 at 15:08 -0400, Tom Lane wrote: > Francisco Reyes <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
