Re: [PERFORM] Foreign Key Deadlocking

2007-04-19 Thread Csaba Nagy
> A frequently mentioned approach to avoid the point of contention is to > have a "totals" record and have the triggers insert "deltas" records; to > get the sum, add them all. Periodically, take the deltas and apply them > to the totals. This is what we do here too. There is only one exception t

Re: [PERFORM] Foreign Key Deadlocking

2007-04-19 Thread Alvaro Herrera
Dave Cramer escribió: > Hi Csaba, > > I have a similar problem. > > In an attempt to avoid the overhead of select count(*) from mailbox > where uid = somuid I've implemented triggers on insert and delete. > > So there is a > > user table which refers to to an inbox table, > > so when people

Re: [PERFORM] Foreign Key Deadlocking

2007-04-19 Thread Erik Jones
On Apr 19, 2007, at 9:00 AM, Dave Cramer wrote: On 18-Apr-07, at 11:36 AM, Csaba Nagy wrote: Can someone confirm that I've identified the right fix? I'm pretty sure that won't help you... see: http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php The deadlock will be there if

Re: [PERFORM] Foreign Key Deadlocking

2007-04-19 Thread Dave Cramer
Hi Csaba, I have a similar problem. In an attempt to avoid the overhead of select count(*) from mailbox where uid = somuid I've implemented triggers on insert and delete. So there is a user table which refers to to an inbox table, so when people insert into the inbox there is an RI trigger

Re: [PERFORM] Foreign Key Deadlocking

2007-04-18 Thread Steven Flatt
Thanks for your answers and feedback. All things considered, it is easiest (and acceptable) in this case to remove RI between the tables where the deadlocks were occurring. We are still looking to upgrade to 8.1.latest but that is another matter... Steve

Re: [PERFORM] Foreign Key Deadlocking

2007-04-18 Thread Tom Lane
"Steven Flatt" <[EMAIL PROTECTED]> writes: > Hi, we're using Postgres 8.1.4. > We've been seeing deadlock errors of this form, sometimes as often as > several times per hour: > ... > I also see claims that this problem is fixed in 8.2, and if the fix is what > I think it is, it's also in 8.1.6. > C

Re: [PERFORM] Foreign Key Deadlocking

2007-04-18 Thread Csaba Nagy
> Can someone confirm that I've identified the right fix? I'm pretty sure that won't help you... see: http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php The deadlock will be there if you update/insert the child table and update/insert the parent table in the same transaction (even

[PERFORM] Foreign Key Deadlocking

2007-04-18 Thread Steven Flatt
Hi, we're using Postgres 8.1.4. We've been seeing deadlock errors of this form, sometimes as often as several times per hour: Apr 17 13:39:50 postgres[53643]: [4-1] ERROR: deadlock detected Apr 17 13:39:50 postgres[53643]: [4-2] DETAIL: Process 53643 waits for ShareLock on transaction 11128328