GP6数据字典中带隐藏字段oid的所有表，这些表的oid增加都是共享一个序列，当然，还有其他的表也是共享这些序列，比如pg_class的relfilenode
查询语句：
postgres=# select attrelid::regclass,attname
from pg_attribute a ,pg_class b
where a.attrelid=b.oid
and b.relnamespace=11
and atttypid=26
and b.relstorage='h'
and attname='oid'
and b.relname not like '%index';
attrelid | attname
-------------------------+---------
pg_proc | oid
pg_type | oid
pg_class | oid
pg_attrdef | oid
pg_constraint | oid
pg_operator | oid
pg_opfamily | oid
pg_opclass | oid
pg_am | oid
pg_amop | oid
pg_amproc | oid
pg_language | oid
pg_largeobject_metadata | oid
pg_rewrite | oid
pg_trigger | oid
pg_event_trigger | oid
pg_cast | oid
pg_enum | oid
pg_namespace | oid
pg_conversion | oid
pg_database | oid
pg_tablespace | oid
pg_authid | oid
pg_ts_config | oid
pg_ts_dict | oid
pg_ts_parser | oid
pg_ts_template | oid
pg_extension | oid
pg_foreign_data_wrapper | oid
pg_foreign_server | oid
pg_user_mapping | oid
pg_default_acl | oid
pg_collation | oid
pg_resqueue | oid
pg_resqueuecapability | oid
pg_resourcetype | oid
pg_resgroup | oid
pg_resgroupcapability | oid
pg_extprotocol | oid
pg_partition | oid
pg_partition_rule | oid
pg_compression | oid
(42 rows)
postgres=#
postgres=# select * from pg_attribute a where atttypid=26 limit 1;
-[ RECORD 1 ]-+-------------
attrelid | 1255
attname | pronamespace
atttypid | 26
attstattarget | -1
attlen | 4
attnum | 2
attndims | 0
attcacheoff | -1
atttypmod | -1
attbyval | t
attstorage | p
attalign | i
attnotnull | t
atthasdef | f
attisdropped | f
attislocal | t
attinhcount | 0
attcollation | 0
attacl |
attoptions |
attfdwoptions |
postgres=# \x
Expanded display is on.
postgres=# select * from pg_class a where relnamespace=11 limit 1;
-[ RECORD 1 ]--+--------------------------------
relname | pg_attribute_relid_attnam_index
relnamespace | 11
reltype | 0
reloftype | 0
relowner | 10
relam | 403
relfilenode | 0
reltablespace | 0
relpages | 8
reltuples | 3367
relallvisible | 0
reltoastrelid | 0
relhasindex | f
relisshared | f
relpersistence | p
relkind | i
relstorage | h
relnatts | 2
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relispopulated | t
relreplident | n
relfrozenxid | 0
relminmxid | 0
relacl |
reloptions |
如何识别对象
1、识别临时表、UNLOGGED TABLE、临时表
select relname from pg_class where relpersistence=? and relkind='r';
pg_class 的relpersistence用于识别表是什么表(正常表、不记日志表、临时表)。 relkind用于识别是什么对象类别(表、索引、序列、切片、视图、物化视图、复合类型、外部表、分区表)。
relpersistence
p = permanent table, u = unlogged table, t = temporary table
relkind
r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table
Greenplum 扩展
pg_class.relstorage 用于区分是什么存储
h = 堆表(heap)
a = append only row存储表
c = append only column存储表
存储过程
pg_proc
数据库
pg_database
表空间
pg_tablespace
schema
pg_namespace
用户
pg_roles
索引接口
pg_am
如何获取对象定义
使用这些函数接口，可以获得对应对象的定义。
pg_get_indexdef
pg_get_functiondef
pg_get_triggerdef
pg_get_ruledef
pg_get_viewdef
pg_get_constraintdef
postgres=# select * from pg_get_indexdef('idx_tbl2_1'::regclass);
pg_get_indexdef
--------------------------------------------------
CREATE INDEX idx_tbl2_1 ON tbl2 USING btree (id)
(1 row)
将oid 转换成名称的集中类型
| 名字 | 引用 | 描述 |
| regproc | pg_proc | 函数名 |
| regprocedure | | 带参数类型的函数 |
| regoper | pg_operator | 操作符名 |
| regoperator | | 带参数类型操作符 |
| regclass | pg_class | 关系名 |
举例子
postgres=# select 1259::regclass;
regclass
----------
pg_class
(1 row)
postgres=# select oid,relname from pg_class where oid='pg_class'::regclass;
oid | relname
------+----------
1259 | pg_class
(1 row)
postgres=# select oid::regoper,oid::regoperator,oid,oprname from pg_operator limit 1;
oid | oid | oid | oprname
--------------+-------------------+-----+---------
pg_catalog.= | =(integer,bigint) | 15 | =
(1 row)
postgres=# select oid:: regoper,oid::regoperator,oid,oprname from pg_operator limit 1;
oid | oid | oid | oprname
--------------+-------------------+-----+---------
pg_catalog.= | =(integer,bigint) | 15 | =
(1 row)
