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

Aleksey Tsalolikhin <atsaloli(dot)tech(at)gmail(dot)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(at)comcast(dot)net
p: 305.321.1144