Basic Querying of pg_locks in PostgreSQL

This is copied from another article I wrote, but I wanted to pull it out into its own spot.

Here’s how I linked pg_locks to pg_class and pg_namespace to see the human-readable name of the table I was looking for:

SELECT pgd.datname as database,
pgn.nspname AS schema,
pgc.relname AS table,
pgl.*
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_namespace pgn ON pgc.relnamespace = pgn.oid
JOIN pg_database pgd ON pgl.database = pgd.oid;

The primary database I work with uses a lot of schemas, so if that’s not relevant to you, omit the join to pg_namespace. You can also add a WHERE clause at the end to seek out a specific table/schema using pg_namespace.nspname or pg_class.relname, e.g.

SELECT pgn.nspname AS schema,
pgc.relname AS table,
pgl.*
FROM pg_locks pgl
JOIN pg_class pgc
ON pgl.relation = pgc.oid
JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
WHERE pgc.relname = 'my_table';

A note on the oid field in Postgres’ system catalogs

pg_locks and other system catalogs reference each other using a field called “oid”. The oid field is hidden and must be explicitly called out in a function to use it! This is in the documentation.