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 on=
ly 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 OF=
F.
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 para=
llel.
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 show=
s 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@postnewspapers.com.au]
Sent: Mon 7/27/2009 11:13 PM
To: Dan Boeriu; PostgreSQL bugs
Subject: RE: [BUGS] BUG #4945: Parallel update(s) gone wild
=20
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