A very strange SQL of PostgreSQL

今天这篇贴子讲述的问题我以前也遇到过,只因发生在一测试环境下,所以没有深究,今天在一生产环境下也遇到了这个问题,所以应该重视了,这个问题困扰了我一阵子,事情是这样的,在生产库上有一个 SQL 非常奇怪,从SQL语句来看,很像是PostgreSQL SERVER 内部执行的,可事实却不是,下面看下今天的分析步骤。

查询数据库会话

连接到数据库里查询,如下:

1
2
3
4
5
6
7
8
9
10
postgres=# select procpid,datname,client_addr,client_port,current_query from pg_stat_activity where current_query !='<IDLE>';  
25858 | uims | XXX.XXX.XXX.XXX | 57791 | SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,
CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'info
rmation_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' EL
SE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST IN
DEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' ELSE NULL END E
ND WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' EL
SE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT J
OIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
dc.relname='pg_class')

这个SQL看上去像 PostgreSQL 发出的系统维护SQL,但找不出是什么进程,后来根据 client_addr 咨询了下应用人员,回复说这是 APPLICATION 机器, 难道这个SQL是应用程序发出?

格式化SQL

格式化的SQL,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT NULL AS TABLE_CAT,  
n.nspname AS TABLE_SCHEM,
c.relname AS TABLE_NAME,
CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'
WHEN true THEN
CASE
WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL
END
WHEN n.nspname = 'pg_toast' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TOAST TABLE'
WHEN 'i' THEN 'SYSTEM TOAST INDEX'
ELSE NULL
END
ELSE
CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE'
WHEN 'i' THEN 'TEMPORARY INDEX'
ELSE NULL
END
END
WHEN false THEN
CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'v' THEN 'VIEW'
ELSE NULL
END
ELSE NULL
END AS TABLE_TYPE,
d.description AS REMARKS
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid
AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid = dc.oid AND dc.relname='pg_class');

执行此 SQL

执行以上SQL,并取一条数据,如下:

1
2
3
4
table_cat | table_schem | table_name | table_type | remarks  
-----------+-------------+-----------------------------------+--------------------+---------
| pg_toast | pg_foreign_data_wrapper_oid_index | SYSTEM TOAST INDEX |
(1 row)

这个SQL执行很快,毫秒级,从查询结果来看,应该是查 TABLE 列表。

国外PostgreSQL论坛发贴求助

实在找不出什么原因,后来在国外 PostgreSQL 社区论坛发贴,从上面的解释终于得到了答案。

回复如下:

  1. 后来在一国外论坛上发贴,上面回复说这个SQL是 PostgreSQL JDBC driver 发出的一个
    getTables()函数,原文如下 :It is part of the getTables() implementation in the
    postgresql JDBC driver.

  2. 后来也咨询了下我们的开发人员,也解释说这是 PostgreSQL JDBC driver 发出的函数,
    用来获取TABLE列表,这个函数在连接进程空闲时会执行,也可以通过配置JDBC配置,
    禁止执行这函数。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%