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

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]