四探 Postgresql Catalog
-----

今天透過建立一個新的 Table 來看 pg_catalog 會發生什麼變化.

create table t190920 (
  testid serial primary key
);

commit;

先來查看 pg_class 中關於此 table 的一些基本資料
補充說明一下,在中文版的文件中
https://docs.postgresql.tw/internals/system-catalogs/pg_class
裡面有 relhaspkey 代表是否有 primary key,現在已經移除了.
https://www.postgresql.org/docs/current/catalog-pg-class.html
有興趣可以查詢網路,會有相關討論,在此就不展開了.

select relname
     , relnamespace::regnamespace
     , pg_catalog.pg_get_userbyid(relowner) as "Owner"
     , relkind
     , relnatts
     , reltuples
     , relhasindex
  from pg_catalog.pg_class
 where oid = 'miku.t190920'::regclass;
 
+---------+--------------+-------+---------+----------+-----------+-------------+
| relname | relnamespace | Owner | relkind | relnatts | reltuples | relhasindex |
+---------+--------------+-------+---------+----------+-----------+-------------+
| t190920 | miku         | miku  | r       |        1 |         0 | t           |
+---------+--------------+-------+---------+----------+-----------+-------------+
(1 row)

可以觀察到在 schema miku 中, table 名稱是 t190920, 屬於一般 table (r: ordinary table)
Owner 是 miku,有一個欄位, 沒有資料(tuples為0),有 index.

接下來看這 pg_type 查詢的結果

select typtype
     , typcategory
     , typarray::regtype
  from pg_catalog.pg_type
 where typrelid = 'miku.t190920'::regclass;

+---------+-------------+-----------+
| typtype | typcategory | typarray  |
+---------+-------------+-----------+
| c       | C           | t190920[] |
+---------+-------------+-----------+

typtype = c 代表是 綜合型態,像是 table 的欄位.
typcategory = C 代表是 綜合型態類別
請參考
https://www.postgresql.org/docs/current/catalog-pg-type.html
Table 52.63. typcategory Codes

typarray 是 t190920[]
接著再來查看

select typname
     , typnamespace::regnamespace
     , typtype
     , typcategory
     , typelem::regtype
  from pg_catalog.pg_type
 where oid = 't190920[]'::regtype;

+----------+--------------+---------+-------------+---------+
| typname  | typnamespace | typtype | typcategory | typelem |
+----------+--------------+---------+-------------+---------+
| _t190920 | miku         | b       | A           | t190920 |
+----------+--------------+---------+-------------+---------+

typtype = b 代表 basetype
typcategory = A 代表 Array types 類別

接著來看 table 中有哪些欄位

select attname
     , atttypid::regtype
     , attnum
     , attnotnull
     , atthasdef
     , attisdropped
  from pg_catalog.pg_attribute
 where attrelid = 'miku.t190920'::regclass
 order by attnum desc;

+----------+----------+--------+------------+-----------+--------------+
| attname  | atttypid | attnum | attnotnull | atthasdef | attisdropped |
+----------+----------+--------+------------+-----------+--------------+
| testid   | integer  |      1 | t          | t         | f            |
| ctid     | tid      |     -1 | t          | f         | f            |
| xmin     | xid      |     -3 | t          | f         | f            |
| cmin     | cid      |     -4 | t          | f         | f            |
| xmax     | xid      |     -5 | t          | f         | f            |
| cmax     | cid      |     -6 | t          | f         | f            |
| tableoid | oid      |     -7 | t          | f         | f            |
+----------+----------+--------+------------+-----------+--------------+
(7 rows)

可以看到有一些系統隱藏欄位,在上面查詢出來的 relnatts = 1 ,就是不包含系統隱藏欄位.

atthasdef = t (True) 代表此欄位有設定 default value

接下來查 default value

select adrelid::regclass
     , adnum
     , pg_catalog.pg_get_expr(adbin, adrelid)
  from pg_catalog.pg_attrdef
 where adrelid = 'miku.t190920'::regclass;
 
+---------+-------+-----------------------------------------+
| adrelid | adnum |               pg_get_expr               |
+---------+-------+-----------------------------------------+
| t190920 |     1 | nextval('t190920_testid_seq'::regclass) |
+---------+-------+-----------------------------------------+

我們在 create table 時使用了 serial ,這會建立一個 integer 欄位,
設定為 not null, 會有 default value, 另外建立一個 sequence 物件,
供 default 使用. 系統自動命名為 t190920_testid_seq

接下來查 sequence

select seqrelid::regclass
     , seqtypid::regtype
     , seqstart
     , seqincrement
     , seqmax
     , seqcache
     , seqcycle
  from pg_catalog.pg_sequence
 where seqrelid = 't190920_testid_seq'::regclass;

+--------------------+----------+----------+--------------+------------+----------+----------+
|      seqrelid      | seqtypid | seqstart | seqincrement |   seqmax   | seqcache | seqcycle |
+--------------------+----------+----------+--------------+------------+----------+----------+
| t190920_testid_seq | integer  |        1 |            1 | 2147483647 |        1 | f        |
+--------------------+----------+----------+--------------+------------+----------+----------+

這些欄位的名稱很明顯,在此不浪費篇幅一一解釋.
pg_sequence 是在 Postgresql 10 以後出現.

Sequence 物件既然是個物件,我們當然透過 pg_class 來查看.

select relname
     , reltype::regtype
     , relkind
     , relnatts
  from pg_catalog.pg_class
 where oid = 't190920_testid_seq'::regclass;

+--------------------+--------------------+---------+----------+
|      relname       |      reltype       | relkind | relnatts |
+--------------------+--------------------+---------+----------+
| t190920_testid_seq | t190920_testid_seq | S       |        3 |
+--------------------+--------------------+---------+----------+

可以看到 kind 是 S 代表 Sequence
relnatts 代表定義了三個欄位

接著繼續追查. 與 table 類似先來看 pg_type

select typtype
     , typcategory
  from pg_catalog.pg_type
 where typrelid = 't190920_testid_seq'::regclass;

+---------+-------------+
| typtype | typcategory |
+---------+-------------+
| c       | C           |
+---------+-------------+

一樣是 綜合型態 與 綜合型態類別

select attname
     , atttypid::regtype
     , attnum
     , attnotnull
     , atthasdef
     , attisdropped
  from pg_catalog.pg_attribute
 where attrelid = 't190920_testid_seq'::regclass
 order by attnum desc;

+------------+----------+--------+------------+-----------+--------------+
|  attname   | atttypid | attnum | attnotnull | atthasdef | attisdropped |
+------------+----------+--------+------------+-----------+--------------+
| is_called  | boolean  |      3 | t          | f         | f            |
| log_cnt    | bigint   |      2 | t          | f         | f            |
| last_value | bigint   |      1 | t          | f         | f            |
| ctid       | tid      |     -1 | t          | f         | f            |
| xmin       | xid      |     -3 | t          | f         | f            |
| cmin       | cid      |     -4 | t          | f         | f            |
| xmax       | xid      |     -5 | t          | f         | f            |
| cmax       | cid      |     -6 | t          | f         | f            |
| tableoid   | oid      |     -7 | t          | f         | f            |
+------------+----------+--------+------------+-----------+--------------+
(9 rows)

可以觀察到 is_called , log_cnt, last_value, 還有隱藏的 tableoid

接著來看 index

select indexrelid::regclass
     , indnatts
     , indisunique
     , indisprimary
  from pg_catalog.pg_index
 where indrelid = 't190920'::regclass;
 
+--------------+----------+-------------+--------------+
|  indexrelid  | indnatts | indisunique | indisprimary |
+--------------+----------+-------------+--------------+
| t190920_pkey |        1 | t           | t            |
+--------------+----------+-------------+--------------+

包含一個欄位,是 unique , 是 primary key

再查看 pg_class 關於此 index 的資訊

select relname
     , reltype::regtype
     , relkind
     , relnatts
  from pg_catalog.pg_class
 where oid = 't190920_pkey'::regclass;

+--------------+---------+---------+----------+
|   relname    | reltype | relkind | relnatts |
+--------------+---------+---------+----------+
| t190920_pkey | -       | i       |        1 |
+--------------+---------+---------+----------+

index 是沒有自己的 type,可以思考一下.
包含一個欄位.  

index 除了欄位以外,還有 operator 與 method.
我們來看以下的 SQL Command 查詢的結果.

select idx.indexrelid::regclass
     , opc.opcintype::regtype
     , opc.opcname
     , am.amname
  from pg_catalog.pg_index idx
     , pg_catalog.pg_opclass opc
     , pg_catalog.pg_am am
 where idx.indrelid = 't190920'::regclass
   and opc.oid = ALL (idx.indclass)
   and am.oid = opc.opcmethod;

+--------------+-----------+----------+--------+
|  indexrelid  | opcintype | opcname  | amname |
+--------------+-----------+----------+--------+
| t190920_pkey | integer   | int4_ops | btree  |
+--------------+-----------+----------+--------+

btree 支持的 integer operator 請參考以下文件中的 Table 38.2. B-tree Strategies
https://www.postgresql.org/docs/current/xindex.html

可以看到有5種.

我們可以用這道 SQL Command 來查看

\t
Tuples only is on.
\pset border 0
Border style is 0.

select string_agg(amop.amopopr::regoperator::text, ' '
       order by amop.amopopr::regoperator::text) as "opfamily_operator"
  from pg_am am
     , pg_opfamily opf
     , pg_amop amop
 where opf.opfmethod = am.oid
   and amop.amopfamily = opf.oid
   and am.amname = 'btree'
   and opf.opfname = 'integer_ops';

<(bigint,bigint) <(bigint,integer) <(bigint,smallint) <(integer,bigint) <(integer,integer) 
<(integer,smallint) <(smallint,bigint) <(smallint,integer) <(smallint,smallint) 
<=(bigint,bigint) <=(bigint,integer) <=(bigint,smallint) <=(integer,bigint) 
<=(integer,integer) <=(integer,smallint) <=(smallint,bigint) <=(smallint,integer) <=(smallint,smallint) 
=(bigint,bigint) =(bigint,integer) =(bigint,smallint) =(integer,bigint) =(integer,integer) 
=(integer,smallint) =(smallint,bigint) =(smallint,integer) =(smallint,smallint) 
>(bigint,bigint) >(bigint,integer) >(bigint,smallint) >(integer,bigint) >(integer,integer) 
>(integer,smallint) >(smallint,bigint) >(smallint,integer) >(smallint,smallint) 
>=(bigint,bigint) >=(bigint,integer) >=(bigint,smallint) >=(integer,bigint) >=(integer,integer) 
>=(integer,smallint) >=(smallint,bigint) >=(smallint,integer) >=(smallint,smallint)
 

因為有好幾種 integer....後續介紹其他資料型態與運算子時,會有更多令人驚艷的特色.

接著我們來看 依賴關係

SELECT d.classid::regclass
     , CASE d.classid
        WHEN 'pg_class'::regclass THEN d.objid::regclass::text
        ELSE NULL::text
       END AS objid
     , d.refclassid::regclass
     , CASE d.refclassid
        WHEN 'pg_class'::regclass THEN d.refobjid::regclass::text
        WHEN 'pg_namespace'::regclass THEN d.refobjid::regnamespace::text
        ELSE NULL::text
       END AS refobjid
     , CASE WHEN d.refobjsubid > 0 THEN a.attname
            ELSE NULL::text
       END AS refobjsubid
     , d.deptype
     , c.relkind
  FROM pg_catalog.pg_depend d
  LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = refobjid AND a.attnum = d.refobjsubid)
  LEFT JOIN pg_catalog.pg_class c ON (c.oid = d.objid)
 WHERE (d.objid = 'miku.t190920'::regclass
    OR d.refobjid = 'miku.t190920'::regclass);

+---------------+--------------------+--------------+----------+-------------+---------+---------+
|    classid    |       objid        |  refclassid  | refobjid | refobjsubid | deptype | relkind |
+---------------+--------------------+--------------+----------+-------------+---------+---------+
| pg_type       | ¤                  | pg_class     | t190920  | ¤           | i       | ¤       |
| pg_class      | t190920            | pg_namespace | miku     | ¤           | n       | r       |
| pg_attrdef    | ¤                  | pg_class     | t190920  | testid      | a       | ¤       |
| pg_constraint | ¤                  | pg_class     | t190920  | testid      | a       | ¤       |
| pg_class      | t190920_testid_seq | pg_class     | t190920  | testid      | a       | S       |
+---------------+--------------------+--------------+----------+-------------+---------+---------+
(5 rows)

依賴型態在此有 i, n, a

i : DEPENDENCY_INTERNAL
n : DEPENDENCY_NORMAL
a : DEPENDENCY_AUTO 

DEPENDENCY_AUTO 大致上是自動產生出來的延伸物件.像是欄位(pg_attrdef)或是 Sequence,在前面的探討中,可以理解.
pg_constraint 就是限制,對應前面的 primary key.

這時候會不會覺得應該要有一個 pg_constraint, 符合 Postgresql 一貫的設計哲學.

select conname
     , contype
     , conrelid::regclass
     , conindid::regclass
     , conkey
  from pg_catalog.pg_constraint
 where conrelid = 'miku.t190920'::regclass;

+--------------+---------+----------+--------------+--------+
|   conname    | contype | conrelid |   conindid   | conkey |
+--------------+---------+----------+--------------+--------+
| t190920_pkey | p       | t190920  | t190920_pkey | {1}    |
+--------------+---------+----------+--------------+--------+
(1 row)

Primary Key 型態的 constraint,名為 t190920_pkey,
使用了名為 t190920_pkey 的 index. constraint 使用了第一個欄位.

由上面的探討,可以理解到 Postgresql 精巧優美的設計.