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

> >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!

Anecdotally and not having read the code, are you using cursors in
your code? While it may not make things blazingly fast, it is
friendly on the server in terms of RAM, which often times does speed
applications up if a machine is memory constrained.

--
Sean Chittenden