psql 的 Meta-Commands 等同執行了什麼 SQL Query?

想要找出 psql meta-command 對照到什麼樣的 SQL 指令，可以藉由變更 log_min_duration_statement 參數（預設 -1），就能夠從資料庫的 log 看出送到資料庫的是什麼指令了。

首先先在這次操作的 Session 設定上述 Log 紀錄參數

edb=# SET log_min_duration_statement TO 0;

對照一下 Log （位在 $PGDATA/pg_log/ 底下）內容，可以確認上面變更

2016-08-17 03:57:16 UTC LOG: duration: 0.164 ms statement: set log_min_duration_statement to 0;

接著可以開始觀察了，下舉四個例子：

1. 列舉資料庫帳戶

edb=# \du

對應 Log 內容

2016-08-17 03:57:27 UTC LOG: duration: 1.768 ms statement: SELECT r.rolname, r.rolsuper, r.rolinherit,
          r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
          r.rolconnlimit, r.rolvaliduntil,
          ARRAY(SELECT b.rolname
                FROM pg_catalog.pg_auth_members m
                JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
                WHERE m.member = r.oid) as memberof
        , r.rolreplication
        , r.rolbypassrls
        , r.rolprofile
        , edb_get_role_status(r.oid)
        , edb_get_password_expiry_date(r.oid)
        , r.rollockdate
        FROM pg_catalog.pg_roles r
        ORDER BY 1;

2. 列舉當前登入的 Database 裡面的 Schema

edb=# \du

對應 Log 內容

2016-08-17 07:57:35 UTC LOG:  duration: 3.986 ms  statement: SELECT n.nspname AS "Name",
          pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
        FROM pg_catalog.pg_namespace n
        WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
              AND n.nspname <> 'sys'
              AND n.nspname <> 'dbo'
        AND n.nspparent = 0
        ORDER BY 1;

3. 列舉表格物件

edb=# \dt

對應 Log 內容

2016-08-17 07:59:42 UTC LOG:  duration: 3.259 ms  statement: SELECT n.nspname as "Schema",
          c.relname as "Name",
          CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
          pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','')
              AND n.nspname <> 'pg_catalog'
              AND n.nspname <> 'information_schema'
              AND n.nspname <> 'sys'
              AND n.nspname <> 'dbo'
              AND n.nspname !~ '^pg_toast'
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 1,2;

4. 列舉 Database

edb=# \l

對應 Log 內容

2016-08-17 08:03:12 UTC LOG:  duration: 54.428 ms  statement: SELECT d.datname as "Name",
               pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
               pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
               d.datcollate as "Collate",
               d.datctype as "Ctype",
               d.daticu as "ICU",
               pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
        FROM pg_catalog.pg_database d
        ORDER BY 1;

透過這個小筆記，希望能夠讓大家比較了解 PostgreSQL 的 Log 如何運用。