Re: Deadlock in Postgres 8.2

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 *
frompgutils.locks ; | 00:00:00 
 15716 | csb-dev  | pg_catalog | pg_class_oid_index         | relation | AccessShareLock | RUN   | rkh     | select *
frompgutils.locks ; | 00:00:00 
 15716 | csb-dev  | pg_catalog | pg_class_relname_nsp_index | relation | AccessShareLock | RUN   | rkh     | select *
frompgutils.locks ; | 00:00:00 
...


--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0