Обсуждение: Attempting to disable count triggers on cleanup
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
On Tue, Sep 25, 2007 at 07:08:42AM -0400, Dave Cramer wrote: > 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" take a look at: http://www.depesz.com/index.php/2007/09/12/objects-in-categories-counters-with-triggers/ and if you want to temporarily disable trigger, simply do appropriate "alter table disable trigger". depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
hubert depesz lubaczewski wrote: > On Tue, Sep 25, 2007 at 07:08:42AM -0400, Dave Cramer wrote: > >> 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" >> > > take a look at: > http://www.depesz.com/index.php/2007/09/12/objects-in-categories-counters-with-triggers/ > > and if you want to temporarily disable trigger, simply do appropriate > "alter table disable trigger". > > Well, that doesn't work inside a transaction I've tried it. This has been fixed in 8.3 Dave > depesz > >