Attempting to disable count triggers on cleanup

Поиск
Список
Период
Сортировка
Искать
От
Dave Cramer
Тема
Attempting to disable count triggers on cleanup
Дата
Msg-id
46F8EC3A.4040607@fastcrypt.com
Список
Дерево обсуждения
Attempting to disable count triggers on cleanup Dave Cramer <pg@fastcrypt.com>
Re: Attempting to disable count triggers on cleanup hubert depesz lubaczewski <depesz@depesz.com>
Re: Attempting to disable count triggers on cleanup Dave Cramer <pg@fastcrypt.com>
I have a database that we want to keep track of counts of rows.

We have triggers on the rows which increment, and decrement a count 
table. In order to speed up deleting many rows we have added the following

   if user != 'mocospace_cleanup' 
then                                                                          

        update user_profile_count set buddycount=buddycount-1 where 
user_profile_count.uid=OLD.userid;      
    end 
if;                                                                                                      


However in the logs we can see the following. I have checked to make 
sure that the user really is the mocospace_cleanup user and checked 
manually by logging in as the mocospace_cleanup user to make sure that 
the code above does what it purports to.

ERROR:  deadlock detected
DETAIL:  Process 23063 waits for ExclusiveLock on tuple (20502,48) of 
relation 48999028 of database 14510214; blocked by process 23110.
Process 23110 waits for ShareLock on transaction 1427023217; blocked by 
process 23098.
...
CONTEXT:  SQL statement "update user_profile_count set 
buddycount=buddycount-1 where user_profile_count.uid= $1 "
PL/pgSQL function "user_buddy_count" line 11 at SQL statement
SQL statement "DELETE FROM ONLY "public"."user_buddies" WHERE 
"buddyuserid" = $1"

Dave
В списке pgsql-performance по дате отправления
От: Steinar H. Gunderson
Дата:
От: hubert depesz lubaczewski
Дата:
FAQ