I know what causes the "sorry, too many clients already" error, but the
number of connections shouldn't grow so big.. so i guess the table
(category_tree) got locked.
We do REINDEX every 5 minutes because the table gets updated very often
and the query's took a lot of time. I think since we use autovacuum now,
it's not needed anymore.
Here's the fuction itself..
DECLARE
row RECORD;
BEGIN
UPDATE category_tree SET item_count = 0;
FOR row IN SELECT
count(I.item_id) AS itemcount
,CT.node_left
,CT.node_right
FROM
items I
JOIN category_tree CT ON (I.category1=CT.category_id)
WHERE
date_start <= now()
AND date_end >= now()
AND item_status = 1
AND I.view_group IS NULL
GROUP BY
node_left||node_right,
node_left,
node_right
LOOP
UPDATE category_tree SET item_count = item_count + row.itemcount
where node_left <= row.node_left AND node_right >= row.node_right;
END LOOP;
FOR row IN SELECT
count(I.item_id) AS itemcount
,CT.node_left
,CT.node_right
FROM
items I
JOIN category_tree CT ON (I.category2=CT.category_id)
WHERE
date_start <= now()
AND date_end >= now()
AND item_status = 1
AND I.view_group IS NULL
GROUP BY
node_left||node_right,
node_left,
node_right
LOOP
UPDATE category_tree SET item_count = item_count + row.itemcount
where node_left <= row.node_left AND node_right >= row.node_right;
END LOOP;
--REINDEX TABLE category_tree;
RETURN 1;
END;
and it's called from..
BEGIN;
SELECT * FROM update_itemcount();
UPDATE cache.cached_stats SET intval=(SELECT count(*) FROM items WHERE
item_status = 1 AND view_group IS NULL) WHERE stat_id = 1;
COMMIT;
Heikki Linnakangas wrote:
Janar Kartau wrote:
Hi.
I've been running autovacuum over a month now without any problems,
but today one of the critical tables got locked and made a pretty big
mess. :)
We have a cron script that does REINDEX on this table every 5
minutes. So i wonder if running REINDEX and VACUUM on the same table
at the same time may cause this deadlock?
Or can a VACUUM make so much trouble?
ISTM that you have two separate issues.
The "sorry, too many clients already" error means that you've reached
the maximum number of connections, as set with the max_connections
setting. The autovacuum processes needs one connection to run.
I suspect that the deadlock is not related to the autovacuum, but just
an interaction between your transactions and the REINDEX.
What does the update_itemcount() function look like? Are you running
the REINDEX in a transaction?
Why do you need to reindex every 5 minutes? How long does the vacuum
run normally?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
