Postgres table locks

I’ve just been looking into some issues with locking in Postgres and the documentation as ever has been excellent.

http://wiki.postgresql.org/wiki/Lock_Monitoring

A closer look at the queries provided suggests they don’t show table level locks. At the very least if you do a pg_dump of your database while checking those queries you see nothing, despite there definitely being some locks going on. This query probably isn’t perfect, and is simply based on a quick practical test of running pg_dump against a test db but it may help spot the table locks which could be blocking things.

select pid, usename, datname, current_query 
from pg_catalog.pg_locks l 
inner join pg_catalog.pg_stat_activity a on a.procpid = pid 
where mode like '%ExclusiveLock%';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s