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
