Re: out of memory for query result

On Wed, 2006-05-03 at 13:16 -0400, Douglas McNaught wrote:
> Sven Willenberger <sven(at)dmv(dot)com> writes:
>
> > 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:
>
> The server settings don't have anything to do with this problem. The
> client is trying to read the entire result set into memory before it
> writes it out (that's just the way it works). You can do it in
> smaller chunks by using a cursor with DECLARE and FETCH.
>
> -Doug

OK, that I do see; I guess I never noticed it on the other (i386)
machine as the memory never exceeded the max amount allowed by the
tunables. That raises a question though:

Using identical data and identical queries, why would the amd64 system
using postgresql 8.1.3 be using some 2/3s more memory to store the query
results before output than the i386 system using postgresql 8.0.4?

amd64 system:
PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU CMD
8193 svenw 1 5 0 1516M 1475M ttyin 1 0:15 0.00% psql

i386 system:
PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU CMD
61263 svenw 1 5 0 972M 956M ttyin 2 0:27 0.00% psql

These are the final stages after the file has flushed (this was run on
the prior month's data which had fewer results). This would either point
to an issue with a) amd64 memory allocation or b) palloc interacting
with 64bit memory or c) some other change.

For now I will try the cursor technique; I assume that if this is run
from within a function returning setof xxxx that I will run into the
same issue?

Sven