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

Attempting to disable count triggers on cleanup

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: postgresql performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Attempting to disable count triggers on cleanup
Date: 2007-09-25 11:08:42
Message-ID: 46F8EC3A.4040607@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: hubert depesz lubaczewskiDate: 2007-09-25 11:21:49
Subject: Re: Attempting to disable count triggers on cleanup
Previous:From: Steinar H. GundersonDate: 2007-09-25 09:31:33
Subject: Re: REPOST: Nested loops row estimates always too high

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