Hologres:Tabel sistem

Kueri properti dan indeks tabel

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

Ambil DDL untuk tabel atau tampilan

-- Ambil DDL untuk tabel
SELECT hg_dump_script('<table_name>');

-- Ambil DDL untuk tampilan
SELECT hg_dump_script('<view_name>');

Jika kueri gagal, instal ekstensi hg_toolkit terlebih dahulu:

CREATE EXTENSION hg_toolkit;

Kueri titik akhir instans

SHOW hg_frontend_endpoints;

Daftar semua database dalam instans saat ini

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;

Daftar semua pemetaan pengguna dalam database saat ini

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;

Daftar semua skema dalam database saat ini

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;

Daftar semua tabel, tabel eksternal, dan tampilan dalam database saat ini

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;

Daftar semua tabel dan pemiliknya dalam skema saat ini (tidak termasuk tabel sistem)

-- Sertakan tabel sistem
SELECT * FROM pg_tables;

-- Kecualikan tabel sistem
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;

Daftar tabel anak dari tabel induk

-- Dengan nilai kunci partisi
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';

-- Tanpa nilai kunci partisi
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>';

Daftar tabel anak dengan waktu pembuatan dan tabel induk

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;

Daftar semua tabel eksternal dan tabel MaxCompute yang sesuai

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

Daftar semua tampilan dalam database saat ini

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;

Temukan tampilan yang bergantung pada tabel

SELECT * FROM information_schema.view_table_usage WHERE table_name = '<table_name>';

Kueri komentar kolom dan komentar tabel

-- Kueri komentar kolom untuk tabel
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;

Ganti <schema_name>.<table_name> dengan nama skema dan tabel yang sebenarnya.

-- Kueri komentar tabel dan metadata terkait (pemilik, ukuran)
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;

-- Kueri komentar pada tabel tertentu
SELECT pg_catalog.obj_description('<table_name>'::regclass::oid, 'pg_class') AS "Description";

Daftar semua pengguna dan role dalam database

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;

Daftar semua ekstensi dalam database

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;

Periksa izin akun

SELECT * FROM pg_roles WHERE rolname = '<uid>';

Daftar semua pengguna instans dan izinnya

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;

Daftar semua tabel di mana pengguna memiliki izin

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)
    );

Ganti <user_id> dengan ID pengguna yang sebenarnya.

Daftar semua pengguna yang memiliki izin pada tabel

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

Ganti <schema_name>.<table_name> dengan nama skema dan tabel yang sebenarnya.