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.
That's it for now.
Hope someone can provide helpful answers.
Thanks,
Mark W.