Re: Preventing or controlling runaway queries

Eric E <whalesuit(at)gmail(dot)com> writes:
> Here's the test function:
> ...
> my $data_handle = spi_exec_query('SELECT * FROM
> schema1."table_of_approximately 30000 rows";');

Well, the plperl documentation does point out that spi_exec_query should
only be used when you know that the result set will be relatively
small. I think the alternative (spi_query/spi_fetchrow) is new in 8.1,
but you might be able to use a cursor with FETCH instead in older
versions.

> Dec 5 14:54:33 dbtestserver kernel: Out of Memory: Killed process 3294
> (postmaster).

This, however, is just plain administrator error. You should have the
OOM killer disabled completely (memory overcommit off) on any server
machine, or indeed any machine at all that runs processes you would not
like to see killed at random. I believe PG could recover from this,
if the kernel gave it a polite "out of memory" error and not a kill -9.
But the truly nasty part of the OOM killer is that it may kill a totally
innocent process.

Linux's memory-overcommit behavior was invented by someone accustomed to
Windows standards of reliability. Personally I don't think there is any
good reason to have it enabled ever.

regards, tom lane