Re: pg_locks: who is locking ? (SOLVED!)

[Alexandre Arruda <alepaes(at)aldeiadigital(dot)com(dot)br>]

Tom Lane wrote:
> Alexandre Arruda <alepaes(at)aldeiadigital(dot)com(dot)br> writes:
> > But pg_stat_activity joined with pg_locks only give me informations 
> > about the lock itself.
> > Realy, I want a (possible) simple information: Who is locking me ?
>
> You need a self-join to pg_locks to find the matching lock that is held
> (not awaited) by some process, then join that to pg_stat_activity to
> find out who that is.

Tom, thanks for explanation !!!
And if someone need, here will go my views (sorry if I made this in the 
long and complicated way)... ;)

1) For transaction locks

create or replace view locks_tr_aux as SELECT a.transaction,a.pid as 
pid_locked,b.pid as pid_locker,c.usename as user_locked FROM pg_locks a, 
pg_locks b, pg_stat_activity c where b.granted=true and a.granted=false 
and a.transaction=b.transaction and a.pid=c.procpid;

create or replace view locks_tr as select a.*,c.usename as user_locker 
from locks_tr_aux a,pg_stat_activity c where a.pid_locker=c.procpid;


2) For tables locks

create or replace view locks_tb_aux as SELECT a.relation::regclass as 
table,a.transaction,a.pid as pid_locked,b.pid as pid_locker,c.usename as 
user_locked FROM pg_locks a, pg_locks b, pg_stat_activity c where 
b.granted=true and a.granted=false and a.relation=b.relation and 
a.pid=c.procpid;

create or replace view locks_tb as select a.*,c.usename as user_locker 
from locks_tb_aux a,pg_stat_activity c where a.pid_locker=c.procpid;


3) For transactionid locks

create or replace view locks_trid_aux as SELECT a.transaction,a.pid as 
pid_locked,b.pid as pid_locker,c.usename as user_locked FROM pg_locks a, 
pg_locks b, pg_stat_activity c where b.granted=true and a.granted=false 
and a.transactionid=b.transactionid and a.pid=c.procpid and 
a.locktype='transactionid';

create or replace view locks_trid as select a.*,c.usename as user_locker 
from trava_trid_aux a,pg_stat_activity c where a.pid_lockedr=c.procpid;


select * from locks_tr;
select * from locks_tb;
select * from locks_trid;


Best Regads,


Alexandre
Aldeia Digital