Re: Table locking problems?

On Aug 9, 2005, at 1:08 PM, Tom Lane wrote:

> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
>> My experience is that when this type of thing happens it is typically
>> specific queries that cause the problem. If you turn on statement
>> logging you can get the exact queries and debug from there.
>>
>
>
>> Here are some things to look for:
>>
>
>
>> Is it a large table (and thus large indexes) that it is updating?
>> Is the query using indexes?
>> Is the query modifying ALOT of rows?
>>
>
> Another thing to look at is foreign keys.  Dan could be running into
> problems with an update on one side of an FK being blocked by locks
> on the associated rows on the other side.
>
>             regards, tom lane
>

Tom, Steve, Josh:

Thank you for your ideas.  The updates are only on a single table, no
joins.  I had stats collection turned off.  I have turned that on
again so that I can try and catch one while the problem is
occurring.  The last table it did this on was about 3 million
records.  4 single-column indexes on it.

The problem I had with statement logging is that if the query never
finishes, it doesn't get logged as far as I can tell.  So everything
that did get logged was normal and would run with no isses in psql by
copy and pasting it.  The rows updated will certainly vary by query.
I really need to "catch it in the act" with stats collection on so I
can get the query from pg_stat_activity.  Once I get it, I will play
with explains and see if I can reproduce it outside the wild.

Thanks again for your help.

-Dan