Try this query for a start, and add system tables to the join to find what you want:-
-- displays locks with database name and username, but not table
-- CAUTION: this query may impact system performance as you are selecting from system tables
select
relation
,
c.relname,
u.usename,
pid,
mode,
transaction,
granted,
datname,
u.usesysid,
usesuper--*
from
pg_locks l, pg_stat_activity s, pg_user u , pg_class cwhere
l.pid = s. procpid and l.relation = c .relfilenode and s.usesysid = u .usesysidorder
by l.pid;On 7/31/07, Milen A. Radev <milen@xxxxxxxxx> wrote:
I have performance problems with a DB (slow queries) and I suspect the
main cause is that a lot of queries wait for a lock on one small
table. That's why I need some stats about the number and (average)
wait-time for locks (even only for this particular table).
After a bit of googling I found a project in PgFoundry with a
promising description - http://pgfoundry.org/projects/pglockdiag/.
Unfortunately the projects seems stillborn - no published files and
nothing in CVS.
--
Milen A. Radev
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
