Postgresql 系統資訊schema pg_catalog 初步探討

請先參考文件:
https://docs.postgresql.tw/the-sql-language/ddl/schemas#5-8-5-xi-tong-zi-xun-schema

在此要先感謝Postgresql.TW社群的古永忠博士及社群夥伴,製作了很好的中文參考資料.

1. information_schema 與 pg_catalog 的差異
在ANSI SQL的標準中,有定義了information_schema, 許多資料庫產品也有提供.
在敝人以往一些在ithelp的回答中,也有使用.
我們先來看一下 MySQL的,可以參考
https://dev.mysql.com/doc/refman/8.0/en/information-schema.html

https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

在這裡可以看到
TABLE_CATALOG:
    The name of the catalog to which the table belongs. This value is always def.

TABLE_SCHEMA:
    The name of the schema (database) to which the table belongs. 

以下看實例:
(miku1) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| miku1              |
+--------------------+

select TABLE_CATALOG, TABLE_SCHEMA
  from information_schema.tables
 group by TABLE_CATALOG, TABLE_SCHEMA;
+---------------+--------------------+
| TABLE_CATALOG | TABLE_SCHEMA       |
+---------------+--------------------+
| def           | information_schema |
| def           | miku1              |
+---------------+--------------------+
2 rows in set (0.11 sec)

參考好MySQL的部分,接著我們來看 Postgresql的.
在昨天的 Postgresql 一些基本資料的查詢方式 中,
可以看到我的Postgresql 中有兩個database,分別是 miku, advwork.
以下看實例:
在 database miku中, schema的search path,我只有設定了兩個.
[miku]# show search_path;
+-----------------+
|   search_path   |
+-----------------+
| "$user", public |
+-----------------+
(1 row)

透過meta command查,有17個.
[miku]# \dn
      List of schemas
+--------------+----------+
|     Name     |  Owner   |
+--------------+----------+
| dbms_alert   | miku     |
| dbms_assert  | miku     |
| dbms_output  | miku     |
| dbms_pipe    | miku     |
| dbms_random  | miku     |
| dbms_utility | miku     |
| geolite      | miku     |
| miku         | miku     |
| oracle       | miku     |
| plunit       | miku     |
| plvchr       | miku     |
| plvdate      | miku     |
| plvlex       | miku     |
| plvstr       | miku     |
| plvsubst     | miku     |
| public       | bunko666 |
| utl_file     | miku     |
+--------------+----------+
(17 rows)

但實際上還有隱藏的.如同MySQL我們來透過 information_schema.tables查詢
select table_schema
  from information_schema.tables
 group by table_schema;

+--------------------+
|    table_schema    |
+--------------------+
| utl_file           |
| public             |
| miku               |
| pg_catalog         |
| oracle             |
| dbms_pipe          |
| geolite            |
| information_schema |
+--------------------+
(8 rows)

因為這是要有table的,才會在information_schema.tables有資料,上面17個schema中,有些只有函數,有些只有view,
故在此不會出現.可以觀察到有 information_schema與pg_catalog兩個隱藏的schema.
在Postgresql create database時會建立information_schema,以符合ANSI SQL規範.
不會設定到search_path,所以我們查詢時需要包含schema名稱.
不同database之間的information_schema的內容不同.只包含本database相關資訊.
在miku database中
select table_catalog
  from information_schema.tables
 group by table_catalog; 
+---------------+
| table_catalog |
+---------------+
| miku          |
+---------------+
(1 row)
只會看到一個 table_catalog,與MySQL不同,MySQL只會顯示def.
在此要說明一下, 在關聯式理論中,是catalog 是相當於現在一般使用的database層級.
故可以看到,不管是MySQL或是Postgesql均在裡面使用了catalog來對應.
在advwork database中
+---------------+
| table_catalog |
+---------------+
| advwork       |
+---------------+

另外在Postgresql中有 information_schema_catalog_name ,只有一個欄位,只有一筆資料.
先看關於此table的資訊

[miku]# \d information_schema.information_schema_catalog_name
              View "information_schema.information_schema_catalog_name"
+--------------+-----------------------------------+-----------+----------+---------+
|    Column    |               Type                | Collation | Nullable | Default |
+--------------+-----------------------------------+-----------+----------+---------+
| catalog_name | information_schema.sql_identifier |           |          |         |
+--------------+-----------------------------------+-----------+----------+---------+

[miku]# select * from information_schema.information_schema_catalog_name;
+--------------+
| catalog_name |
+--------------+
| miku         |
+--------------+
(1 row)

[advwork]# select * from information_schema.information_schema_catalog_name;
+--------------+
| catalog_name |
+--------------+
| advwork      |
+--------------+
(1 row)

可以看到不同database,有各自的值.
MySQL未提供此table.


另外我們有觀察到還有一個 pg_catalog. 這個Postgresql系統資訊的schema.
這是映射過來,這樣每個資料庫的owner才能有效使用自己的物件.
實際上是存放在 postgres database中.
來看實例:

[postgres]# select * from information_schema.information_schema_catalog_name;
+--------------+
| catalog_name |
+--------------+
| postgres     |
+--------------+

可以看到我們登入了 postgres 這個catalog (也就是 database)
查看底下的 schema

[postgres]# \dn pg_catalog
     List of schemas
+------------+----------+
|    Name    |  Owner   |
+------------+----------+
| pg_catalog | bunko666 |
+------------+----------+
(1 row)

本尊出現了.此處的Owner 一般會是postgres.

因為有映射的關係,我們在一般使用者也可以查看 pg_catalog下的tables / views
也不需要加上 pg_catalog ,方便使用.
例如要看 pg_class的定義

\d pg_class
                      Table "pg_catalog.pg_class"
+---------------------+--------------+-----------+----------+---------+
|       Column        |     Type     | Collation | Nullable | Default |
+---------------------+--------------+-----------+----------+---------+
| relname             | name         |           | not null |         |
| relnamespace        | oid          |           | not null |         |
.....

以及 pg_namespace
\d pg_namespace
             Table "pg_catalog.pg_namespace"
+----------+-----------+-----------+----------+---------+
|  Column  |   Type    | Collation | Nullable | Default |
+----------+-----------+-----------+----------+---------+
| nspname  | name      |           | not null |         |
| nspowner | oid       |           | not null |         |
| nspacl   | aclitem[] |           |          |         |
+----------+-----------+-----------+----------+---------+
Indexes:
    "pg_namespace_nspname_index" UNIQUE, btree (nspname)
    "pg_namespace_oid_index" UNIQUE, btree (oid)

今天先探討了 information_schema 與 pg_catalog 的初步,
還有schema catalog 的關係.
在 Postgresql中是以下的關係
Computer -> Cluter -> Catalog -> Schema -> Database Object (Table/View 等等)

一台電腦可以有多個 Cluster,在 Oracle 叫 Instance
一個 Cluster 可以有多個 Catalog ,包含Postgresql在內,許多資料庫會另外稱作 Database.
一個 Database 可以有多個 Schema, 又叫做  namespace of relations (關聯式物件), 還有相關存取規則(ACL).
可以觀察上面的 pg_namespace 的欄位.
以下有一張圖片可以查看.
明天我們將繼續探討 pg_catalog ,會使用到 pg_class, pg_namespace.