Help with pg_locks query

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Help with pg_locks query
Date: 2011-09-05 14:30:32
Message-ID: 201109051430.p85EUWU26292@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Attachment Content-Type Size
unknown_filename text/plain 3.6 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2011-09-05 15:07:12 Re: [COMMITTERS] pgsql: Clean up the #include mess a little.
Previous Message Kevin Grittner 2011-09-05 14:27:34 CF 2011-09-15 Call for Reviewers