pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAttempting to disable count triggers on cleanup
3+ messages / 2 participants
[nested] [flat]
* Attempting to disable count triggers on cleanup
@ 2007-09-25 11:08 Dave Cramer <pg@fastcrypt.com>
2007-09-25 11:21 ` Re: Attempting to disable count triggers on cleanup hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dave Cramer @ 2007-09-25 11:08 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Attempting to disable count triggers on cleanup
2007-09-25 11:08 Attempting to disable count triggers on cleanup Dave Cramer <pg@fastcrypt.com>
@ 2007-09-25 11:21 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-09-25 11:39 ` Re: Attempting to disable count triggers on cleanup Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2007-09-25 11:21 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Attempting to disable count triggers on cleanup
2007-09-25 11:08 Attempting to disable count triggers on cleanup Dave Cramer <pg@fastcrypt.com>
2007-09-25 11:21 ` Re: Attempting to disable count triggers on cleanup hubert depesz lubaczewski <depesz@depesz.com>
@ 2007-09-25 11:39 ` Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dave Cramer @ 2007-09-25 11:39 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-09-25 11:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-25 11:08 Attempting to disable count triggers on cleanup Dave Cramer <pg@fastcrypt.com>
2007-09-25 11:21 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-09-25 11:39 ` Dave Cramer <pg@fastcrypt.com>
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