GP数据字典.docx
- 文档编号:20104047
- 上传时间:2023-04-25
- 格式:DOCX
- 页数:21
- 大小:22.04KB
GP数据字典.docx
《GP数据字典.docx》由会员分享,可在线阅读,更多相关《GP数据字典.docx(21页珍藏版)》请在冰豆网上搜索。
GP数据字典
greenplum是基于postgresql开发的分布式数据库,里面大部分的数据字典是一样的。
我们在维护gp的时候对gp的数据字典比较熟悉,特此分享给大家。
在这里不会详细介绍每个字典的内容,只会介绍常见的应用以及一些已经封装好了的函数。
具体的介绍大家可以去看postgresql的中文文档,里面有详细的解释。
1.postgresql中,所有数据库的对象都是用oid连接在一起的。
这样子会造成我们在理解数据字典的时候有一些不知所措。
下面介绍几个函数,可以简化很多的操作。
名字
引用
描述
regproc
pg_proc
函数名字
regprocedure
pg_proc
带参数类型的函数
regoper
pg_operator
操作符名
regoperator
pg_operator
带参数类型的操作符
regclass
pg_class
关系名
最常用的是regclass,关联数据字典的oid,使用方法如下:
aligputf8=#select1259:
:
regclass;
regclass
———-
pg_class
(1row)
aligputf8=#selectoid,relnamefrompg_classwhereoid=’pg_class’:
:
regclass;
oid |relname
——+———-
1259|pg_class
(1row)
这样子就可以通过regclass寻找一个表的信息,就不用去关联pg_class跟pg_namespace(记录schema信息)了。
比较方便。
同样的,其他几个类型也是一样的用法,如regproc(regprocedure)是跟pg_proc(保存普通函数的命令)关联的。
regoper(regoperator)是跟pg_operator(操作符)的oid关联的。
Eg:
aligputf8=#selectoid:
:
regoper,oid:
:
regoperator,oid,oprnamefrompg_operatorlimit1;
oid | oid |oid|oprname
————–+——————-+—–+———
pg_catalog.=|=(integer,bigint)| 15|=
(1row)
aligputf8=#selectoid:
:
regproc,oid:
:
regprocedure,oid,pronamefrompg_proclimit1;
oid | oid |oid |proname
——–+—————–+——+———
boolin|boolin(cstring)|1242|boolin
(1row)
下面给给出如何使用regclass的例子。
2.获取表的字段信息。
表名是放在pg_class,schema名是放在pg_namespace里面的,字段信息是放在pg_attribute里面的。
一般是关联这三张表:
eg:
SELECTa.attname,pg_catalog.format_type(a.atttypid,a.atttypmod)ASdata_type
FROM pg_catalog.pg_attributea,
(
SELECT c.oid
FROM pg_catalog.pg_classc
LEFTJOINpg_catalog.pg_namespacen
ONn.oid=c.relnamespace
WHEREc.relname=‘pg_class’
ANDn.nspname=‘pg_catalog’
)b
WHEREa.attrelid=b.oid
ANDa.attnum>0
ANDNOTa.attisdroppedORDERBYa.attnum;
如果使用regclass就会简化很多:
SELECTa.attname,pg_catalog.format_type(a.atttypid,a.atttypmod)ASdata_type
FROM pg_catalog.pg_attributea
WHEREa.attrelid=’pg_catalog.pg_class’:
:
regclass
ANDa.attnum>0
ANDNOTa.attisdroppedORDERBYa.attnum;
其实regclass就是一个类型,oid或text到regclass有一个类型转换,跟多表关联不一样,多数据字典表关联的话,如果表不存在,会返回空记录,不会报错,而如果采用regclass则会报错,所以在不确定表是否存在的情况下,慎用regclass。
3.获取表的分布键:
gp_distribution_policy记录这表的数据字典,localoid跟pg_class的oid关联。
attrnums是一个数组,记录字段的attnum,跟pg_attribute里面的attnum关联的。
aligputf8=#createtablecxfa2(aint,bint,cint,dint)distributedby(c,a);
aligputf8=#select*fromgp_distribution_policywherelocaloid=’cxfa2′:
:
regclass ;
localoid|attrnums
———-+———-
334868|{3,1}
(1row)
selecta.attrnums[i.i],b.attname,a.localoid:
:
regclass
fromgp_distribution_policya,
(selectgenerate_series(1,10))i(i),
pg_attributeb
wherea.attrnums[i.i]isnotnull
anda.localoid=b.attrelid
anda.attrnums[i.i]=b.attnum
anda.localoid=’public.cxfa2′:
:
regclass
orderbyi.i;
结果如下:
attrnums|attname|localoid
———-+———+———-
3|c |cxfa2
1|a |cxfa2
(2rows)
4.获取一个视图的定义。
aligputf8=#\dfpg_get_viewdef
Listoffunctions
Schema | Name |Resultdatatype|Argumentdatatypes
————+—————-+——————+———————
pg_catalog|pg_get_viewdef|text |oid
pg_catalog|pg_get_viewdef|text |oid,boolean
pg_catalog|pg_get_viewdef|text |text
pg_catalog|pg_get_viewdef|text |text,boolean
(4rows)
使用这个系统函数可以获取视图的定义,可以传入oid或者是表名,第二个参数表示是否格式化输出,默认不格式化输出。
aligputf8=#createtablecxfa(aint)distributedby(a);
CREATETABLE
aligputf8=#createviewv_cxfaasselect*fromcxfa;
CREATEVIEW
aligputf8=#selectpg_get_viewdef(’v_cxfa’,true);
pg_get_viewdef
—————-
SELECTcxfa.a
FROMcxfa;
(1row)
其实这个函数是去获取数据字典pg_rewrite(存储为表和视图定义的重写规则),将规则重新算出sql展现给我们。
可以通过下面sql去查询数据库保存的重写规则。
aligputf8=#selectev_actionfrompg_rewritewhereev_class=’v_cxfa’:
:
regclass;
ev_action
————————————————————————————————————————————————————
({QUERY:
commandType1:
querySource0:
canSetTagtrue:
utilityStmt<>:
resultRelation0:
into<>:
intoOptions<>:
intoOnCommit0:
intoTableSpaceName<>:
hasAggsfalse:
hasWindFuncsfalse:
hasSubLinksfalse:
rtable({RTE:
alias{ALIAS:
aliasname*OLD*:
colnames<>}:
eref{ALIAS:
aliasname*OLD*:
colnames(”a”)}:
rtekind0:
relid334939:
inhfalse:
inFromClfalse:
requiredPerms0:
checkAsUser0:
forceDistRandomfalse:
pseudocols<>}{RTE:
alias{ALIAS:
aliasname*NEW*:
colnames<>}:
eref{ALIAS:
aliasname*NEW*:
colnames(”a”)}:
rtekind0:
relid334939:
inhfalse:
inFromClfalse:
requiredPerms0:
checkAsUser0:
forceDistRandomfalse:
pseudocols<>}{RTE:
alias<>:
eref{ALIAS:
aliasnamecxfa:
colnames(”a”)}:
rtekind0:
relid334930:
inhtrue:
inFromCltrue:
requiredPerms2:
checkAsUser0:
forceDistRandomfalse:
pseudocols<>}):
jointree{FROMEXPR:
fromlist({RANGETBLREF:
rtindex3}):
quals<>}:
targetList({TARGETENTRY:
expr{VAR:
varno3:
varattno1:
vartype23:
vartypmod-1:
varlevelsup0:
varnoold3:
varoattno1}:
resno1:
resnamea:
ressortgroupref0:
resorigtbl334930:
resorigcol1:
resjunkfalse}):
returningList<>:
groupClause<>:
havingQual<>:
windowClause<>:
distinctClause<>:
sortClause<>:
limitOffset<>:
limitCount<>:
rowMarks<>:
setOperations<>:
resultRelations<>:
result_partitions<>:
result_aosegnos<>:
returningLists<>:
intoOidInfo.relOid0:
intoOidIptypeOid0:
intoOidInfo.toastOid0:
intoOidInfo.toastIndexOid0:
intoOidInfo.toastComptypeOid0:
intoOidInfo.aosegOid0:
intoOidInfo.aosegIndexOid0:
intoOidInfo.aosegComptypeOid0})
(1row)
与pg_get_viewdef类似的函数还有如下,其原理都是差不多的,将数据字典的重写规则翻译为sql:
aligputf8=#\dfpg_get_*def
Listoffunctions
Schema | Name |Resultdatatype| Argumentdatatypes
————+—————————+——————+———————–
pg_catalog|pg_get_constraintdef |text |oid
pg_catalog|pg_get_constraintdef |text |oid,boolean
pg_catalog|pg_get_indexdef |text |oid
pg_catalog|pg_get_indexdef |text |oid,integer,boolean
pg_catalog|pg_get_partition_def |text |oid
pg_catalog|pg_get_partition_def |text |oid,boolean
pg_catalog|pg_get_partition_rule_def|text |oid
pg_catalog|pg_get_partition_rule_def|text |oid,boolean
pg_catalog|pg_get_ruledef |text |oid
pg_catalog|pg_get_ruledef |text |oid,boolean
pg_catalog|pg_get_triggerdef |text |oid
注:
触发器在greenplum里面是不支持的。
5.分区表相关操作
\d一个表是看不出一个表是否是分区表的,所以必须查询数据字典才能知道。
查分区表可以通过pg_partitions跟pg_partition_columns这两个视图来查询,但是这两个视图的结构非常复杂,在线上gp上数据字典都非常大,没有充分利用到索引,这个查询起来效率太低了。
我们直接通过数据字典的实体表来查询的话,会快很多。
首先创建一个分区表:
createtablepublic.cxfa3(
id integer
,name charactervarying(20)
,birth date
)Distributedby(id)
PARTITIONBYrange(birth)
(
PARTITIONp19860801START(’1986-08-01′:
:
date)END(’1986-08-02′:
:
date)EVERY(’1day’:
:
interval),
PARTITIONp19860802START(’1986-08-02′:
:
date)END(’1986-08-03′:
:
date)EVERY(’1day’:
:
interval)
);
5.1查询一个表是否是分区表
表pg_partition:
每一个分区表的父表有一行记录。
parkind:
表示分区类型(range或者list)。
parnatts:
分区建个数。
paratts:
分区键,跟pg_attribute关联,是一个列表。
表pg_partition_rule:
保存分区表每一个子分区的分区名以及分区规则等。
aligputf8=#selectcount(*)frompg_partitionwhereparrelid=’public.cxfa3′:
:
regclass;
count
——-
1
(1row)
只要count的结果是=1的,表示该表是分区表,否则该表不是分区表。
每个分区表在里面只有一行记录。
5.2查询一个表的分区键
aligputf8=#selectattnameascolumnname
aligputf8-# frompg_attributea,pg_partitionb
aligputf8-# wherea.attnum=b.paratts[0]
aligputf8-# andb.parrelid=a.attrelid
aligputf8-# anda.attrelid=’public.cxfa3′:
:
regclass;
columnname
————
birth
(1row)
由于现在gp上面的分区键都是一个的,所以为了效率,我们也只是获取第一个分区键
5.3查询分区表每个分区的具体信息
aligputf8=#SELECTpp.parrelid:
:
regclass,pr1.parchildrelid:
:
regclass,pr1.parname,
aligputf8-# CASE
aligputf8-# WHENpp.parkind=‘h’:
:
”char”THEN‘hash’:
:
text
aligputf8-# WHENpp.parkind=‘r’:
:
”char”THEN‘range’:
:
text
aligputf8-# WHENpp.parkind=‘l’:
:
”char”THEN‘list’:
:
text
aligputf8-# ELSENULL:
:
text
aligputf8-# ENDASpartitiontype,
aligputf8-# pg_get_partition_rule_def(pr1.oid,true)ASpartitionboundary
aligputf8-#FROM pg_partitionpp,pg_partition_rulepr1
aligputf8-#WHEREpp.paristemplate=falseANDpp.parrelid=‘cxfa3′:
:
regclassANDpr1.paroid=pp.oid
aligputf8-#orderbypr1.parname;
parrelid| parchildrelid | parname |partitiontype| partitionboundary
———-+———————–+———–+—————+—————————————————————————————————
cxfa3 |cxfa3_1_prt_p19860801|p19860801|range |PARTITIONp19860801START(’1986-08-01′:
:
date)END(’1986-08-02′:
:
date)EVERY(’1day’:
:
interval)
cxfa3 |cxfa3_1_prt_p19860802|p19860802|range |PARTITIONp19860802START(’1986-08-02′:
:
date)END(’1986-08-03′:
:
date)EVERY(’1day’:
:
interval)
(2rows)
6.查询comment(备注信息)
comment信息是放在表pg_description中的。
名字
类型
引用
描述
objoid
oid
任意oid属性
这条描述所描述的对象的OID
classoid
oid
pg_class.oid
这个对象出现的系统表的OID
objsubid
int4
对于一个表字段的注释,它是字段号(objoid和classoid指向表自身)。
对于其它对象类型,它是零。
description
text
作为对该对象的描述的任意文本
查询在表上的comment信息:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- GP 数据 字典