Hologres:システムテーブル

テーブルのプロパティとインデックスのクエリ

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

テーブルまたはビューの DDL の取得

-- テーブルの DDL を取得
SELECT hg_dump_script('<table_name>');

-- ビューの DDL を取得
SELECT hg_dump_script('<view_name>');

クエリが失敗した場合は、まず hg_toolkit 拡張機能をインストールしてください。

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_'
    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;

現在のスキーマ内のすべてのテーブルと所有者の一覧表示 (システムテーブルを除く)

-- システムテーブルを含む
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,
    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>';

列コメントとテーブルコメントのクエリ

-- テーブルの列コメントをクエリ
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_name>.<table_name>'::regclass::oid
ORDER BY a.attnum;

<schema_name>.<table_name> を実際のスキーマ名とテーブル名に置き換えます。

-- テーブルのコメントと関連メタデータ (所有者、サイズ) をクエリ
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;

-- 特定のテーブルのコメントをクエリ
SELECT pg_catalog.obj_description('<table_name>'::regclass::oid, 'pg_class') AS "Description";

データベース内のすべてのユーザーとロールの一覧表示

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 != '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('<user_id>', c.relowner, 'USAGE'::text)
        OR has_table_privilege('<user_id>', c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
        OR has_any_column_privilege('<user_id>', c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)
    );

<user_id> を実際のユーザー ID に置き換えます。

テーブルに対する権限を持つすべてのユーザーの一覧表示

SELECT rolname
FROM pg_roles
WHERE has_table_privilege(rolname, '<schema_name>.<table_name>',
    'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER');

<schema_name>.<table_name> を実際のスキーマ名とテーブル名に置き換えます。