Re: Crash in PostgreSQL-8.2.4 while executing query

"rupesh bajaj" <rupesh(dot)bajaj(at)gmail(dot)com> writes:
> When I run the following query Postmaster crashes.
> ...
> LOG: background writer process (PID 5808) was terminated by signal 9
> LOG: terminating any other active server processes
> LOG: statistics collector process (PID 5809) was terminated by signal 9

Signal 9 is not Postgres' fault. That means the kernel OOM killer has
decided to kill these processes as a result of a system-wide
out-of-memory condition. (Given that the bgwriter never gets very
large, these seem to be particularly misdirected OOM kills, but the
entire "feature" is pretty broken anyway.)

The planner seems to think that the query will return 4.8 billion rows;
does that have anything to do with reality? If so, what's probably
happening is that the client side is running out of memory to buffer the
result, and the kernel is killing some other process instead of the one
that actually ate all the memory. (Not an unusual behavior at all for
the OOM killer.)

Suggestions:
1. Turn off memory overcommit in your kernel settings.
2. Reconsider whether this query is what you actually wanted.
3. If it is, use a cursor to fetch the result in segments.

regards, tom lane