Re: Analizar un proceso aun sin terminar

Excerpts from Daymel Bonne Solís's message of vie sep 09 01:06:00 -0300 2011:

> SELECT
> w.current_query as waiting_query,
> w.procpid as w_pid,
> w.usename as w_user,
> l.current_query as locking_query,
> l.procpid as l_pid,
> l.usename as l_user,
> t.schemaname || '.' || t.relname as tablename
> from pg_stat_activity w
> join pg_locks l1 on w.procpid = l1.pid and not l1.granted
> join pg_locks l2 on l1.relation = l2.relation and l2.granted
> join pg_stat_activity l on l2.pid = l.procpid
> join pg_stat_user_tables t on l1.relation = t.relid
> where w.waiting;

Un pequeño comentario, si vas a hacer un self-join de pg_locks deberías
usar IS NOT DISTINCT FROM en estas columnas:

FROM
pg_catalog.pg_locks l1
JOIN
pg_catalog.pg_locks l2 ON (
(
l1.locktype, l1.database, l1.relation, l1.page,
l1.tuple, l1.virtualxid, l1.transactionid, l1.classid,
l1.objid, l1.objsubid
)
IS NOT DISTINCT FROM
(
l2.locktype, l2.database, l2.relation, l2.page,
l2.tuple, l2.virtualxid, l2.transactionid, l2.classid,
l2.objid, l2.objsubid
)
)
...

--
Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>