Yes, you can join pg_locks to pg_stat_activity and look for waiting
queries, and what they're waiting on in locks.
On Mon, Sep 14, 2009 at 11:20 AM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:
>
>
> Thanks Scott,
>
> How were you able to determine the resource that was causing it. There must
> be a way of comparing the information to a table?
>
> Chris
>
>> Date: Mon, 14 Sep 2009 09:53:08 -0600
>> Subject: Re: [GENERAL] Locks in postgres causing system load and crash.
>> From: scott.marlowe@gmail.com
>> To: compuguruchrisbarnes@hotmail.com
>> CC: pgsql-general@postgresql.org
>>
>> On Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes
>> <compuguruchrisbarnes@hotmail.com> wrote:
>> > We have a situation where the database locks escalate and load causes
>> > problems or the system crashes in some circumstances.
>> >
>> > We have munin installed and notice that the locks (access share locks)
>> > climbed to 2.7k.
>> >
>> > I'm wondering what or how I can get a snapshot of the table(s) and
>> > perhaps
>> > the culprit that is causing this either from postgres internally or some
>> > other means?
>>
>> The access share locks are likely a symptom, not the cause. Look for
>> what they're waiting on for the lock. It's usually an exclusive lock
>> of some kind that causes this problem. We had an issue with a wayward
>> update with no where clause causing an issue like this a year ago.
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> ________________________________
> New! Open Hotmail faster on the new MSN homepage!
--
When fascism comes to America, it will be intolerance sold as diversity.