再探Postgresql Catalog
-----

pg_catalog schema 底下的 table, 也是定義在 pg_catalog 裡的 pg_class

可以參考 https://docs.postgresql.tw/internals/system-catalogs/pg_class#table-52-11-pg_class-columns

所以我們可以用以下 SQL Command

select *
  from pg_catalog.pg_class
 where oid = 'pg_catalog.pg_class'::regclass;

或是

select *
  from pg_catalog.pg_class
 where relname = 'pg_class';

都可以查到以下的結果

+-[ RECORD 1 ]--------+-----------------------------------------+
| relname             | pg_class                                |
| relnamespace        | 11                                      |
| reltype             | 83                                      |
| reloftype           | 0                                       |
| relowner            | 10                                      |
| relam               | 0                                       |
| relfilenode         | 0                                       |
| reltablespace       | 0                                       |
| relpages            | 12                                      |
| reltuples           | 342                                     |
| relallvisible       | 12                                      |
| reltoastrelid       | 0                                       |
| relhasindex         | t                                       |
| relisshared         | f                                       |
| relpersistence      | p                                       |
| relkind             | r                                       |
| relnatts            | 33                                      |
....


可以使用 psql metacommand 來查看

[miku]# \d pg_catalog.pg_class
                      Table "pg_catalog.pg_class"
+---------------------+--------------+-----------+----------+---------+
|       Column        |     Type     | Collation | Nullable | Default |
+---------------------+--------------+-----------+----------+---------+
| relname             | name         |           | not null |         |
| relnamespace        | oid          |           | not null |         |
| reltype             | oid          |           | not null |         |
| reloftype           | oid          |           | not null |         |
| relowner            | oid          |           | not null |         |
| relam               | oid          |           | not null |         |
| relfilenode         | oid          |           | not null |         |
| reltablespace       | oid          |           | not null |         |
| relpages            | integer      |           | not null |         |
| reltuples           | real         |           | not null |         |
| relallvisible       | integer      |           | not null |         |
| reltoastrelid       | oid          |           | not null |         |
| relhasindex         | boolean      |           | not null |         |
| relisshared         | boolean      |           | not null |         |
| relpersistence      | "char"       |           | not null |         |
| relkind             | "char"       |           | not null |         |
| relnatts            | smallint     |           | not null |         |
| relchecks           | smallint     |           | not null |         |
| relhasoids          | boolean      |           | not null |         |
| relhasrules         | boolean      |           | not null |         |
| relhastriggers      | boolean      |           | not null |         |
| relhassubclass      | boolean      |           | not null |         |
| relrowsecurity      | boolean      |           | not null |         |
| relforcerowsecurity | boolean      |           | not null |         |
| relispopulated      | boolean      |           | not null |         |
| relreplident        | "char"       |           | not null |         |
| relispartition      | boolean      |           | not null |         |
| relrewrite          | oid          |           | not null |         |
| relfrozenxid        | xid          |           | not null |         |
| relminmxid          | xid          |           | not null |         |
| relacl              | aclitem[]    |           |          |         |
| reloptions          | text[]       |           |          |         |
| relpartbound        | pg_node_tree |           |          |         |
+---------------------+--------------+-----------+----------+---------+
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

---
要查看是在哪個 schema (namespace),可以跟 pg_namespace join

select n.nspname
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n
    on c.relnamespace = n.oid
 where c.relname = 'pg_class';

+------------+
|  nspname   |
+------------+
| pg_catalog |
+------------+

或是直接轉型

select relnamespace
     , relnamespace::regnamespace
  from pg_catalog.pg_class
 where relname = 'pg_class';

+--------------+--------------+
| relnamespace | relnamespace |
+--------------+--------------+
|           11 | pg_catalog   |
+--------------+--------------+

因為 relnamespace 是 oid.
請參考
https://docs.postgresql.tw/the-sql-language/data-types/8.18.-zhi-biao-xing-bie

所以可以用直接轉型的方法,Postgresql 是 物件關聯式資料庫,由此可以體會到.
請參考:
https://www.postgresql.org/docs/current/intro-whatis.html

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2.


接著我們來查看是否有不屬於 pg_catalog 的物件

select relnamespace::regnamespace
     , count(*)
  from pg_catalog.pg_class
 where relnamespace::regnamespace::text <> 'pg_catalog'
 group by rollup(relnamespace::regnamespace)
 order by 1; 
+--------------------+-------+
|    relnamespace    | count |
+--------------------+-------+
| pg_toast           |    54 |
| public             |     1 |
| information_schema |    67 |
| miku               |    11 |
| geolite            |    11 |
| oracle             |    11 |
| dbms_pipe          |     1 |
| utl_file           |     1 |
| ¤                  |   157 |
+--------------------+-------+
(9 rows)

可以觀察到,我使用了  ::text 轉型之後來比較,並且用 rollup 來取得總計.

接著我們來看 pg_class 有多少物件及佔用空間

select c.relkind
     , count(c.*)
  from pg_catalog.pg_class c
     , pg_catalog.pg_namespace n
 where n.nspname = 'pg_catalog'
   and c.relnamespace = n.oid
 group by rollup (c.relkind)
 order by 1;

+---------+-------+
| relkind | count |
+---------+-------+
| i       |   116 |
| r       |    62 |
| v       |    59 |
| ¤       |   237 |
+---------+-------+
(4 rows)


r: Relations (ordinary tables)
v: Views
i: Indexes

select pg_size_pretty(sum(
       pg_total_relation_size(c.oid))) as curt_size
  from pg_catalog.pg_class c
     , pg_catalog.pg_namespace n
 where n.nspname = 'pg_catalog'
   and c.relnamespace = n.oid;
+-----------+
| curt_size |
+-----------+
| 14 MB     |
+-----------+
(1 row)


物件數量及容量隨著每個 Postgresql 新增或刪除物件而有所差異.
故當你實際查詢時,數字與的我的例子不同時,是正常的.

透過上面的查詢可以看到 pg_catalog 中含有 59 個 view.

觀察以下兩個query:

select schemaname
     , tablename
     , tableowner
  from pg_catalog.pg_tables -- 實際上是view 
 where schemaname = 'pg_catalog'
   and tablename = 'pg_class';
  
+------------+-----------+------------+
| schemaname | tablename | tableowner |
+------------+-----------+------------+
| pg_catalog | pg_class  | bunko666   | -- 一般owner會是postgres
+------------+-----------+------------+

select c.relnamespace
     , c.relname
     , c.relowner
  from pg_catalog.pg_class c -- table
     , pg_catalog.pg_namespace n
 where c.relname = 'pg_class'
   and n.nspname = 'pg_catalog'
   and c.relnamespace = n.oid;
+--------------+----------+----------+
| relnamespace | relname  | relowner |
+--------------+----------+----------+
|           11 | pg_class |       10 |
+--------------+----------+----------+
(1 row)


來看一下 pg_tables 的定義
\d+ pg_catalog.pg_tables
               View "pg_catalog.pg_tables"
+-------------+---------+-----------+----------+---------+
|   Column    |  Type   | Collation | Nullable | Default |
+-------------+---------+-----------+----------+---------+
| schemaname  | name    |           |          |         |
| tablename   | name    |           |          |         |
| tableowner  | name    |           |          |         |
| tablespace  | name    |           |          |         |
| hasindexes  | boolean |           |          |         |
| hasrules    | boolean |           |          |         |
| hastriggers | boolean |           |          |         |
| rowsecurity | boolean |           |          |         |
+-------------+---------+-----------+----------+---------+

View definition:
 SELECT n.nspname AS schemaname,
    c.relname AS tablename,
    pg_get_userbyid(c.relowner) AS tableowner,
    t.spcname AS tablespace,
    c.relhasindex AS hasindexes,
    c.relhasrules AS hasrules,
    c.relhastriggers AS hastriggers,
    c.relrowsecurity AS rowsecurity
   FROM pg_class c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
  WHERE c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]);
  
  
可以觀察到 一般 table 與 partition table 都可以透過 pg_tables 這個 view
來方便查詢.顯示的資料也較為明晰,無需對基礎表有深入了解. view 的用處在此可見一斑.
 

pg_catalog 中物件的相關描述,該如何查詢.
我們先來看看有沒有 description 之類的名字

select c.oid
     , c.oid::regclass
  from pg_catalog.pg_class c
     , pg_catalog.pg_namespace n
 where n.nspname = 'pg_catalog'
   and c.relnamespace = n.oid
   and c.oid::regclass::text like '%description';
+------+------------------+
| oid  |       oid        |
+------+------------------+
| 2609 | pg_description   |
| 2396 | pg_shdescription |
+------+------------------+
(2 rows)

發現有 description的tables

\d pg_description
            Table "pg_catalog.pg_description"
+-------------+---------+-----------+----------+---------+
|   Column    |  Type   | Collation | Nullable | Default |
+-------------+---------+-----------+----------+---------+
| objoid      | oid     |           | not null |         |
| classoid    | oid     |           | not null |         |
| objsubid    | integer |           | not null |         |
| description | text    |           | not null |         |
+-------------+---------+-----------+----------+---------+
Indexes:
    "pg_description_o_c_o_index" UNIQUE, btree (objoid, classoid, objsubid)

一般我們查看 pg_description 

select objoid
     , classoid::regclass
     , description
  from pg_catalog.pg_description
 order by objoid
 limit 10;
+--------+--------------+-----------------------------------------------+
| objoid |   classoid   |                  description                  |
+--------+--------------+-----------------------------------------------+
|     11 | pg_namespace | system catalog schema                         |
|     12 | pg_language  | built-in functions                            |
|     13 | pg_language  | dynamically-loaded C functions                |
|     14 | pg_language  | SQL-language functions                        |
|     15 | pg_operator  | equal                                         |
|     16 | pg_type      | boolean, 'true'/'false'                       |
|     17 | pg_type      | variable-length string, binary values escaped |
|     18 | pg_type      | single character                              |
|     19 | pg_type      | 63-byte type for storing system identifiers   |
|     20 | pg_type      | ~18 digit integer, 8-byte storage             |
+--------+--------------+-----------------------------------------------+
(10 rows)

可以觀察到 Postgresql 的物件,非常精彩.

但是關於 catalog本身的描述呢??

SELECT count(d.*)
  FROM pg_catalog.pg_description d
     , pg_catalog.pg_class c
     , pg_catalog.pg_namespace n
 WHERE d.objoid = c.oid
   AND c.relnamespace = n.oid
   AND n.nspname = 'pg_catalog';

+-------+
| count |
+-------+
|     0 | -- 殘念!
+-------+

但是別擔心,生命會找到出路的.明天我們接著繼續探討.