Postgresql常用查看锁语句

可以通过以下SQL查询出所有的catalog view：

SELECT

n.nspname as "Schema",

c.relname as "Name",

CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",

r.rolname as "Owner"

FROM pg_catalog.pg_class c

JOIN pg_catalog.pg_roles r ON r.oid = c.relowner

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('v','')

AND n.nspname = 'pg_catalog'

AND n.nspname !~ '^pg_toast'

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

查看当前活动的客户端连接数：

SELECT count(*) FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid();

查询客户端连接的情况：

SELECT procpid,waiting,current_timestamp - least(query_start,xact_start) AS runtime,substr(current_query,1,25) AS current_query FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid();

查看当前锁的一些信息：

SELECT

locktype,

virtualtransaction,

transactionid,

nspname,

relname,

mode,

granted,

cast(date_trunc('second',query_start) AS timestamp) AS query_start,

substr(current_query,1,25) AS query

FROM

pg_locks

LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)

LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),

pg_stat_activity

WHERE

NOT pg_locks.pid=pg_backend_pid() AND

pg_locks.pid=pg_stat_activity.procpid;

查看持有锁的一些信息：

SELECT

locked.pid AS locked_pid,

locker.pid AS locker_pid,

locked_act.usename AS locked_user,

locker_act.usename AS locker_user,

locked.virtualtransaction,

locked.transactionid,

locked.locktype

FROM

pg_locks locked,

pg_locks locker,

pg_stat_activity locked_act,

pg_stat_activity locker_act

WHERE

locker.granted=true AND

locked.granted=false AND

locked.pid=locked_act.procpid AND

locker.pid=locker_act.procpid AND

(locked.virtualtransaction=locker.virtualtransaction OR

locked.transactionid=locker.transactionid);

还是查看锁的一些信息：

SELECT

locked.pid AS locked_pid,

locker.pid AS locker_pid,

locked_act.usename AS locked_user,

locker_act.usename AS locker_user,

locked.virtualtransaction,

locked.transactionid,

relname

FROM

pg_locks locked

LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid),

pg_locks locker,

pg_stat_activity locked_act,

pg_stat_activity locker_act

WHERE

locker.granted=true AND

locked.granted=false AND

locked.pid=locked_act.procpid AND

locker.pid=locker_act.procpid AND

locked.relation=locker.relation;

查看当前库表和索引的的大小并排序显示前20条：

SELECT

nspname,

relname,

pg_size_pretty(pg_relation_size(C.oid)) AS "size"

FROM pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

WHERE nspname NOT IN ('pg_catalog', 'information_schema')

ORDER BY pg_relation_size(C.oid) DESC

LIMIT 20;

pg_total_relation_size = pg_table_size + pg_indexes_size

pg_table_size = pg_relation_size + toast table + toast indexes + FSM

9.0以后：

SELECT

nspname,

relname,

relkind as "type",

pg_size_pretty(pg_table_size(C.oid)) AS size,

pg_size_pretty(pg_indexes_size(C.oid)) AS idxsize,

pg_size_pretty(pg_total_relation_size(C.oid)) as "total"

FROM pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND

nspname !~ '^pg_toast' AND

relkind IN ('r','i')

ORDER BY pg_total_relation_size(C.oid) DESC

LIMIT 20;