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

David,

> Uh, maybe I'm misunderstanding you, but AFAIK, you can't UPDATE two
> tables at once via a join.
>
> In fact, I'm sure I'm misunderstanding you. Can you explain a bit more,
> Josh?

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.

--
Josh Berkus
Aglio Database Solutions
San Francisco