Re: statement_timeout is not cancelling query

On Tue, Dec 15, 2009 at 12:09 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> On 15/12/2009 12:35 PM, Mark Williamson wrote:
>
>> So what happened is, the above update never completed and the Postgresql
>> service consumed all available memory.  We had to forcefully reboot the
>> machine
>
> That means your server is misconfigured. PostgreSQL should never consume all
> available memory. If it does, you have work_mem and/or maintenance_work_mem
> set way too high, and you have VM overcommit enabled in the kernel. You also
> have too much swap.
>
> http://www.postgresql.org/docs/current/interactive/kernel-resources.html
>
> http://www.network-theory.co.uk/docs/postgresql/vol3/LinuxMemoryOvercommit.html
>
> I wouldn't be surprised if you had shared_buffers set too high as well, and
> you have no ulimit set on postgresql's memory usage. All those things add up
> to "fatal".
>
> A properly configured machine should be able to survive memory exhaustion
> caused by a user process fine. Disable VM overcommit, set a ulimit on
> postgresql so it can't consume all memory, use a sane amount of swap, and
> set sane values for work_mem and maintenance_work_mem.

I am skeptical that this is the real cause of the problem. Yeah, OK,
ulimit might have stopped it, but I don't think any of the rest of
this would have mattered. Unfortunately, we don't know how large the
table was that the OP attempted to update, or the details of how the
trigger was set up, but my guess it that it was the pending-trigger
list that sucked up all the available memory on the box. This is an
issue that other people have run into in the past, and I don't think
we have a good solution. I wonder if we should put some kind of a
limit in place so that queries like this will at least fail relatively
gracefully with an error message rather than taking down the box.

There is a feature forthcoming in 8.5 which will make it easier to
avoid these types of problems, by allowing you to test a condition
before the trigger gets added to the pending trigger list. So if your
trigger is designed to update some side table whenever a certain
column is updated, you can really skip the trigger altogether without
incurring any memory overhead. But there will still be people who do
it the old way, and it would be nice if we could at least mitigate the
impact a little.

...Robert