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

Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors

From: David Wheeler <david(at)wheeler(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>,<sfpug(at)postgresql(dot)org>
Subject: Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors
Date: 2003-07-16 01:46:20
Message-ID: 4C8355E4-B72F-11D7-99A8-0003931A964A@wheeler.net (view raw or flat)
Thread:
Lists: sfpug
On Tuesday, July 15, 2003, at 03:39  PM, Josh Berkus wrote:

> Imagine this:
>
> a, b, and c are each tables with 100,000 records.
>
> you run:
>
> UPDATE b SET col4 = TRUE
> FROM a,c
> WHERE a.id = b.col2
> 	and b.col3 = 'x';
>
> In the above pseudo-query, I've forgotten to join c.col2 = b.col3, and 
> as a
> result I am asking Postgres to update each row of b once for each row 
> of c
> that exists ... in other words, if the where clauses yielded 3000 rows 
> of b,
> I would be running 30,000,000 row-updates.  The statement will take 
> forever,
> and will likely deadlock with any other concurrent transaction that 
> updates
> a, b, or c.

Oh, okay. Bricolage doesn't have any queries that UPDATE FROM. But in 
the past I have found a couple of SELECTs that return every row in a 
table. Not very good for performance.

BTW, I'm told that the patch Stephan mentioned did indeed eliminate the 
problem. Go Jan!

Thanks!

David

-- 
David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394
http://kineticode.com/                         Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


In response to

Responses

sfpug by date

Next:From: Sean ChittendenDate: 2003-07-16 01:52:17
Subject: Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors
Previous:From: Josh BerkusDate: 2003-07-15 22:39:12
Subject: Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors

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