greenplum数据字典.docx

greenplum是基于postgresql开发的分布式数据库，里面大部分的数据字典是一样的。我们在维护gp的时候对gp的数据字典比较熟悉，特此分享给大家。在这里不会详细介绍每个字典的内容，只会介绍常见的应用以及一些已经封装好了的函数。具体的介绍大家可以去看postgresql的中文文档，里面有详细的解释。 1.postgresql中，所有数据库的对象都是用oid连接在一起的。 这样子会造成我们在理解数据字典的时候有一些不知所措。下面介绍几个函数，可以简化很多的操作。名字引用描述regprocpg_proc函数名字regprocedurepg_proc带参数类型的函数regoperpg_operator操作符名regoperatorpg_operator带参数类型的操作符regclasspg_class关系名 最常用的是regclass,关联数据字典的oid，使用方法如下：aligputf8=# select 1259:regclass;regclass-pg_class(1 row)aligputf8=# select oid,relname from pg_class where oid=pg_class:regclass;oid | relname+-1259 | pg_class(1 row) 这样子就可以通过regclass寻找一个表的信息，就不用去关联 pg_class跟pg_namespace（记录schema信息）了。比较方便。 同样的，其他几个类型也是一样的用法，如regproc（regprocedure）是跟pg_proc（保存普通函数的命令）关联的。regoper(regoperator)是跟pg_operator（操作符）的oid关联的。eg:aligputf8=# select oid:regoper,oid:regoperator,oid,oprname from pg_operator limit 1; oid | oid | oid | oprname+-+pg_catalog.= | =(integer,bigint) | 15 | =(1 row)aligputf8=# select oid:regproc,oid:regprocedure,oid,proname from pg_proc limit 1; oid | oid | oid | proname+boolin | boolin(cstring) | 1242 | boolin(1 row) 下面给给出如何使用regclass的例子。2.获取表的字段信息。 表名是放在pg_class，schema名是放在pg_namespace里面的，字段信息是放在pg_attribute里面的。一般是关联这三张表： eg:select a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) as data_typefrom 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 )bwhere a.attrelid = b.oidand a.attnum 0and not a.attisdropped order by a.attnum; 如果使用regclass就会简化很多：select a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod) as data_typefrom pg_catalog.pg_attribute awhere a.attrelid =pg_catalog.pg_class:regclassand a.attnum 0and not a.attisdropped order by a.attnum; 其实regclass就是一个类型，oid或text到regclass有一个类型转换，跟多表关联不一样，多数据字典表关联的话，如果表不存在，会返回空记录，不会报错，而如果采用regclass则会报错，所以在不确定表是否存在的情况下，慎用regclass。3.获取表的分布键：gp_distribution_policy记录这表的数据字典，localoid跟pg_class的oid关联。attrnums是一个数组，记录字段的attnum，跟pg_attribute里面的attnum关联的。aligputf8=# create table cxfa2 ( a int ,b int ,c int ,d int ) distributed by (c,a);aligputf8=# select * from gp_distribution_policy where localoid=cxfa2:regclass ;localoid | attrnums-+- 334868 | 3,1(1 row) select a.attrnumsi.i,b.attname,a.localoid:regclass from gp_distribution_policy a, (select generate_series(1,10)i(i), pg_attribute b where a.attrnumsi.i is not null and a.localoid=b.attrelid and a.attrnumsi.i=b.attnum and a.localoid=public.cxfa2:regclass order by i.i;结果如下：attrnums | attname | localoid-+- 3 | c | cxfa2 1 | a | cxfa2(2 rows)4.获取一个视图的定义。aligputf8=# df pg_get_viewdef list of functions schema | name | result data type | argument data types+-+pg_catalog | pg_get_viewdef | text | oidpg_catalog | pg_get_viewdef | text | oid, booleanpg_catalog | pg_get_viewdef | text | textpg_catalog | pg_get_viewdef | text | text, boolean(4 rows)使用这个系统函数可以获取视图的定义，可以传入oid或者是表名，第二个参数表示是否格式化输出，默认不格式化输出。aligputf8=# create table cxfa( a int) distributed by (a);create tablealigputf8=# create view v_cxfa as select * from cxfa;create viewaligputf8=# select pg_get_viewdef(v_cxfa,true);pg_get_viewdef- select cxfa.a from cxfa;(1 row)其实这个函数是去获取数据字典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 :intooidiptypeoid 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=# df pg_get_*def list of functions schema | name | result data type | argument data types+pg_catalog | pg_get_constraintdef | text | oidpg_catalog | pg_get_constraintdef | text | oid, booleanpg_catalog | pg_get_indexdef | text | oidpg_catalog | pg_get_indexdef | text | oid, integer, booleanpg_catalog | pg_get_partition_def | text | oidpg_catalog | pg_get_partition_def | text | oid, booleanpg_catalog | pg_get_partition_rule_def | text | oidpg_catalog | pg_get_partition_rule_def | text | oid, booleanpg_catalog | pg_get_ruledef | text | oidpg_catalog | pg_get_ruledef | text | oid, booleanpg_catalog | pg_get_triggerdef | text | oid注：触发器在greenplum里面是不支持的。5.分区表相关操作d一个表是看不出一个表是否是分区表的，所以必须查询数据字典才能知道。查分区表可以通过pg_partitions跟pg_partition_columns这两个视图来查询，但是这两个视图的结构非常复杂，在线上gp上数据字典都非常大，没有充分利用到索引，这个查询起来效率太低了。我们直接通过数据字典的实体表来查询的话，会快很多。首先创建一个分区表：create table public.cxfa3( id integer ,name character varying(20) ,birth date)distributed by (id)partition by range(birth)( partition p19860801 start (1986-08-01:date) end (1986-08-02:date) every (1 day:interval), partition p19860802 start (1986-08-02:date) end (1986-08-03:date) every (1 day:interval); 5.1 查询一个表是否是分区表表pg_partition：每一个分区表的父表有一行记录。parkind： 表示分区类型（range 或者 list）。parnatts： 分区建个数。paratts： 分区键，跟pg_attribute关联，是一个列表。表pg_partition_rule：保存分区表每一个子分区的分区名以及分区规则等。aligputf8=# select count(*) from pg_partition where parrelid=public.cxfa3:regclass;count- 1(1 row)只要count的结果是=1的，表示该表是分区表，否则该表不是分区表。每个分区表在里面只有一行记录。 5.2 查询一个表的分区键aligputf8=# select attname as columnnamealigputf8-# from pg_attribute a,pg_partition baligputf8-# where a.attnum = b.paratts0aligputf8-# and b.parrelid = a.attrelidaligputf8-# and a.attrelid=public.cxfa3:regclass;columnnamebirth(1 row)由于现在gp上面的分区键都是一个的，所以为了效率，我们也只是获取第一个分区键 5.3 查询分区表每个分区的具体信息aligputf8=# select pp.parrelid:regclass,pr1.parchildrelid:regclass,pr1.parname,aligputf8-# casealigputf8-# when pp.parkind = h:”char” then hash:textaligputf8-# when pp.parkind = r:”char” then range:textaligputf8-# when pp.parkind = l:”char” then list:textaligputf8-# else null:textaligputf8-# end as partitiontype,aligputf8-# pg_get_partition_rule_def(pr1.oid, true) as partitionboundaryaligputf8-# from pg_partition pp, pg_partition_rule pr1aligputf8-# where pp.paristemplate = false and pp.parrelid = cxfa3:regclass and pr1.paroid = pp.oidaligputf8-# order by pr1.parname; parrelid | parchildrelid | parname | partitiontype | partitionboundary-+cxfa3 | cxfa3_1_prt_p19860801 | p19860801 | range | partition p19860801 start (1986-08-01:date) end (1986-08-02:date) every (1 day:interval)cxfa3 | cxfa3_1_prt_p19860802 | p19860802 | range | partition p19860802 start (1986-08-02:date) end (1986-08-03:date) every (1 day:interval)(2 rows)6.查询comment（备注信息）comment信息是放在表pg_description中的。名字类型引用描述objoidoid任意 oid 属性这条描述所描述的对象的 oidclassoidoidpg_class.oid这个对象出现的系统表的 oidobjsubidint4对于一个表字段的注释，它是字段号(objoid 和 classoid 指向表自身)。对于其它对象类型，它是零。descriptiontext作为对该对象的描述的任意文本 查询在表上的comment信息：aligputf8=# select coalesce(description,”) as comment from pg_description where objoid=cxfa:regclass and objsubid=0; commenta table created by scutshuxue.chenxf(1 row) 查询表中字段的comment信息：aligputf8=# select b.attname as columnname, coalesce(a.description,”) as commentaligputf8-# from pg_catalog.pg_description a,pg_catalog.pg_attribute baligputf8-# where objoid=cxfa:regclassaligputf8-# and a.objoid=b.attrelidaligputf8-# and a.objsubid=b.attnum;columnname | comment+a | column a of table cxfa(1 row)7.查询权限信息对于表、视图来说，在pg_class里面有一个字段relacl，保存了权限信息，如下：aligputf8=# select relacl from pg_class where relname=cxfa3; relaclgpadmin1=arwdxt/gpadmin1,role_aquery=arwdxt/gpadmin1(1 row)具体解释如下： =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* list of functions schema | name | result data type | argument data types+pg_catalog | has_database_privilege | boolean | name, oid, textpg_catalog | has_database_privilege | boolean | name, text, textpg_catalog | has_database_privilege | boolean | oid, oid, textpg_catalog | has_database_privilege | boolean | oid, textpg_catalog | has_database_privilege | boolean | oid, text, textpg_catalog | has_database_privilege | boolean | text, textpg_catalog | has_function_privilege | boolean | name, oid, textpg_catalog | has_function_privilege | boolean | name, text, textpg_catalog | has_function_privilege | boolean | oid, oid, textpg_catalog | has_function_privilege | boolean | oid, textpg_catalog | has_function_privilege | boolean | oid, text, textpg_catalog | has_function_privilege | boolean | text, textpg_catalog | has_language_privilege | boolean | name, oid, textpg_catalog | has_language_privilege | boolean | name, text, textpg_catalog | has_language_privilege | boolean | oid, oid, textpg_catalog | has_language_privilege | boolean | oid, textpg_catalog | has_language_privilege | boolean | oid, text, textpg_catalog | has_language_privilege | boolean | text, textpg_catalog | has_schema_privilege | boolean | name, oid, textpg_catalog | has_schema_privilege | boolean | name, text, textpg_catalog | has_schema_privilege | boolean | oid, oid, textpg_catalog | has_schema_privilege | boolean | oid, textpg_catalog | has_schema_privilege | boolean | oid, text, textpg_catalog | has_schema_privilege | boolean | text, textpg_catalog | has_table_privilege | boolean | name, oid, textpg_catalog | has_table_privilege | boolean | name, text, textpg_catalog | has_table_privilege | boolean | oid, oid, textpg_catalog | has_table_privilege | boolean | oid, textpg_catalog | has_table_privilege | boolean | oid, text, textpg_catalog | has_table_privilege | boolean | text, textpg_catalog | has_tablespace_privilege | boolean | name, oid, textpg_catalog | has_tablespace_privilege | boolean | name, text, textpg_catalog | has_tablespace_privilege | boolean | oid, oid, textpg_catalog | has_tablespace_privilege | boolean | oid, textpg_catalog | has_tablespace_privilege | boolean | oid, text, textpg_catalog | has_tablespace_privilege | boolean | text, text(36 rows)示例：查询role_aquery用户是否有访问public.cxfa3这个表的select权限。如果结果为t则表示有这个权限，如果为f则没有权限。aligputf8=# select has_table_privilege(role_aquery,public.cxfa3,select);has_table_privileget(1 row)aligputf8=# select has_table_privilege(role_dhw,public.cxfa3,select);has_table_privilegef(1 row)8.查询表的依赖关系 我们在drop一个表的时候，经常被提醒上面有视图，不能drop。例如：aligputf8=# drop table cxfa;notice: rule _return on view v_cxfa depends on table cxfanotice: view v_cxfa depends on rule _return on view v_cxfaerror: cannot drop table cxfa because other objects depend on ithint: use drop cascade to drop the dependent objects too. 那么数据库里面是怎么保存这些依赖关系的呢？答案就在pg_depend数据字典。下面就以视图 create view v_cxfa as select * from cxfa; 为例，介绍依赖关系是如何工作的。 pg_depend的官方文档：/pgsqldoc-8.1c/catalog-pg-depend.html#aen56970aligputf8=# select * from pg_depend where refobjid=cxfa:regclass;classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype+-+-+-+ 1247 | 334931 | 0 | 1259 | 334930 | 0 | i 2618 | 334941 | 0 | 1259 | 334930 | 1 | n(2 rows)aligputf8=# select relname from pg_class where oid in (1247,2618); relnamepg_typepg_rewrite(2 rows)aligputf8=# select typname from pg_type where oid=334931;typnamecxfa(1 row)aligputf8=# select rulename,ev_class:regclass,ev_class from pg_rewrite where oid=334941;rulename | ev_class | ev_class-+-+-_return | v_cxfa | 334939(1 row)上面说明了复合类型（每创建一个表，都会在pg_type里面创建一个复合类型，drop表的时候会默认drop掉的）cxfa是依赖于表cxfa的，还有pg_rewrite这个表里面oid=334941的记录是依赖于表cxfa的，这个记录是表示视图v_cxfa的重写规则的，所以我们可以通过这个找到依赖于表cxfa的视图。我们可以下面的sql来查询依赖于表上面的视图，过滤掉复合类型及其他函数等。aligputf8=# select ev_class:regclass from pg_rewrite where oid in (aligputf8(# select b.objidaligputf8(# from pg_depend a,pg_depend baligputf8(# where a.refclassid=1259aligputf8(# and b.deptype=ialigputf8(# and a.classid=2618aligputf8(# and a.objid=b.objidaligputf8(# and a.classid=b.classidaligputf8(# and a.refclassid=b.refclassidaligputf8(# and a.refobjidb.refobjidaligputf8(# and a.refobjid=cxfa:regclassaligputf8(# );ev_class-v_cxfa(1 row)由于pg_depend是没有记录数据字典的依赖关系的，所以我们如果要查询数据字典上面的视图，我们可以这么做：查出pg_attribute的oid：aligputf8=# select oid from pg_class where relname=pg_attribute;oid1249(1 row)加入一个视图，验证能否找到新加入的表：aligputf8=# create view v_cxf_attribute as select * from pg_attribute;create view然后用下面的sql查找出视图：aligputf8=# select ev_class:regclass from pg_catalog.pg_rewrite where ev_action like %relid 1249%; ev_classpg_statspg_partition_columnsinformation_schema.attributesinformation_schema.check_constraintsinformation_schema.column_domain_usageinformation_schema.column_privilegesinformation_schema.column_udt_usageinformation