Hi, I ran into a problem with one of our schema monitoring queries today, which I ended up concluding to likely be caused by a combination of querying pg_locks on a recurring basis (once every 10 minutes), and a customer workload that used excessive predicate locks (> 100MB of total pg_locks data).
Our motivation for querying pg_locks in the first place is *not* to see all locks, but rather to check whether any relations are exclusively locked, so we can avoid calling pg_get_indexdef or pg_get_expr on catalog entries that relate to the exclusively locked table, and having our schema monitoring query time out because of a single table that's exclusively locked. Whilst digging through the lock manager code for anything faster than pg_locks, I found GetRunningTransactionLocks, currently used for the XLOG_STANDBY_LOCK WAL record, which is exactly the information I was looking for. The main performance benefit being that it skips over the predicate locks information (as well as fast-path locks), and returns no other data except for exclusive locks. The attached patched repurposes that function (and renames it for clarity), and exposes it as a view called "pg_exclusive_locks": postgres=# SELECT * FROM pg_exclusive_locks; -[ RECORD 1 ]-+------ database | 5 relation | 16384 transactionid | 755 pid | 33030 This can then be used in schema information gathering queries like this: WITH locked_relids AS (SELECT relation FROM pg_exclusive_locks) SELECT pg_catalog.pg_get_indexdef(i.indexrelid, 0, FALSE) FROM pg_catalog.pg_class c JOIN pg_catalog.pg_index i ON (c.oid = i.indrelid) AND c.oid NOT IN (SELECT relation FROM locked_relids) Worth noting that particular use of it has a race condition where pg_get_indexdef could still run into a lock taken after the lock manager was queried, but in practice this is infrequent enough that it still helps (based on our experience doing this with pg_locks for a while). In a different use case this could also be used to understand better what happened (i.e. which locks were held) when the XLOG_STANDBY_LOCK record was emitted and queries on standbys ran into unexpected locking issues. Thoughts? Thanks, Lukas -- Lukas Fittl
v0-0001-Introduce-pg_exclusive_locks-view.patch
Description: Binary data
