PostgreSQL自定义快捷命令

导读：之前看了一篇《PostgreSQL DBA常用SQL查询语句》文章。与文章作者讨论了一下元命令\dt，结合平时工作感觉这个命令确实有些不太智能，应该根据search_path把所有的表都列出来才是，于是尝试使用快捷命令解决，也顺道将平时工作中用到的查询总结一下。

1. 如何制作快捷命令

编辑家目录下.psqlrc文件，格式如下：

\set short_command 'SQL;'

• short_commad为自定义的快捷命令名称
• SQL里的单引号需要转义
• 需要带分号

2. 快捷命令my_dt之按search_path查看表

test用户有一个同名schma和public schema

test=> \dn
List of schemas
  Name  | Owner 
--------+-------
 public | test
 test   | test
(2 rows)

两个模式下分别创建一张同名的t表

test=> create table public.t(id int);
test=> create table test.t(id int,info text);

test=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

test=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 test   | t    | table | test
(1 row)

test=> :my_dt
 Schema |  Name  | Owner 
--------+--------+-------
 public | t      | test
 test   | t      | test
(5 rows)

快捷命令my_dt，sql语句如下：

SELECT n.nspname as "Schema",
       c.relname as "Name",
	   pg_catalog.pg_get_userbyid(c.relowner) as "Owner" 
  FROM pg_catalog.pg_class c 
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind ='r'
   AND n.nspname in('public',current_user)
ORDER BY 2,1;

语句修改来源于\dt，根据search_path中的配置，查询public和当前用户，同时先按表名称排序，便于同名表问题排查分析。

3. 快捷命令之连接数统计

test=> :my_stat_conn
 client_addr  | count 
--------------+-------
              |     6
 192.168.99.1 |     2
(2 rows)

select client_addr,count(1) 
from pg_stat_activity 
group by 1

经常遇到搞java开发的同学用eclipse不断重启web应用把数据库连接占满的情况。

4. 快捷命令之查询活动会话

test=> :my_active_session

--SQL
select pid,usename,datname,query,client_addr 
from pg_stat_activity 
where pid<>pg_backend_pid() 
and state='active' 
order by query;

5. 快捷命令之查询等待事件

test=> :my_wait_event

--SQL
select pid,usename,datname,query,client_addr,wait_event_type,wait_event 
from pg_stat_activity 
where pid<>pg_backend_pid() 
and wait_event is not null 
order by wait_event;

6. 快捷命令之查找那些表的字段使用了timestamptz类型（带时区）

test=> :my_timestamptz

--SQL
SELECT relname, attname
FROM pg_class c,
pg_attribute attr 
WHERE c.oid = attr.attrelid
and attisdropped = 'f'
and atttypid=1184
and relname !~ '^pg_';

7. 快捷命令之查找数据库维度的CTc

test=> :my_db_ctc
  datname  |           connect_granted           |       create_granted        |            temp_granted             
-----------+-------------------------------------+-----------------------------+-------------------------------------
 postgres  | {postgres,repuser,test,test1,test2} | {postgres}                  | {postgres,repuser,test,test1,test2}
 template0 | {postgres,repuser,test,test1,test2} | {postgres}                  | {postgres}
 test      | {postgres,test}                     | {postgres,test,test1,test2} | {postgres,test,test2}
 template1 | {postgres,repuser,test,test1,test2} | {postgres}                  | {postgres}
(4 rows)

--SQL
select   
  datname,   
  array_agg(usename) filter (where pri='CONNECT' and has_database_privilege (usename, datname, pri)=true) as connect_granted ,  
  array_agg(usename) filter (where pri='CREATE' and has_database_privilege (usename, datname, pri)=true) as create_granted ,  
  array_agg(usename) filter (where pri='TEMP' and has_database_privilege (usename, datname, pri)=true) as temp_granted   
from pg_user, pg_database, (values ('CREATE'),('CONNECT'),('TEMP')) t (pri) group by datname;  

参考https://github.com/digoal/blog/blob/master/201905/20190505_01.md
其实\l元命令已经足够，只是显示内容较多，没有上面显示的清爽。

8. 快捷命令之查找非当前用户创建的对象(表、视图、序列等)

test=> :my_obj_create_by_other
Schema | Name | Type | Owner | Size | Description
--------±-----±------±------±--------±------------
public | t2 | table | test2 | 0 bytes |
(1 row)

–SQL
SELECT n.nspname as “Schema”,
c.relname as “Name”,
CASE c.relkind WHEN ‘r’ THEN ‘table’ WHEN ‘v’ THEN ‘view’ WHEN ‘m’ THEN ‘materialized view’ WHEN ‘i’ THEN ‘index’ WHEN ‘S’ THEN ‘sequence’ WHEN ‘s’ THEN ‘special’ WHEN ‘f’ THEN ‘foreign table’ WHEN ‘p’ THEN ‘partitioned table’ WHEN ‘I’ THEN ‘partitioned index’ END as “Type”,
pg_catalog.pg_get_userbyid(c.relowner) as “Owner”,
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as “Size”,
pg_catalog.obj_description(c.oid, ‘pg_class’) as “Description”
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (‘r’,‘p’,‘v’,‘m’,‘S’,‘f’,’’)
AND n.nspname <> ‘pg_catalog’
AND n.nspname <> ‘information_schema’
AND n.nspname !~ ‘^pg_toast’
AND pg_catalog.pg_table_is_visible(c.oid)
and c.relowner!=to_regrole(current_user)
ORDER BY 1,2;

9. 快捷命令之查找当前用户对象的权限状态

test=> :my_obj_auth
 Schema | Name | Type  | Access privileges  | Column privileges | Policies 
--------+------+-------+--------------------+-------------------+----------
 public | t    | table | test=arwdDxt/test +|                   | 
        |      |       | test1=arwdDxt/test+|                   | 
        |      |       | test2=r/test       |                   | 
 public | t2   | table |                    |                   | 
 public | t3   | table |                    |                   | 
 test   | t1   | table | test=arwdDxt/test +|                   | 
        |      |       | test1=rw/test     +|                   | 
        |      |       | test2=d/test       |                   | 
(4 rows)

select relname,relacl 
from pg_class 
where relkind='r'
and relname !~ '^pg_';
或者
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT polname
    || CASE WHEN polcmd != '*' THEN
           E' (' || polcmd || E'):'
       ELSE E':' 
       END
    || CASE WHEN polqual IS NOT NULL THEN
           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
       ELSE E''
       END
    || CASE WHEN polwithcheck IS NOT NULL THEN
           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
       ELSE E''
       END    || CASE WHEN polroles <> '{0}' THEN
           E'\n  to: ' || pg_catalog.array_to_string(
               ARRAY(
                   SELECT rolname
                   FROM pg_catalog.pg_roles
                   WHERE oid = ANY (polroles)
                   ORDER BY 1
               ), E', ')
       ELSE E''
       END
    FROM pg_catalog.pg_policy pol
    WHERE polrelid = c.oid), E'\n')
    AS "Policies"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f');

https://github.com/digoal/blog/blob/master/201905/20190505_01.md

其他快捷命令(待整理)

往期回顾

• PostgreSQL超级用户忘记密码了怎么办
• 源码编译安装PostgreSQL
• PostgreSQL外部表插件oracle_fdw
• PG实践学习笔记
• PostgreSQL元命令之connect
• PostgreSQL递归查询
• PostgreSQL源码编译dblink
• PostgreSQL11及以上集成orafce

保持联系

本人组建了一个技术交流群：PG乐知乐享交流群。欢迎关注文章的小伙伴随缘加入，进群请加本人微信skypkmoon并备注PG乐知乐享。