How can you have an Exclusive Lock on nothing? please help me understand this lock monitoring query output

Aleksey Tsalolikhin <atsaloli.tech@gmail.com> writes:

Hi. I use the following query (from
http://wiki.postgresql.org/wiki/Lock_Monitoring)
to monitor locks; and I've got an ExlusiveLock that does not have a relation
name associated with it. What is locked with the Exclusive Lock in this case,
please? (it's between "d" and "e" tables below)

Try printing all fields from the pg_lock view and you may be
enlightened.

Transaction IDs are one example of a lock that is not on a relation.

Also be aware that if you are viewing a lock that is for some other DB
besides the one you're sitting in, the pg_class join will give nothing.

Also be aware that

psql -U postgres -d ddcKeyGen -c 'select
pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,50),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;'

Output:

a_index | | AccessShareLock
| t | user |
| 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798
b_index | | AccessShareLock
| t | user |
| 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798
c_index | | AccessShareLock
| t | user |
| 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798
d | |
AccessShareLock | t | user |
| 2011-04-14 17:36:01.257669-07 |
00:21:28.847825 | 16798
|
| ExclusiveLock | t | user |
| 2011-04-14 17:36:01.257669-07 |
00:21:28.847825 | 16798
e | |
AccessShareLock | t | user |
| 2011-04-14 17:36:01.257669-07 |
00:21:28.847825 | 16798
f_index | | ShareLock
| t | user |
| 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798
g_index | | AccessShareLock | t
| user | |
2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798

Thanks,
Aleksey

--
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144

On Fri, Apr 15, 2011 at 2:12 AM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

Hi.  I use the following query (from
http://wiki.postgresql.org/wiki/Lock_Monitoring)
to monitor locks; and I've got an ExlusiveLock that does not have a relation
name associated with it.  What is locked with the Exclusive Lock in this case,
please?  (it's between "d" and "e" tables below)

Locks can be held on databases, relations, rows and also show for transactions.

So the absence of a relation name just means it is one of the other
kinds of lock.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services