Hologres：系統資料表

概述

使用限制

• 表名稱以hg開頭的是Hologres系統資料表，以pg開頭的是PostgreSQL系統資料表，簡稱PG系統資料表。PG系統資料表不能與業務建立的表進行關聯查詢，也不能將PG系統資料表的資料匯入至業務建立的表中。

  說明

  Hologres 從V1.3.22版本開始，支援PG系統資料表與業務建立的表進行Join，以及將系統資料表資料匯出到Hologres表，但需注意，如果存在Hologres不支援的資料類型，則無法匯出到Hologres表，也無法與Hologres表進行Join。如果有需求請您升級執行個體。

• 在Hologres中，系統資料表中的OID欄位表示表、索引、視圖等關係的唯一標誌符。但Postgres是單機系統，Hologres是分布式系統，有多個FE節點，每個FE節點的OID序列通常不相同。所以當查詢結果中有OID時，可能會出現OID結果不一致的情況。

hologres.hg_table_properties

pg_catalog.pg_tables

pg_tables儲存表的元資訊，包含如下欄位。

pg_catalog.pg_locks

pg_catalog.pg_class

pg_class用於儲存原生Postgres的所有系統資訊，包含如下欄位。

hologres_statistic.hg_table_statistic

Hologres的統計資訊表，其欄位如下。

pg_catalog.pg_stats

pg_stats用於儲存Postgres原生的統計資訊，欄位如下。

pg_catalog.pg_roles

pg_roles用於存放執行個體內角色及其許可權資訊，欄位如下。

information_schema.role_table_grants

常用SQL

一些常見命令可以通過psql簡寫來實現，詳情請參見psql。同時也可以根據業務情況選擇使用如下提供的常見SQL。

查看錶對應的Hologres索引和屬性資訊

SELECT * FROM hologres.hg_table_properties where table_name = '<tablename>';

tablename為表名稱。

查看錶/視圖DDL

select hg_dump_script('<tablename>'); -- 表DDL
select hg_dump_script('<viewname>');  -- 視圖DDL

create extension hg_toolkit;

查看執行個體的Endpoint

show hg_frontend_endpoints;

查看當前執行個體下的所有資料庫

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",
    pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM
    pg_catalog.pg_database d
WHERE
    d.datname != 'postgres'
    AND d.datname != 'template0'
    AND d.datname != 'template1'
ORDER BY
    1;

列出當前資料庫下所有User Mapping

SELECT
    um.srvname AS "Server",
    um.usename AS "User name"
FROM
    pg_catalog.pg_user_mappings um
WHERE
    um.srvname != 'query_log_store_server'
ORDER BY
    1,
    2;

列出當前資料庫下所有Schema

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 != 'hologres'
    AND n.nspname != 'hologres_sample'
    AND n.nspname != 'hologres_statistic'
    AND n.nspname !~ '^hg_'
    AND n.nspname !~ '^holo_'
ORDER BY
    1;

列出當前資料庫下所有表、外表和視圖

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'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    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', 'p', 'v', 'm', 'S', 'f', '')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    1,
    2;

查看當前Schema下所有表以及表Owner（不包含系統資料表）

--查看當前DB下所有表（包含系統資料表）
SELECT * FROM pg_tables


--查看當前schema下所有表以及表owner（不包含系統資料表）
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' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' 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','p','v','m','S','f','')
and     n.nspname <> 'pg_catalog'
and     n.nspname <> 'information_schema'
and     n.nspname !~ '^pg_toast'
and     pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
            

查看父表對應的所有子表

-- 含分區索引值
SELECT  c.oid::pg_catalog.regclass
        ,c.relkind
        ,pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM    pg_catalog.pg_class c
        ,pg_catalog.pg_inherits i
WHERE   c.oid = i.inhrelid
AND     i.inhparent::pg_catalog.regclass = 'parent_table_name'::pg_catalog.regclass
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT'
;



-- 不含分區索引值
SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

查看所有子表的建立時間和所屬父表

select
        cn.nspname as child_schema_name,c.relname as child_table_name,
        pn.nspname as parent_schema_name,p.relname as parent_table_name,
        to_timestamp(cp.property_value::bigint) as create_time
from pg_inherits i
left join pg_class p on p.oid=i.inhparent
left join pg_namespace pn on pn.oid = p.relnamespace
left join pg_class c on c.oid=i.inhrelid
left join pg_namespace cn on cn.oid = c.relnamespace
left join hologres.hg_table_properties cp on cp.property_key='create_time' and cp.table_namespace=pn.nspname and cp.table_name = c.relname;

查看所有外部表格以及外部表格對應的MaxCompute表

SELECT  n.nspname
        ,c.relname
        ,s.srvname
        ,pg_catalog.array_to_string(
            ARRAY(
                SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(ftoptions)
            )
            ,', '
        )
FROM    pg_catalog.pg_foreign_table f
        ,pg_catalog.pg_foreign_server s
        ,pg_catalog.pg_class c
        ,pg_catalog.pg_namespace n
WHERE   s.oid = f.ftserver
and     c.oid = f.ftrelid
and     c.relnamespace = n.oid
and     n.nspname not in ('hologres', 'hologres_statistic', 'pg_catalog', 'pg_toast')
;
            

列出當前資料庫下所有視圖

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'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    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 ('v', '')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    1,
    2;

查看錶被哪些view依賴

select * from information_schema.view_table_usage where table_name = '<table_name>';

查看錶或者表欄位的注釋

• 查看指定表中欄位的注釋：

  SELECT a.attname as Column,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
    a.attnotnull as "Nullable",
    pg_catalog.col_description(a.attrelid, a.attnum) as "Description"
  FROM pg_catalog.pg_attribute a
  WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = '<schema.tablename>'::regclass::oid
  ORDER BY a.attnum;

  其中schema.tablename為{Schema名稱}.{表名稱}。

• 查看錶的注釋並且包含表的Owner等相關資訊。

  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' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
    pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
    pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
    pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r','p','v','m','S','f','')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
  ORDER BY 1,2;
  
  
  --返回結果樣本
                          List of relations
   Schema | Name | Type  |      Owner       |  Size   | Description
  --------+------+-------+------------------+---------+-------------
   public | a    | table | 1365937xxxx | xxxx bytes | abcdef
  (1 row)

• 只查看指定表的注釋資訊：

  select pg_catalog.obj_description('<tablename>'::regclass::oid, 'pg_class') as "Description";
  
  --返回結果樣本
  Description
  ------------
  abcdef

  其中tablename為指定表名稱。

列出當前資料庫下所有使用者和角色

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
FROM
    pg_catalog.pg_roles r
WHERE
    r.rolname !~ '^pg_'
ORDER BY
    1;

列出當前資料庫下所有EXTENSIONS

SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
        AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname != 'hg_admin_cmd'
    AND e.extname != 'holo_dump_stat'
    AND e.extname != 'holo_funcs'
    AND e.extname != 'holo_link'
    AND e.extname != 'holo_system_admin'
    AND e.extname != 'holo_dump_stat'
    AND e.extname != 'query_log'
    AND e.extname != 'plpgsql'
ORDER BY
    1;

查看某個帳號的對應許可權

SELECT * FROM pg_roles where rolname='<uid>'

查看當前執行個體下所有的使用者以及許可權樣本

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
FROM    pg_catalog.pg_roles r
WHERE   r.rolname !~ '^pg_'
ORDER BY 1;

查看一個使用者有許可權的所有表

SELECT current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]) THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            WHEN c.relkind = 'f'::"char" THEN 'FOREIGN'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type,
        CASE
            WHEN (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) OR (c.relkind = ANY 
(ARRAY['v'::"char", 'f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, false) 
& 8) = 8 THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_insertable_into,
        CASE
            WHEN t.typname IS NOT NULL THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_typed,
    NULL::character varying::information_schema.character_data AS commit_action
   FROM pg_namespace nc
     JOIN pg_class c ON nc.oid = c.relnamespace
     LEFT JOIN (pg_type t
     JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])) AND 
NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role('<USERID>', c.relowner, 'USAGE'::text)
 OR has_table_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
 OR has_any_column_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

查看一張表有許可權的所有使用者

select rolname from pg_roles where has_table_privilege(rolname, '<schemaname>.<tablename>',
 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER');