From: | Craig Vosburgh <craig(dot)vosburgh(at)cassatt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1 |
Date: | 2008-05-12 22:19:46 |
Message-ID: | C44E1EA2.C52E%craig.vosburgh@cassatt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> What's the locktype?
>
Yep, locktype is transaction.
> If (as I suspect) it's a transaction or
> virtualtransaction lock, then which process holds that lock and what's
> it doing?
As for which process owns that lock, I'm not sure how to find that out
(sorry newbie). I can find the PID that is waiting for that lock and I can
find the table/row that appears to be waiting for the lock to perform the
action but I can't figure out which process actually owns the lock that is
causing the issue.
> For that matter, what is the stuck process doing?
> pg_stat_activity output should be sufficient here.
>
> regards, tom lane
I've enabled stats tracking and the process that is hung has the following
data in the pg_stat_activity table
datid | datname | procpid | usesysid | usename |
current_query
| waiting | xact_start | query_start |
backend_start | client_addr | client_port
-------+---------+---------+----------+----------+--------------------------
----------------------------------------------------------------------------
-------------------------------------------------+---------+----------------
---------------+-------------------------------+----------------------------
---+-------------+-------------
16384 | collage | 20938 | 10 | postgres | update ips set
address=$1, usage=$2, subnet_rips_id=$3, hostname=$4, errored=$5,
errorReason=$6, modinfo=$7, name=$8, description=$9 where bmo_id=$10 | t
| 2008-05-11 17:25:04.484224-06 | 2008-05-11 17:25:04.528319-06 | 2008-05-11
17:02:00.016083-06 | 127.0.0.1 | 49056
Everything else in the table is either IDLE or IDLE in transaction.
Thanks again,
-Craig
On 5/12/08 1:11 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
From | Date | Subject | |
---|---|---|---|
Next Message | Justin | 2008-05-12 22:37:02 | Re: rounding problems |
Previous Message | A.M. | 2008-05-12 21:59:06 | Re: changing the endianness of a database |