Running the UPDATE on another connection is an interesting suggestion.
I hope I will remember it next time I run into a similar problem (or
remember reviewing my mail archive :-) ).
In the mean time, I found the source of the problem: the client
application made SQL calls in invalid sequences. (I knew all along
that something similar was at work here, but I wanted to give
PostgreSQL's diagnostic facilities a chance to help me locate the
problem -- and learn about those facilities along the way. A bit off
topic: I ran the same test case against Oracle as well and Oracle
appeared to handle more robustly the client application error: it gave
a more informative error message ("protocol violation"); and, also,
the error message was emitted much closer to the place in the
execution path where the actual programming error occurred.)
Thank you so much for all your suggestions.
Peter
2009/4/10 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
>> It appears nobody is waiting for a lock (all current locks are
>> granted):
>
> Then I would run the UPDATE on another connection with EXPLAIN in
> front of it. That would show you the plan it will use, which might
> give a clue why it is running so long.
>
> Also, a connection which is "idle in transaction" will prevent normal
> maintenance and may lead to table bloat which can kill performance.
> It could be that your problem is there. You might want to check how
> big your database is (using du or something similar), and make sure
> that the size seems reasonable.
>
> I hope this helps.
>
> -Kevin
>