How to resolve pg_locks.objid to a meaningful object name
1
vote
1
answer
62
views
I'm trying to work out what a particular transaction is locking that prevents other queries from executing. I queried pg_locks like this:
select c.relname, l.*
from pg_locks l
join pg_database d on l.database = d.oid
left join pg_class c on l.relation = c.oid
where d.datname = 'MYDB' and pid = MYPID
That returned a bunch of results like this
| relname | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart |
| ------- | -------- | -------- | -------- | ---- | ----- | ---------- | ------------- | ------- | -------- | -------- | ------------------ | ------ | ------------------- | ------- | -------- | ---------- |
| NULL | object | 14035700 | NULL | NULL | NULL | NULL | NULL | 2606 | 14051522 | 0 | 4/431226 | 793556 | AccessExclusiveLock | true | false | NULL |
relation
was NULL for most (not all) results. For these, classid
and objid
were non-null (as in the example above). How do I resolve those to useful object identifiers? According to the [pg_locks docs](https://www.postgresql.org/docs/current/view-pg-locks.html) locks are "identified by class OID and object OID, in the same way as in pg_description". When I query [pg_description](https://www.postgresql.org/docs/current/catalog-pg-description.html) , however, I cannot find those oids. The values of the objoid column there are all under 20000 - there is nothing like 805681436.
**Edit:** updated query based on Laurenz Albe's answer (thanks!)
select l.granted, l.locktype, l.mode, l.classid, l.objid, l.classid::regclass
, c.relname, cns.conname -- relation
, cns.conrelid::regclass as constraint_table, cns.contype -- constraint
, trg.tgrelid::regclass as trigger_table, trg.tgname -- trigger
from pg_locks l
join pg_database d on l.database = d.oid
left join pg_class c on l.relation = c.oid
left join pg_constraint cns on l.objid = cns.oid and l.classid::regclass = 'pg_constraint'::regclass
left join pg_trigger trg on l.objid = trg.oid and l.classid::regclass = 'pg_trigger'::regclass
where d.datname = 'MYDB' and pid = MYPID
Asked by EM0
(250 rep)
Mar 10, 2025, 04:30 PM
Last activity: Mar 17, 2025, 05:25 PM
Last activity: Mar 17, 2025, 05:25 PM