On Jun 30, 2014, at 12:32 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Jeff Frost <j...@pgexperts.com> writes:
>> Sampling pg_locks on the primary shows ~50 locks with ExclusiveLock mode:
>
>> mode | count
>> --------------------------+-------
>> AccessExclusiveLock | 11
>> AccessShareLock | 2089
>> ExclusiveLock | 46
>> RowExclusiveLock | 81
>> RowShareLock | 17
>> ShareLock | 4
>> ShareUpdateExclusiveLock | 5
>
> That's not too helpful if you don't pay attention to what the lock is on;
> it's likely that all the ExclusiveLocks are on transactions' own XIDs,
> which isn't relevant to the standby's behavior. The AccessExclusiveLocks
> are probably interesting though --- you should look to see what those
> are on.
You're right about the ExclusiveLocks.
Here's how the AccessExclusiveLocks look:
locktype | database | relation | page | tuple | virtualxid | transactionid |
classid | objid | objsubid | virtualtransaction | pid | mode
| granted
----------+----------+------------+------+-------+------------+---------------+---------+------------+----------+--------------------+-------+---------------------+---------
relation | 111285 | 3245291551 | | | | |
| | | 233/170813 | 23509 |
AccessExclusiveLock | t
relation | 111285 | 3245292820 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292833 | | | | |
| | | 173/1723993 | 23407 |
AccessExclusiveLock | t
relation | 111285 | 3245287874 | | | | |
| | | 133/3818415 | 23348 |
AccessExclusiveLock | t
relation | 111285 | 3245292836 | | | | |
| | | 173/1723993 | 23407 |
AccessExclusiveLock | t
relation | 111285 | 3245292774 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292734 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292827 | | | | |
| | | 173/1723993 | 23407 |
AccessExclusiveLock | t
relation | 111285 | 3245288540 | | | | |
| | | 133/3818415 | 23348 |
AccessExclusiveLock | t
relation | 111285 | 3245292773 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292775 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292743 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292751 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245288669 | | | | |
| | | 133/3818415 | 23348 |
AccessExclusiveLock | t
relation | 111285 | 3245292817 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245288657 | | | | |
| | | 133/3818415 | 23348 |
AccessExclusiveLock | t
object | 111285 | | | | | |
2615 | 1246019760 | 0 | 233/170813 | 23509 |
AccessExclusiveLock | t
relation | 111285 | 3245292746 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245287876 | | | | |
| | | 133/3818415 | 23348 |
AccessExclusiveLock | t
relation | 111285 | 3245292739 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292826 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292825 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245292832 | | | | |
| | | 173/1723993 | 23407 |
AccessExclusiveLock | t
relation | 111285 | 3245292740 | | | | |
| | | 5/22498235 | 23427 |
AccessExclusiveLock | t
relation | 111285 | 3245287871 | | | | |
| | | 133/3818415 | 23348 |
AccessExclusiveLock | t
(25 rows)
And if you go fishing in pg_class for any of the oids, you don't find anything:
SELECT s.procpid,
s.query_start,
n.nspname,
c.relname,
l.mode,
l.granted,
s.current_query
FROM pg_locks l,
pg_class c,
pg_stat_activity s,
pg_namespace n
WHERE l.relation = c.oid
AND l.pid = s.procpid
AND c.relnamespace = n.oid
AND l.mode = 'AccessExclusiveLock';
procpid | query_start | nspname | relname | mode | granted | current_query
---------+-------------+---------+---------+------+---------+---------------
(0 rows)
Temp tables maybe?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
