Thread: pg_locks: who is locking ?
Hi, My Database have a lot of locks not granted every moments in a day. Can I create a view that returns someting like this ? User Granted Table Who_is_locking_me PID ---- ------- ----- ----------------- --- joe f foo frank 1212 jeff f foo frank 1313 ann f foo frank 1414 frank t foo 1111 (...) (Or the locked transactions, if the table cold't be retrived) pg_locks view does not give me WHO is locking... Best regards, Alexandre
Alexandre Arruda wrote: > Hi, > > My Database have a lot of locks not granted every moments in a day. > > Can I create a view that returns someting like this ? > > User Granted Table Who_is_locking_me PID > ---- ------- ----- ----------------- --- > joe f foo frank 1212 > jeff f foo frank 1313 > ann f foo frank 1414 > frank t foo 1111 > (...) > > (Or the locked transactions, if the table cold't be retrived) You can look up more data about a backend by joining pg_locks to pg_stat_activity, using the PID (I think it's called procpid on one view and pid on the other). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera escreveu: > Alexandre Arruda wrote: >> Hi, >> >> My Database have a lot of locks not granted every moments in a day. >> >> Can I create a view that returns someting like this ? >> >> User Granted Table Who_is_locking_me PID >> ---- ------- ----- ----------------- --- >> joe f foo frank 1212 >> jeff f foo frank 1313 >> ann f foo frank 1414 >> frank t foo 1111 >> (...) >> >> (Or the locked transactions, if the table cold't be retrived) > > You can look up more data about a backend by joining pg_locks to > pg_stat_activity, using the PID (I think it's called procpid on one view > and pid on the other). > Hi, 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 ? Today, I *presume* this information by manually search the pg_locks: 1) Search for the locked tables 2) Search for all lock GRANTED to this tables 3) Generally, the older PID is the locker Not so smart, I think. :) Best regards, Alexandre
Alexandre Arruda <alepaes@aldeiadigital.com.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.
regards, tom lane
Tom Lane wrote: > Alexandre Arruda <alepaes@aldeiadigital.com.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