Skip site navigation (1) Skip section navigation (2)

Re: pg_autovacuum and REINDEX at the same time (?)

From: Janar Kartau <janar(dot)kartau(at)cvkeskus(dot)ee>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_autovacuum and REINDEX at the same time (?)
Date: 2007-02-28 11:38:50
Message-ID: 45E569CA.6050805@cvkeskus.ee (view raw or flat)
Thread:
Lists: pgsql-bugs
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?
>


In response to

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2007-02-28 12:12:18
Subject: Re: pg_autovacuum and REINDEX at the same time (?)
Previous:From: Heikki LinnakangasDate: 2007-02-28 11:15:36
Subject: Re: pg_autovacuum and REINDEX at the same time (?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group