Thread
-
Help with pg_locks query
Bruce Momjian <bruce@momjian.us> — 2011-09-05T14:30:32Z
I am writing a talk about the lock manager for PG Open and I would like suggestions on how to improve a query in my talk. The query creates a lockinfo_hierarchy view of a recursive query on other views. The output shows the locks held and the locks being waited for: \! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g' SELECT * FROM lockinfo_hierarchy; ?column? | pid | vxid | granted | xid_lock | lock_type | relname | page | tuple ----------+-------+--------+---------+----------+---------------+----------+------+------- 1 | 24860 | 2/3106 | t | 828 | transactionid | | | 1 | 24864 | 3/42 | t | 829 | transactionid | | | 1 | 24868 | 4/78 | t | 830 | transactionid | | | 1 | 24872 | 5/22 | t | 831 | transactionid | | | 2 | 24864 | 3/42 | f | 828 | transactionid | | | 3 | 24864 | 3/42 | t | | tuple | lockdemo | 0 | 1 4 | 24868 | 4/78 | f | | tuple | lockdemo | 0 | 1 4 | 24872 | 5/22 | f | | tuple | lockdemo | 0 | 1 (8 rows) The SQL needed to reproduce this output is attached, and must be run in your personal database, e.g. postgres. What this output shows are four transactions holding locks on their own xids, transaction 3/42 waiting for 828 to complete, and 3/42 holding a row lock that 4/78 and 5/22 are waiting on. When there are multiple waiters, one transaction waits on the real xid and the others sleep waiting to be woken up later. Is there any better way to show this? (The first column is just there for debugging so you can see what part of the query generated the row.) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +