Re: psql runs out of memory

Vittorio wrote:
> In my Pentium 4 box 2GHz with 256 MB of RAM I run a pg server 8.0.3
> under netbsd 2.0.2 in which among other tables there's a "huge" table
> letture02 made of 657,000 records and 98 numerical columns.
>
> Using psql
> and the "heavy" view letture24btnondom (see below) I face the following
> fatal error:
> ..........
> # select * from letture24btnondom ;
> out of
> memory for query result

How many rows does this return?
How much memory does psql take up when it reports this?
Do you have any memory limits defined for the user doing the query?
Can you get it to work with something like LIMIT 10 added to the query?
What about LIMIT 50,100,200 etc?

> Now, it happens that I pg_dumped
> the database from an original ** freebsd ** installation on another
> older pc (pentium 3 128 MB) where ** under psql I set up the same query
> and ran it successfully many times.
> Besides, via ODBC I can open as a
> table the letture24btnondom view without any problems.

I'd guess ODBC is using a cursor, so you'll only get a few rows at a
time. With psql, it'll fetch all the rows at once.

--
Richard Huxton
Archonet Ltd