On Sun, 2008-01-20 at 16:54 -0500, Greg Smith wrote: > You can look at who has locks on what using pg_locks, see > http://www.postgresql.org/docs/8.2/static/view-pg-locks.html I use the following view to ferret out locking problems. Briefly, look for rows with state='WAIT', then find RUN rows for the same <db,schema,relation> that have an exclusive lock. -Reece CREATE OR REPLACE VIEW pgutils.locks AS SELECT l.pid, d.datname AS "database", n.nspname AS "schema", c.relname AS relation, l.locktype, l."mode", CASE l."granted" WHEN true THEN 'RUN'::text ELSE 'WAIT'::text END AS state, a.usename, a.current_query, to_char(now() - a.query_start, 'HH24:MI:SS'::text) AS duration FROM pg_locks l JOIN pg_database d ON l."database" = d.oid JOIN pg_class c ON l.relation = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_stat_activity a ON l.pid = a.procpid ORDER BY l.pid, d.datname, n.nspname, c.relname, l."granted"; eg=> select * from pgutils.locks ; pid | database | schema | relation | locktype | mode | state | usename | current_query | duration -------+----------+------------+----------------------------+----------+-----------------+-------+---------+-------------------------------+---------- 15716 | csb-dev | pg_catalog | pg_class | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00 15716 | csb-dev | pg_catalog | pg_class_oid_index | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00 15716 | csb-dev | pg_catalog | pg_class_relname_nsp_index | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00 ... -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
