Thread: psql crashing - don't know why

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
returnsto 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


I have written a script along the lines of:

get distinct cell_id from table; > file

while read cell ; do
   psql -d db -c "select * from view where cell=$cell;" >> output
done < file

This worked, & the number of lines in the output file is the same number as that returned by "select count(*) from
view;"(which works fine), but I'd like to find out the cause of the error. I assume there is some sort of timeout or
overflowoccurring, but I can't see any indication of what settings I can change to fix this. All the underlying tables
havejust had vacuum analyse run on them. 

I'm running PostgreSQL 8.1.4 on x86_64 Linux, I know it is dated, but I'm not in a position to upgrade at this point.


Thanks,

  Brent Wood



Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

On Sun, Aug 9, 2009 at 5:04 PM, Brent Wood<b.wood@niwa.co.nz> 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"
andreturns 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
>

Most likely you're getting bitten by the OOM killer in linux.

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"
andreturns 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@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.