PostgreSQL find locks including the table name

Question

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?

See https://stackoverflow.com/a/23060492/9843399 for a quick query to add a more user-friendly lock view — Caleb Syring, Jul 20 '23 at 17:28

score 17 · Answer 1 · edited Oct 08 '20 at 19:53

17

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());

edited Oct 08 '20 at 19:53

HrkBrkkl

613
5
22

answered Nov 13 '19 at 10:36

Phil Horder

402
6
13

Chris Chudzicki · Answer 2 · 2021-03-10T16:32:22.377

12

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

select relation::regclass, * from pg_locks;

edited Mar 10 '21 at 16:32

answered Mar 10 '21 at 16:16

Chris Chudzicki

630
13
16

Rémy Baron · Accepted Answer · 2016-05-09T08:04:30.847

8

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

edited May 09 '16 at 08:04

answered May 09 '16 at 07:48

Rémy Baron

1,379
8
15

score 4 · Answer 4 · answered May 09 '16 at 08:03

4

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
;

answered May 09 '16 at 08:03

TheLovelySausage

3,838
15
56
106

3

`pg_class.relnamespace >= 2200` it seems here that `2200` is the "magic" number I was looking for to exclude non-interesting postgres internal locks from the list and return only locks on user-defined tables. I wonder if there's some way to introspect this number from postgres itself? – Anentropic Sep 12 '18 at 20:53

score 4 · Answer 5 · edited May 10 '18 at 10:44

4

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;

edited May 10 '18 at 10:44

anothernode

5,100
13
43
62

answered May 10 '18 at 10:25

chiru

789
6
5

PostgreSQL find locks including the table name

5 Answers5