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

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.

Have you checked pg_locks ?

SELECT * FROM pg_locks;

What does pg_stat_activity indicate about the query?

SELECT * FROM pg_stat_activity;

> The plan might change from one execution to the other - is there a way
> to get the executed plan at runtime?

I think there is in 8.4, but I haven't moved up to it and tested yet.
Not in previous versions.

--
Craig Ringer