greenplum(postgresql) 数据字典

1.postgresql中，所有数据库的对象都是用oid连接在一起的。

这样子会造成我们在理解数据字典的时候有一些不知所云。下面介绍几个函数，可以简化很多的操作。

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关联的。

[sql] view plain copy

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)

SELECT a.attname,pg_catalog.format_type(a.atttypid, 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‘

WHERE a.attrelid = b.oid

AND a.attnum > 0

AND NOT a.attisdropped ORDER BY a.attnum;

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;

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

3.获取表的分布键：

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

[sql] view plain copy

ligputf8=# select * from gp_distribution_policy where localoid= ‘cxfa2‘::regclass ;

localoid | attrnums

---------+----------

334868 | {3,1}

1 row)

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;

aligputf8=# \df pg_get_viewdef

List of functions

Schema | Name | Result data type | Argument data types

------------+----------------+------------------+---------------------

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

(4 rows)

aligputf8=# create table cxfa( a int) distributed by (a);

CREATE TABLE

aligputf8=# create view v_cxfa as select * from cxfa;

CREATE VIEW

aligputf8=# 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 :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：

[sql] view plain copy

aligputf8=# \df pg_get_*def

List of functions

Schema | Name | Result data type | Argument data types

------------+---------------------------+------------------+-----------------------

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上数据字典都非常大，没有充分利用到索引，这个查询起来效率太低了。我们直接通过数据字典的实体表来查询的话，会快很多。

首先创建一个分区表：

[sql] view plain copy

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)

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

parnatts：分区建个数。

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

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

[sql] view plain copy

aligputf8=# select count(*) from pg_partition where parrelid= ‘public.cxfa3‘::regclass;

count

-------

(1 row)

aligputf8=# select attname as columnname

aligputf8-# from pg_attribute a,pg_partition b

aligputf8-# where a.attnum = b.paratts[0]

aligputf8-# and b.parrelid = a.attrelid

aligputf8-# and a.attrelid= ‘public.cxfa3‘::regclass;

columnname

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

birth

(1 row)

aligputf8=# SELECT pp.parrelid::regclass,pr1.parchildrelid::regclass,pr1.parname,

aligputf8-# CASE

aligputf8-# WHEN pp.parkind = ‘h‘:: "char" THEN ‘hash‘::text

aligputf8-# WHEN pp.parkind = ‘r‘:: "char" THEN ‘range‘::text

aligputf8-# WHEN pp.parkind = ‘l‘:: "char" THEN ‘list‘::text

aligputf8-# ELSE NULL::text

aligputf8-# END AS partitiontype,

aligputf8-# pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary

aligputf8-# FROM pg_partition pp, pg_partition_rule pr1

aligputf8-# WHERE pp.paristemplate = false AND pp.parrelid = ‘cxfa3‘::regclass AND pr1.paroid = pp.oid

aligputf8-# 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)

aligputf8=# select COALESCE(description, ‘‘) as comment from pg_description where objoid= ‘cxfa‘::regclass and objsubid=0;

comment

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

a table created by scutshuxue.chenxf

(1 row)

aligputf8=# select b.attname as columnname, COALESCE(a.description, ‘‘) as comment

aligputf8-# from pg_catalog.pg_description a,pg_catalog.pg_attribute b

aligputf8-# where objoid= ‘cxfa‘::regclass

aligputf8-# and a.objoid=b.attrelid

aligputf8-# and a.objsubid=b.attnum;

columnname | comment

------------+------------------------

a | column a of table cxfa

(1 row)

aligputf8=# select relacl from pg_class where relname= ‘cxfa3‘;

relacl

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

{gpadmin1=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。

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

[sql] view plain copy

aligputf8=# \df *privilege*

List of functions

Schema | Name | Result data type | Argument data types

------------+--------------------------+------------------+---------------------

pg_catalog | has_database_privilege | boolean | name, oid, text

pg_catalog | has_database_privilege | boolean | name, text, text

pg_catalog | has_database_privilege | boolean | oid, oid, text

pg_catalog | has_database_privilege | boolean | oid, text

pg_catalog | has_database_privilege | boolean | oid, text, text

pg_catalog | has_database_privilege | boolean | text, text

pg_catalog | has_function_privilege | boolean | name, oid, text

pg_catalog | has_function_privilege | boolean | name, text, text

pg_catalog | has_function_privilege | boolean | oid, oid, text

pg_catalog | has_function_privilege | boolean | oid, text

pg_catalog | has_function_privilege | boolean | oid, text, text

pg_catalog | has_function_privilege | boolean | text, text

pg_catalog | has_language_privilege | boolean | name, oid, text

pg_catalog | has_language_privilege | boolean | name, text, text

pg_catalog | has_language_privilege | boolean | oid, oid, text

pg_catalog | has_language_privilege | boolean | oid, text

pg_catalog | has_language_privilege | boolean | oid, text, text

pg_catalog | has_language_privilege | boolean | text, text

pg_catalog | has_schema_privilege | boolean | name, oid, text

pg_catalog | has_schema_privilege | boolean | name, text, text

pg_catalog | has_schema_privilege | boolean | oid, oid, text

pg_catalog | has_schema_privilege | boolean | oid, text

pg_catalog | has_schema_privilege | boolean | oid, text, text

pg_catalog | has_schema_privilege | boolean | text, text

pg_catalog | has_table_privilege | boolean | name, oid, text

pg_catalog | has_table_privilege | boolean | name, text, text

pg_catalog | has_table_privilege | boolean | oid, oid, text

pg_catalog | has_table_privilege | boolean | oid, text

pg_catalog | has_table_privilege | boolean | oid, text, text

pg_catalog | has_table_privilege | boolean | text, text

pg_catalog | has_tablespace_privilege | boolean | name, oid, text

pg_catalog | has_tablespace_privilege | boolean | name, text, text

pg_catalog | has_tablespace_privilege | boolean | oid, oid, text

pg_catalog | has_tablespace_privilege | boolean | oid, text

pg_catalog | has_tablespace_privilege | boolean | oid, text, text

pg_catalog | has_tablespace_privilege | boolean | text, text

(36 rows)

aligputf8=# select has_table_privilege( ‘role_aquery‘, ‘public.cxfa3‘, ‘select‘);

has_table_privilege

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

(1 row)

aligputf8=# select has_table_privilege( ‘role_dhw‘, ‘public.cxfa3‘, ‘select‘);

has_table_privilege

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

(1 row)

aligputf8=# drop table cxfa;

NOTICE: rule _RETURN on view v_cxfa depends on table cxfa

NOTICE: view v_cxfa depends on rule _RETURN on view v_cxfa

ERROR: cannot drop table cxfa because other objects depend on it

HINT: Use DROP ... CASCADE to drop the dependent objects too.

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

create view v_cxfa as select * from cxfa;

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

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

[sql] view plain copy

aligputf8=# select * from pg_depend where refobjid= ‘cxfa‘::regclass;

---------+--------+----------+------------+----------+-------------+---------

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);

relname

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

pg_type

pg_rewrite

(2 rows)

aligputf8=# select typname from pg_type where oid=334931;

typname

---------

(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来查询依赖于表上面的视图，过滤掉复合类型及其他函数等。

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

[sql] view plain copy

aligputf8=# select ev_class::regclass from pg_rewrite where oid in (

aligputf8(# select b.objid

aligputf8(# from pg_depend a,pg_depend b

aligputf8(# where a.refclassid=1259

aligputf8(# and b.deptype= ‘i‘

aligputf8(# and a.classid=2618

aligputf8(# and a.objid=b.objid

aligputf8(# and a.classid=b.classid

aligputf8(# and a.refclassid=b.refclassid

aligputf8(# and a.refobjid<>b.refobjid

aligputf8(# and a.refobjid= ‘cxfa‘::regclass

aligputf8(# );

ev_class

----------

v_cxfa

(1 row)

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

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

CREATE VIEW

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

[sql] view plain copy

aligputf8=# SELECT ev_class::regclass FROM pg_catalog.pg_rewrite WHERE ev_action like ‘%relid 1249%‘;

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

(15 rows)

aligputf8=# select castfunc::regprocedure from pg_cast where castsource= ‘text‘::regtype and casttarget= ‘date‘::regtype;

castfunc

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

date(text)

(1 row)

aligputf8=# select ‘20110302‘:: date;

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

2011-03-02

(1 row)

aligputf8=# select date( ‘20110302‘);

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

2011-03-02

(1 row)

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

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

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

[sql] view plain copy

[转]greenplum(postgresql)之数据字典

标签： .net ted having context sts 取数据 trigger net rand

原文地址：http://www.cnblogs.com/jianyungsun/p/7168222.html