Re: Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1

>
> 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: