From: Dave Cramer <pg@fastcrypt.com>
To: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Attempting to disable count triggers on cleanup
Date: Tue, 25 Sep 2007 07:08:42 -0400
Message-ID: <46F8EC3A.4040607@fastcrypt.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pg@fastcrypt.com
Subject: Re: Attempting to disable count triggers on cleanup
In-Reply-To: <46F8EC3A.4040607@fastcrypt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox