greenplum(postgresql)之数据字典

概述本文转自：http://www.voidcn.com/article/p-prxbfphk-bbx.html  greenplum是基于postgresql开发的分布式数据库，里面大部分的数据字典是一样的。我们在维护gp的时候对gp的数据字典比较熟悉，特此分享给大家。在这里不会详细介绍每个字典的内容，只会介绍常见的应用以及一些已经封装好了的函数。具体的介绍大家可以去看postgresql的中文文档

本文转自：http://www.jb51.cc/article/p-prxbfphk-bbx.html

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，使用方法如下：

view plain 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:

selectoID::regoper,oID::regoperator,oID,oprnamefrompg_operatorlimit1; oID|oID|oID|oprname --------------+-------------------+-----+--------- pg_catalog.=|=(integer,bigint)|15|= aligputf8=#selectoID::regproc,oID::regprocedure,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 FROMpg_catalog.pg_attributea, ( SELECTc.oID FROMpg_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就会简化很多：

FROMpg_catalog.pg_attributea WHEREa.attrelID='pg_catalog.pg_class'::regclass BYa.attnum;

其实regclass就是一个类型，oID或text到regclass有一个类型转换，跟多表关联不一样，多数据字典表关联的话，如果表不存在，会返回空记录，不会报错，而如果采用regclass则会报错，所以在不确定表是否存在的情况下，慎用regclass。

3.获取表的分布键：

gp_distribution_policy记录这表的数据字典，localoID跟pg_class的oID关联。attrnums是一个数组，记录字段的attnum，跟pg_attribute里面的attnum关联的。

createtablecxfa2(aint,bint)distributedby(c,a); ligputf8=#select*fromgp_distribution_policywherelocaloID='cxfa2'::regclass; localoID|attrnums ---------+---------- 334868|{3,1} 1row) selecta.attrnums[i.i],b.attname,a.localoID::regclass fromgp_distribution_policya,255); line-height:21px"> (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,255); line-height:21px"> (4rows)

使用这个系统函数可以获取视图的定义，可以传入oID或者是表名，第二个参数表示是否格式化输出，默认不格式化输出。

tablecxfa(aby(a); CREATEtable vIEwv_cxfaasfromcxfa; VIEW selectpg_get_vIEwdef('v_cxfa',153); background-color:inherit; Font-weight:bold">true); pg_get_vIEwdef ---------------- SELECTcxfa.a FROMcxfa; (1row)

其实这个函数是去获取数据字典pg_rewrite（存储为表和视图定义的重写规则），将规则重新算出sql展现给我们。可以通过下面sql去查询数据库保存的重写规则。

aligputf8=# select ev_action from pg_rewrite where ev_class='v_cxfa'::regclass;

ev_action

------------------------------------------------------------------------------------------------------------------------------------------------------------

({query :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :into <> :intoOptions <> :intoOnCommit 0 :intotableSpacename <> :hasAggs false :hasWindFuncs false :hasSublinks false :rtable ({RTE :alias {AliAS :aliasname *olD* :colnames <>} :eref {AliAS :aliasname *olD* :colnames ("a")} :rtekind 0 :relID 334939 :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :forcedistRandom false :pseudocols <>} {RTE :alias {AliAS :aliasname *NEW* :colnames <>} :eref {AliAS :aliasname *NEW* :colnames ("a")} :rtekind 0 :relID 334939 :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :forcedistRandom false :pseudocols <>} {RTE :alias <> :eref {AliAS :aliasname cxfa :colnames ("a")} :rtekind 0 :relID 334930 :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :forcedistRandom false :pseudocols <>}) :jointree {FROMEXPR :fromList ({RANGETBLREF :rtindex 3}) :quals <>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 1} :resno 1 :resname a :ressortgroupref 0 :resorigtbl 334930 :resorigcol 1 :resjunk false}) :returningList <> :groupClause <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setoperations <> :resultRelations <> :result_partitions <> :result_aosegnos <> :returningLists <> :intoOIDInfo.relOID 0 :intoOIDInfo.comptypeOID 0 :intoOIDInfo.toastOID 0 :intoOIDInfo.toastIndexOID 0 :intoOIDInfo.toastComptypeOID 0 :intoOIDInfo.aosegOID 0 :intoOIDInfo.aosegIndexOID 0 :intoOIDInfo.aosegComptypeOID 0})

(1 row)

与pg_get_vIEwdef类似的函数还有如下，其原理都是差不多的，将数据字典的重写规则翻译为sql：

aligputf8=#\dfpg_get_*def ------------+---------------------------+------------------+----------------------- pg_catalog|pg_get_constraintdef|text|oID pg_catalog|pg_get_constraintdef|text|oID,255); line-height:21px"> pg_catalog|pg_get_indexdef|text|oID pg_catalog|pg_get_indexdef|text|oID,pg_catalog|pg_get_partition_def|text|oID pg_catalog|pg_get_partition_def|text|oID,255); line-height:21px"> pg_catalog|pg_get_partition_rule_def|text|oID pg_catalog|pg_get_partition_rule_def|text|oID,255); line-height:21px"> pg_catalog|pg_get_ruledef|text|oID pg_catalog|pg_get_ruledef|text|oID,255); line-height:21px"> pg_catalog|pg_get_triggerdef|text|oID

注：触发器在greenplum里面是不支持的。

5.分区表相关 *** 作

\d一个表是看不出一个表是否是分区表的，所以必须查询数据字典才能知道。查分区表可以通过pg_partitions跟pg_partition_columns这两个视图来查询，但是这两个视图的结构非常复杂，在线上gp上数据字典都非常大，没有充分利用到索引，这个查询起来效率太低了。我们直接通过数据字典的实体表来查询的话，会快很多。

首先创建一个分区表：

tablepublic.cxfa3( IDinteger ,153); background-color:inherit; Font-weight:bold">namecharactervarying(20) ,birthdate )distributedby(ID) PARTITIONBYrange(birth) PARTITIONp19860801START('1986-08-01'::date)END('1986-08-02'::date)EVERY('1day'::interval),255); line-height:21px"> PARTITIONp19860802START('1986-08-02'::END('1986-08-03'::date)EVERY('1day'::interval) ); 5.1 查询一个表是否是分区表

表pg_partition：每一个分区表的父表有一行记录。

parkind： 表示分区类型（range 或者 List）。

parnatts： 分区建个数。

paratts： 分区键，跟pg_attribute关联，是一个列表。

表pg_partition_rule：保存分区表每一个子分区的分区名以及分区规则等。

selectcount(*)frompg_partitionwhereparrelID='public.cxfa3'::regclass; count ------- 1 (1row)

只要count的结果是=1的，表示该表是分区表，否则该表不是分区表。每个分区表在里面只有一行记录。

5.2 查询一个表的分区键 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 查询分区表每个分区的具体信息 SELECTpp.parrelID::regclass,pr1.parchildrelID::regclass,pr1.parname,92); line-height:21px"> aligputf8-#CASE WHENpp.parkind='h'::"char"THEN'hash'::text WHENpp.parkind='r'::"char"THEN'range'::text WHENpp.parkind='l'::"char"THEN'List'::text ELSENulL::text ENDASpartitiontype,92); line-height:21px"> aligputf8-#pg_get_partition_rule_def(pr1.oID,153); background-color:inherit; Font-weight:bold">true)ASpartitionboundary FROMpg_partitionpp,pg_partition_rulepr1 WHEREpp.paristemplate=falseANDpp.parrelID='cxfa3'::regclassANDpr1.paroID=pp.oID bypr1.parname; parrelID|parchildrelID|parname|partitiontype|partitionboundary ----------+-----------------------+-----------+---------------+--------------------------------------------------------------------------------------------------- cxfa3|cxfa3_1_prt_p19860801|p19860801|range|PARTITIONp19860801START('1986-08-01'::date)EVERY('1day'::interval) cxfa3|cxfa3_1_prt_p19860802|p19860802|range|PARTITIONp19860802START('1986-08-02':: (2rows) 6.查询comment（备注信息）

comment信息是放在表pg_description中的。

名字	类型	引用	描述
objoID	oID	任意 oID 属性	这条描述所描述的对象的 OID
classoID	oID	pg_class.oID	这个对象出现的系统表的 OID
obJsubID	int4		对于一个表字段的注释，它是字段号(objoID 和 classoID 指向表自身)。对于其它对象类型，它是零。
description	text		作为对该对象的描述的任意文本

查询在表上的comment信息：

COALESCE(description,'')ascommentfrompg_descriptionwhereobjoID='cxfa'::regclassandobJsubID=0; comment -------------------------------------- atablecreatedbyscutshuxue.chenxf (1row)

查询表中字段的comment信息：

selectb.attnameascolumnname,COALESCE(a.description,153); background-color:inherit; Font-weight:bold">ascomment frompg_catalog.pg_descriptiona,pg_catalog.pg_attributeb whereobjoID='cxfa'::regclass anda.objoID=b.attrelID anda.obJsubID=b.attnum; columnname|comment ------------+------------------------ a|columnaoftablecxfa (1row) 7.查询权限信息

对于表、视图来说，在pg_class里面有一个字段relacl，保存了权限信息，如下：

selectrelaclwhererelname='cxfa3'; relacl -------------------------------------------------------- {gpadmin1=arwdxt/gpadmin1,role_aquery=arwdxt/gpadmin1} (1row)

具体解释如下：

=xxxx -- 赋予 PUBliC 的权限
uname=xxxx -- 赋予一个用户的权限
group gname=xxxx -- 赋予一个组的权限

r -- SELECT ("读")
w -- UPDATE ("写")
a -- INSERT ("追加")
d -- DELETE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdxt -- ALL PRIVILEGES (用于表)
* -- 给前面权限的授权选项

/yyyy -- 授出这个权限的用户

对于函数，在pg_proc里面也有一个对应的字段proacl。对于schema，pg_namespace里面也有对应的字段nspacl。

但是查这些字段有点不是很方便，在数据库里面有很多函数可以方便一些查询。如下：

aligputf8=#\df*privilege* ------------+--------------------------+------------------+--------------------- pg_catalog|has_database_privilege|boolean|name,text pg_catalog|has_database_privilege|boolean|pg_catalog|has_database_privilege|boolean|oID,text pg_catalog|has_database_privilege|boolean|oID,text pg_catalog|has_database_privilege|boolean|text,255); line-height:21px"> pg_catalog|has_function_privilege|boolean| pg_catalog|has_function_privilege|boolean| pg_catalog|has_function_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_function_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_function_privilege|boolean|text,255); line-height:21px"> pg_catalog|has_language_privilege|boolean| pg_catalog|has_language_privilege|boolean| pg_catalog|has_language_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_language_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_language_privilege|boolean|text,255); line-height:21px"> pg_catalog|has_schema_privilege|boolean| pg_catalog|has_schema_privilege|boolean| pg_catalog|has_schema_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_schema_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_schema_privilege|boolean|text,255); line-height:21px"> pg_catalog|has_table_privilege|boolean| pg_catalog|has_table_privilege|boolean| pg_catalog|has_table_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_table_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_table_privilege|boolean|text,255); line-height:21px"> pg_catalog|has_tablespace_privilege|boolean| pg_catalog|has_tablespace_privilege|boolean| pg_catalog|has_tablespace_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_tablespace_privilege|boolean|oID,92); line-height:21px"> pg_catalog|has_tablespace_privilege|boolean|text,255); line-height:21px"> (36rows)

示例：

查询role_aquery用户是否有访问public.cxfa3这个表的select权限。如果结果为't'则表示有这个权限，如果为'f'则没有权限。

selecthas_table_privilege('role_aquery','public.cxfa3','select'); has_table_privilege --------------------- t selecthas_table_privilege('role_dhw','select'); has_table_privilege --------------------- f (1row) 8.查询表的依赖关系

我们在drop一个表的时候，经常被提醒上面有视图，不能drop。例如：

droptablecxfa; NOTICE:rule_RETURNonvIEwv_cxfadepends NOTICE:vIEwv_cxfa ERROR:cannottablecxfabecauSEOtherobjectsdependonit HINT:UseDROP...CASCADEtodropthedependentobjectstoo.

那么数据库里面是怎么保存这些依赖关系的呢？答案就在pg_depend数据字典。下面就以视图

create vIEw v_cxfa as select * from cxfa;

为例，介绍依赖关系是如何工作的。

pg_depend的官方文档：http://www.pgsqldb.org/pgsqldoc-8.1c/catalog-pg-depend.html#AEN56970

frompg_dependwhererefobjID='cxfa'::regclass; classID|objID|obJsubID|refclassID|refobjID|refobJsubID|deptype ---------+--------+----------+------------+----------+-------------+--------- 1247|334931|0|1259|334930|0|i 2618|334941|0|1259|334930|1|n rows) selectrelnamewhereoIDin(1247,2618); relname ------------ pg_type pg_rewrite rows) selecttypnamefrompg_typewhereoID=334931; typname --------- cxfa selectrulename,ev_class::regclass,ev_classfrompg_rewritewhereoID=334941; rulename|ev_class|ev_class ----------+----------+---------- _RETURN|v_cxfa|334939 (1row)

上面说明了复合类型（每创建一个表，都会在pg_type里面创建一个复合类型，drop表的时候会默认drop掉的）cxfa是依赖于表cxfa的，还有pg_rewrite这个表里面oID=334941的记录是依赖于表cxfa的，这个记录是表示视图v_cxfa的重写规则的，所以我们可以通过这个找到依赖于表cxfa的视图。

我们可以下面的sql来查询依赖于表上面的视图，过滤掉复合类型及其他函数等。

由于pg_depend是没有记录数据字典的依赖关系的，所以我们如果要查询数据字典上面的视图，我们可以这么做：

selectev_class::regclassin( aligputf8(#selectb.objID aligputf8(#frompg_dependa,pg_dependb wherea.refclassID=1259 aligputf8(#andb.deptype='i' aligputf8(#anda.classID=2618 anda.objID=b.objID anda.classID=b.classID anda.refclassID=b.refclassID anda.refobjID<>b.refobjID anda.refobjID='cxfa'::regclass aligputf8(#); ev_class ---------- v_cxfa (1row)

查出pg_attribute的oID：

selectoIDwhererelname='pg_attribute'; oID ------ 1249 (1row)

加入一个视图，验证能否找到新加入的表：

aligputf8=# create vIEw v_cxf_attribute as select * from pg_attribute;

CREATE VIEW

然后用下面的sql查找出视图：

SELECTev_class::regclassFROMpg_catalog.pg_rewriteWHEREev_actionlike'%relID1249%'; ev_class -------------------------------------------- pg_stats pg_partition_columns information_schema.attributes information_schema.check_constraints information_schema.column_domain_usage information_schema.column_privileges information_schema.column_udt_usage information_schema.columns information_schema.constraint_column_usage information_schema.key_column_usage information_schema.role_column_grants information_schema.table_constraints information_schema.vIEw_column_usage information_schema.element_types v_cxf_attribute (15rows) 9.类型转换

在gp中，我们经常使用 cast函数，或者是::type进行类型转换，究竟哪两种类型之间是可以转换的，哪两种类型之间不能转换，转换的规则是什么。其实这些都在pg_cast里面中定义了。

名字	类型	引用	描述
castsource	oID	pg_type.oID	源数据类型的 OID
casttarget	oID	pg_type.oID	目标数据类型的 OID
castfunc	oID	pg_proc.oID	用于执行这个转换的函数的 OID 。如果该数据类型是二进制兼容的，那么为零(也就是说，不需要运行时的 *** 作来执行转换)。
castcontext	char		标识这个转换可以在什么环境里调用。e 表示只能进行明确的转换(使用 CAST 或 :: 语法)。a 表示在赋值给目标字段的时候隐含调用，也可以明确调用。i 表示在表达式中隐含，当然也包括其它情况。

我们想知道，text类型到date类型的转换是用了那个函数可以这么查：

selectcastfunc::regprocedurefrompg_castwherecastsource='text'::regtypeandcasttarget='date'::regtype; castfunc date(text) select'20110302'::date; 2011-03-02 selectdate('20110302'); (1row)

可以看出，cast('20110302' as date)跟'20110302'::date其实都是调用了date('20110302')函数进行类型转换了。

我们是否可以自定义类型转换呢？答案是肯定的。

比方说，上面的regclass类型是没有到text类型的转换的：

select1259::regclass::text; ERROR:cannotcasttyperegclasstotext liNE1:select1259::regclass::text;

我们先创建一个类型转换函数：

CREATEorreplaceFUNCTIONregclass2text(aregclass) RETURNStext AS$ returna; $LANGUAGEplpythonu;

然后定义一个cast类型转换规则。

createcast(regclassastext)withfunctionregclass2text(aregclass); CREATECAST

这样子我们就定义好了一个类型转换，验证：

text cast(1259::regclassastext); (1row) 总结

以上是内存溢出为你收集整理的greenplum(postgresql)之数据字典全部内容，希望文章能够帮你解决greenplum(postgresql)之数据字典所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。