greenplum(postgresql)之数据字典

前端之家收集整理的这篇文章主要介绍了greenplum(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,使用方法如下:

  1. aligputf8=#select1259::regclass;
  2. regclass
  3. ----------
  4. pg_class
  5. (1row)
  6. aligputf8=#selectoid,relnamefrompg_classwhereoid='pg_class'::regclass;
  7. oid|relname
  8. ------+----------
  9. 1259|pg_class
  10. (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)
  • 猜你在找的Postgre SQL相关文章