Re: BUG #4945: Parallel update(s) gone wild

On Jul 28, 2009, at 1:16 PM, "Dan Boeriu" <dan(dot)boeriu(at)roost(dot)com> wrote:

> I am doing a reply-all - hope that's fine.
>
> The pg_locks shows only locks held by that Postgresql process - I am
> the only one on the box.
> pg_stat_activity shows 2 things:
> 1) the update
> 2) vacuum on the updated table
>
> Now, I tried to isolate the problem even more and turned the auto
> vacuum OFF.
> Then pg_stat_activity shows only the UPDATE statement.
>
> Here is one wierd thing:
> IF I cancel the second UPDATE then restart it then it runs in 20 secs.
>
> Let me repeat the problem: I run 2 SQL UPDATEs (the same statement)
> in parallel.
> The first one to acquire the locks on the updated table goes through
> in 20 secs.
> The second will not finish in 24h but the CPU stays at 100% and
> iostat shows no IO other than the checkpoints.
> IF I cancel the second UPDATE (pg_cancel_backend) and restart it
> (new pgsql invocation) ASAP it goes through in 20 secs.
>
>
> Dan Boeriu
> Senior Architect - Roost.com
> P: (415) 742 8056
> Roost.com - 2008 Inman Award Winner for Most Innovative New Technology
>
>
>
>
> -----Original Message-----
> From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
> Sent: Mon 7/27/2009 11:13 PM
> To: Dan Boeriu; PostgreSQL bugs
> Subject: RE: [BUGS] BUG #4945: Parallel update(s) gone wild
>
> Please reply to the list, not directly to me.
>
> > I don't think is that simple. The VERY SAME statement runs twice -
> one
> > finishes in about 20 secs the other doesn't finish in 24 hours.
>
> Yep, OK, so it's not just a planning or scaling issue.
>
How about posting:

Server version
EXPLAIN ANALYZE output
Schemas of relevant tables

Or far better still:

A self-contained reproducible test case

...Robert