Hologres:システムテーブル

概要

制限事項

• hg で始まる名前のテーブルは、Hologres システムテーブルです。pg で始まる名前のテーブルは、PostgreSQLシステムテーブルです。作成した Hologres 内部テーブルと PostgreSQL システムテーブルを結合してデータをクエリすることはできません。PostgreSQL システムテーブルから Hologres 内部テーブルにデータをインポートすることはできません。

  説明

  Hologres V1.3.22 以降では、作成した Hologres 内部テーブルと PostgreSQL システムテーブルを結合してデータをクエリすること、および PostgreSQL システムテーブルから Hologres 内部テーブルにデータをインポートすることができます。ただし、PostgreSQL システムテーブルに Hologres でサポートされていないデータ型が含まれている場合、前述の操作を実行することはできません。これらの操作を実行する場合は、Hologres インスタンスを V1.3.22 以降にアップグレードしてください。

• Hologres では、システムテーブルのオブジェクト識別子 (OID) フィールドは、テーブル、インデックス、ビューなどのオブジェクト間の関係の一意の識別子を指定します。PostgreSQL はスタンドアロンシステムですが、Hologres は分散システムです。Hologres インスタンスには複数の FE ノードがあります。ほとんどの場合、すべての FE ノードの OID 値は異なります。クエリ結果に OID 値が含まれている場合、OID 値に矛盾が生じる可能性があります。

hologres.hg_table_properties

pg_catalog.pg_tables

pg_catalog.pg_tables システムテーブルには、テーブルのメタデータが含まれています。次の表に、pg_catalog.pg_tables システムテーブルの列を示します。

pg_catalog.pg_locks

pg_catalog.pg_class

pg_catalog.pg_class システムテーブルには、すべての PostgreSQL システム情報が含まれています。次の表に、pg_catalog.pg_class システムテーブルの列を示します。

hologres_statistic.hg_table_statistic

hologres_statistic.hg_table_statistic システムテーブルには、Hologres の統計情報が含まれています。次の表に、hologres_statistic.hg_table_statistic システムテーブルの列を示します。

pg_catalog.pg_stats

pg_catalog.pg_stats システムテーブルには、PostgreSQL の統計情報が含まれています。次の表に、pg_catalog.pg_stats システムテーブルの列を示します。

pg_catalog.pg_roles

pg_catalog.pg_roles システムテーブルには、Hologres インスタンスのロールとその権限が含まれています。次の表に、pg_catalog.pg_roles システムテーブルの列を示します。

information_schema.role_table_grants

一般的な SQL ステートメント

いくつかの一般的なステートメントは、PostgreSQL クライアントアプリケーションである 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;

インスタンスのエンドポイントをクエリする

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;

現在のデータベース内のすべてのユーザーマッピングをクエリする

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;

現在のデータベース内のすべてのスキーマをクエリする

SELECT
    n.nspname AS "Name", -- スキーマ名
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" -- 所有者
FROM
    pg_catalog.pg_namespace n
WHERE
    n.nspname !~ '^pg_' -- pg_ で始まるスキーマを除外
    AND n.nspname <> 'information_schema' -- information_schema を除外
    AND n.nspname != 'hologres' -- hologres を除外
    AND n.nspname != 'hologres_sample' -- hologres_sample を除外
    AND n.nspname != 'hologres_statistic' -- hologres_statistic を除外
    AND n.nspname !~ '^hg_' -- hg_ で始まるスキーマを除外
    AND n.nspname !~ '^holo_' -- 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;

現在のスキーマ内のすべてのテーブル (システムテーブルを除く) とこれらのテーブルの所有者をクエリする

-- 現在のデータベース内のすべてのテーブルをクエリします。システムテーブルが含まれます。
SELECT * FROM pg_tables


-- 現在のスキーマ内のすべてのテーブル (システムテーブルを除く) とこれらのテーブルの所有者をクエリします。
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 -- 子テーブルの OID
        ,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;

テーブルに依存するビューをクエリする

select * from information_schema.view_table_usage where table_name = '<table_name>'; -- table_name はテーブルの名前です

テーブルのコメントとテーブルのフィールドのコメントをクエリする

• テーブルのフィールドのコメントをクエリします。

  SELECT a.attname as Column, -- 列名
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type", -- データ型
    a.attnotnull as "Nullable", -- NULL 値を許可するかどうか
    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 は {スキーマ名}.{テーブル名} を示します。

• テーブルの所有者など、テーブルに関するコメントと関連情報をクエリします。

  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"; -- tablename はテーブル名です
  
  -- 次の結果が返されます。
  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;

データベース内のすべての拡張機能をクエリする

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>' -- uid はユーザー ID です

インスタンスのすべてのユーザーとその権限をクエリする

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) -- USERID はユーザー ID です
 OR has_table_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) -- USERID はユーザー ID です
 OR has_any_column_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)); -- USERID はユーザー ID です

テーブルに対する権限を持つすべてのユーザーをクエリする

select rolname from pg_roles where has_table_privilege(rolname, '<schemaname>.<tablename>', -- schemaname はスキーマ名、tablename はテーブル名です
 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER'); -- 権限