Postgresql常用查看锁语句
来源:互联网 发布:java reflect method 编辑:程序博客网 时间:2024/06/06 01:05
可以通过以下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;
- Postgresql常用查看锁语句
- postgresql 常用语句
- Postgresql常用SQL语句
- Postgresql常用SQL语句
- PostgreSQL常用语句
- postgreSQL常用语句
- 常用postgresql数据库语句
- postgresql常用的查询语句
- postgresql中存储过程语句查看
- Postgresql查看SQL语句执行效率
- postgresql 常用语句的整理(更新)
- oracle查看权限常用语句
- Oracle 常用性能查看语句
- postgresql 查看锁的sql
- postgresql中查看操作执行的具体sql语句
- 查看PostgreSQL数据库中SQL语句的执行计划
- postgreSQL 里常用的查看表空间,数据库实例大小
- linux 下常用查看Apache状态语句
- 让百度收录信息发布的技巧
- Eclipse 或 MyEclipse 不能自动编译 class文件
- 二进制
- 梁山政治 笔记
- Android中startActivityForResult
- Postgresql常用查看锁语句
- JSTL重要技术经验积累
- socket设置
- Create web application with Host Header in SharePoint 2013
- Intent:异步消息(Activity-Service-Receiver)
- jsp核心标签库2
- 我们一起学习Makefile
- HTML5 基础知识
- oracle的字符转义