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

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(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.

regards, tom lane

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