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

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

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: David Wheeler <david(at)wheeler(dot)net>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors
Date: 2003-07-16 01:52:17
Message-ID: 20030716015217.GE24507@perrin.int.nxad.com (view raw or flat)
Thread:
Lists: sfpug
> >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

In response to

Responses

sfpug by date

Next:From: David WheelerDate: 2003-07-16 01:56:08
Subject: Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors
Previous:From: David WheelerDate: 2003-07-16 01:46:20
Subject: Re: Fwd: [Bricolage-Devel] DB Deadlock Detected errors

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