From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | David Wheeler <david(at)wheeler(dot)net> |
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-15 22:39:12 |
Message-ID: | 200307151539.12239.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2003-07-16 01:46:20 | Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors |
Previous Message | David Wheeler | 2003-07-15 21:39:34 | Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors |