I have a few things to report so I'm not sure if one email is good or several but here goes.
We are using Postgresql 8.3.8
We were having a blocking query problem that should have been fixed by statement_timeout = 90000 however this seems to have had zero effect.
The query we have was like so:
update articles set views=views+1 where id=7223
Thats it. Fairly simple right? Well, we also had a trigger function that updates a full text index on that record whenever any value is updated. We have since changed this function to only update the gist index for inserts or updates when one of the indexed columns is updated. However, let's stick with the original for a moment. There should have really been no problem updating the GIST index for a single row in an insert/update trigger.
So what happened is, the above update never completed and the Postgresql service consumed all available memory. We had to forcefully reboot the machine, we turned on track activity, and watch it do it again and again. Luckily we were able to kill the process with the offending query before losing the machine.
The postgresql configuration has a max of 255 connections. The machine has 16 gigabytes of RAM and 2 quad core xeons. We have several instances of Postgresql running on different ports. Our reason for doing this was to prevent one customer's database 'instance' from impacting another customer. A couple of years ago we had a run away query that brought the whole system down. So I implemented this separate instance concept and it has been purring along great ever since, until now.
So we contacted a PG expert who was able to determine we had a corrupt full text index and recommended rebuilding it and fixing the trigger function. Once we rebuilt the index things worked (or are working) so far.
So we have a couple of questions:
Why is it that statement_timeout was ignored and the update statement was allowed to run for excessive time? Why does Postgresql NOT have a maximum memory allowed setting? We want to allocate resources efficiently and cannot allow one customer to impact others.
