digoal
2017-12-08
PostgreSQL , pg_stat , 实时质量监控
PostgreSQL中，所有对象的定义都在元数据库中，详见
https://www.postgresql.org/docs/10/static/catalogs.html
除了元数据库，还有一些管理函数，如下
https://www.postgresql.org/docs/10/static/functions-admin.html
下面简单介绍一下元数据库的使用。
1、识别临时表、UNLOGGED TABLE、临时表
select relname from pg_class where relpersistence=? and relkind='r';
pg_class 的relpersistence用于识别表是什么表(正常表、不记日志表、临时表)。 relkind用于识别是什么对象类别(表、索引、序列、切片、视图、物化视图、复合类型、外部表、分区表)。
relpersistence
p = permanent table, u = unlogged table, t = temporary table
relkind
r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table
pg_class.relstorage 用于区分是什么存储
h = 堆表(heap)
a = append only row存储表
c = append only column存储表
pg_proc
pg_database
pg_tablespace
pg_namespace
pg_roles
pg_am
使用这些函数接口，可以获得对应对象的定义。
pg_get_indexdef
pg_get_functiondef
pg_get_triggerdef
pg_get_ruledef
pg_get_viewdef
pg_get_constraintdef
例子
postgres=# select * from pg_get_indexdef('idx_tbl2_1'::regclass);
pg_get_indexdef
--------------------------------------------------
CREATE INDEX idx_tbl2_1 ON tbl2 USING btree (id)
(1 row)
没有show create的语法，pg_dump的结构导出是如何做到的？pg_admin GUI工具又是如何生成DDL的？
从pg_admin工具的源码中，可以获知如何生成建表（包括其他对象）的DDL。
从pg_dump的源码中，可以获知如何生成建表（包括其他对象）的DDL。
从 Information Schema 中获得对象定义，使用依赖关系系统表，获得相关对象，并获得相关对象的定义。
《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》
https://stackoverflow.com/questions/25639088/show-table-structure-and-list-of-tables-in-postgresql
http://www.postgresqltutorial.com/postgresql-describe-table/
https://www.postgresql.org/docs/current/information-schema.html
https://dba.stackexchange.com/questions/48451/how-to-get-table-definition-of-a-postgresql-table
postgres=# create or replace function describe_table(tbl_name text) returns table(column_name
varchar, data_type varchar,character_maximum_length int, numeric_precision int, numeric_precision_radix int, numeric_scale int) as $$
select column_name, data_type, character_maximum_length, numeric_precision,numeric_precision_radix,numeric_scale
from INFORMATION_SCHEMA.COLUMNS where table_name = $1;
$$
language 'sql';
CREATE FUNCTION
postgres=# select * from describe_table('test');
column_name | data_type | character_maximum_length | numeric_precision | numeric_precision_radix | numeric_scale
-------------+-----------------------------+--------------------------+-------------------+-------------------------+---------------
id | integer | | 32 | 2 | 0
c1 | bigint | | 64 | 2 | 0
c2 | smallint | | 16 | 2 | 0
c3 | double precision | | 53 | 2 |
c4 | real | | 24 | 2 |
c5 | numeric | | | 10 |
c6 | numeric | | 10 | 10 | 2
c7 | timestamp without time zone | | | |
c8 | character varying | 100 | | |
(9 rows)
使用psql客户端得到获取对象定义的SQL，并从中获取DDL
digoal@pg11-test-> psql -E
psql (11.1)
Type "help" for help.
postgres=# \d+ test
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
, c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '1912240';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attstorage,
CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,
pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1912240' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT inhparent::pg_catalog.regclass,
pg_catalog.pg_get_expr(c.relpartbound, inhrelid),
pg_catalog.pg_get_partition_constraintdef(inhrelid)
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid = inhrelid
WHERE c.oid = '1912240' AND c.relispartition;
**************************
********* QUERY **********
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '1912240' ORDER BY 1;
**************************
********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
FROM pg_catalog.unnest(stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND
a.attnum = s.attnum AND NOT attisdropped)) AS columns,
'd' = any(stxkind) AS ndist_enabled,
'f' = any(stxkind) AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1912240'
ORDER BY 1;
**************************
********* QUERY **********
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
WHERE pr.prrelid = '1912240'
UNION ALL
SELECT pubname
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('1912240')
ORDER BY 1;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '1912240' AND c.relkind != 'p' ORDER BY inhseqno;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '1912240' ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
c1 | bigint | | | | plain | |
c2 | smallint | | | | plain | |
c3 | double precision | | | | plain | |
c4 | real | | | | plain | |
c5 | numeric | | | | main | |
c6 | numeric(10,2) | | | | main | |
c7 | timestamp(2) without time zone | | | | plain | |
c8 | character varying(100) | | | | extended | |
https://www.postgresql.org/docs/10/static/catalogs.html
https://www.postgresql.org/docs/10/static/functions-admin.html
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
