一、数据库集群信息
1、gp_segment_configration
2、pg_filespace_entry
这两个表是在pg_global表空间下面的,是全局表。
用来查看集群segment信息,比如segment个数。
二、常用数据字典表
1、pg_class
保存了所有表、视图、序列、索引元数据信息,每个DDL/DML操作都必须跟这个表发生关系。
2、pg_attribute
记录字段的内容
3、gp_distribution_policy
记录表的分布键
4、pg_statistic 和 pg_stats
数据库中表的统计信息保存在pg_statistic中
pg_stats可以方便帮我们查看pg_statistic的内容
5、pg_partition
记录分区表的信息
6、pg_partition_rule
分区表的分区规则
7、pg_partitions
三、字典表的应用
1、查看表的信息
1)从catalog中查询
2)从information_schema查询
select * from information_schema.tables where table_name ='';
1、获取字段信息
1)从catalog中查询
SELECT a.attname,pg_catalog.format_type(a.atttypid,( pg_catalog.pg_attribute a, a.atttypmod) AS data_type
FROM pg_catalog.pg_attribute a,
(SELECT c. oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relname = 'pg_class'
AND n.nspname = 'pg_catalog'
) b
WHERE a.attrelid = b.oid
AND a.attnum > 0
AND NOT a.attisdropped ORDER BY a.attnum;
使用regclass就会简化很多:
SELECT a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_attribute a
WHERE a.attrelid ='pg_catalog.pg_class'::regclass
AND a.attnum > 0
AND NOT a.attisdropped ORDER BY a.attnum;
2)从information_schema查询
select * from information_schema.columns where table_name = '';
2、获取表的分布键
select a.attrnums[i.i],b.attname,a.localoid::regclass
from gp_distribution_policy a,
(select generate_series(1,10))i (i),
pg_attribute b
where a.attrnums[i.i) is not null
and a.localoid=b.attrelid
and a.attrnums[i.i]=b.attnum
and a.localoid='public.cxfa2 '::regclass
order by i.i;
3、获取一个视图定义
testDB=# create table cxfa( a int) distributed by (a);
CREATE TABLE
testDB=# create view v_cxfa as select * from cxfa;
CREATE VIEW
testDB=# select pg_get_viewdef('v_cxfa', t