Using pgadmin to check status of Postgres advisory locks

Question

I'm trying to use Postgres advisory locks in my Django app, and it seems like acquiring is frozen.

I want to use the pgadmin GUI to see which locks are acquired and which aren't. I tried using it, and navigated to the pg_locks and looked around there for a while, but I couldn't find where I could see which locks are acquired. How can I see that?

Answer 1

Advisory locks are listed in pg_locks, with locktype = advisory and the objid containing the locked value:

regress=> SELECT pg_advisory_lock(12345);
 pg_advisory_lock 
------------------

(1 row)

regress=> SELECT * FROM pg_locks;
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted | fastpath 
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
 relation   |   144654 |    11090 |      |       |            |               |         |       |          | 2/24979            | 22097 | AccessShareLock | t       | t
 virtualxid |          |          |      |       | 2/24979    |               |         |       |          | 2/24979            | 22097 | ExclusiveLock   | t       | t
 advisory   |   144654 |          |      |       |            |               |       0 | 12345 |        1 | 2/24979            | 22097 | ExclusiveLock   | t       | f
(3 rows)

regress=> SELECT objid, mode, granted FROM pg_locks WHERE locktype = 'advisory';
 objid |     mode      | granted 
-------+---------------+---------
   456 | ExclusiveLock | t
 12345 | ExclusiveLock | t
(2 rows)

For the two-value locks, the first part is in classid and objsubid is 2 (instead of 1 for single-argument locks):

regress=> SELECT pg_advisory_lock(123, 456);
 pg_advisory_lock 
------------------

(1 row)

regress=> SELECT classid, objid, mode, granted, objsubid FROM pg_locks WHERE locktype = 'advisory';
 classid | objid |     mode      | granted | objsubid
---------+-------+---------------+--------------------
     123 |   456 | ExclusiveLock | t       | 2
(1 row)

Update:

The mode field is the lock mode.

regress=> SELECT pg_advisory_lock_shared(1234);
 pg_advisory_lock_shared 
-------------------------

(1 row)

regress=> SELECT classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory';
 classid | objid | objsubid |   mode    | granted 
---------+-------+----------+-----------+---------
       0 |  1234 |        1 | ShareLock | t
(1 row)

If a given lock isn't acquired at all, there'll be no row for it.

regress=> SELECT classid, objid, objsubid, mode, granted 
          FROM pg_locks 
          WHERE locktype = 'advisory' 
            AND objsubid = 1  /* One-argument form lock */
            AND objid = 1235; /* argument = 1235 */

 classid | objid | objsubid | mode | granted 
---------+-------+----------+------+---------
(0 rows)

If a lock is blocked waiting on another session, it'll have granted = 'f'.

You can see which process ID holds / is trying to acquire a lock using the pid field. Joining on pg_stat_activity can be useful, as can a self-join on pg_locks to see which session blocks a given lock.

See the user manual for pg_locks for details.