CODEWITHSUNDEEP
relational-databasepostgresql-9.2database-administration
TheLovelySausage
TheLovelySausage

Reputation: 4094

PostgreSQL find locks including the table name

I'm trying to take a look at locks that are happening on specific tables in my PostgreSQL database.

I see there's a table called pg_locks

select * from pg_locks;

Which seems to give me a bunch of columns but is it possible to find the relation because I see one of the columns is the relation oid.

What table must I link that to to get the relation name?

Upvotes: 13

Views: 40984

Answers (5)

Chris Chudzicki
Chris Chudzicki

Reputation: 722

If you just want the contents of pg_locks but with a human-friendly relation name,

select relation::regclass, * from pg_locks;

Upvotes: 15

Phil Horder
Phil Horder

Reputation: 421

This is Remy's query, adjusted for Postgres 10:

select nspname, relname, l.* 
from pg_locks l 
    join pg_class c on (relation = c.oid) 
    join pg_namespace nsp on (c.relnamespace = nsp.oid)
where pid in (select pid 
              from pg_stat_activity
              where datname = current_database() 
                and query != current_query());

Upvotes: 19

chiru
chiru

Reputation: 997

The below command will give the list of locks:

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted 
from pg_locks l, pg_stat_all_tables t where l.relation=t.relid
order by relation asc;

Upvotes: 5

Rémy Baron
Rémy Baron

Reputation: 1399

Try this :

select nspname,relname,l.* from pg_locks l join pg_class c on 
 (relation=c.oid) join pg_namespace nsp on (c.relnamespace=nsp.oid) where 
  pid in (select procpid from pg_stat_activity where 
  datname=current_database() and current_query!=current_query())

Upvotes: 9

TheLovelySausage
TheLovelySausage

Reputation: 4094

Remy Baron's answer is correct I just wanted to post one I came up with as well only because it's more specific to what I need in this case

select pg_class.relname,
       pg_locks.mode
from pg_class,
     pg_locks
where pg_class.oid = pg_locks.relation
and pg_class.relnamespace >= 2200
;

Upvotes: 4

Related Questions