Re: psql crashing - don't know why

On Mon, Aug 10, 2009 at 11:04:24AM +1200, Brent Wood wrote:
> Hi...
>
> I have a view across 3 tables, total some 5m rows.
>
> I can extract parts of the view, entire rows, with a where clause
> (eg: select * from view where cell_id=100000;)
>
> If I try to select the entire view (eg: select * from view;) it runs for a while then gives the error msg "Killed" and returns to the system prompt, having exited psql.
>
> The log says:
> 2009-08-10 00:19:01 NZST ben200601 woodb LOG: could not send data to client: Broken pipe
> 2009-08-10 00:19:48 NZST ben200601 woodb LOG: unexpected EOF on client connection

Your problem is that you're trying to load the entire table into memory
in psql, that is, the client side. I don't know how big your dataset
is, but perhaps you should compare that with the amount of memory you
actually have.

Do you actually need to read all the data at once or can you accept it
in chunks? In that you can simply declare a a cursor and then do FETCH
10000 to successively read the rows as needed.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.