On Tue, Dec 15, 2009 at 12:09 AM, Craig Ringer <cr...@postnewspapers.com.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs