Hologres:System tables

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

-- Retrieve the DDL for a table
SELECT hg_dump_script('<table_name>');

-- Retrieve the DDL for a view
SELECT hg_dump_script('<view_name>');

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;

-- Include system tables
SELECT * FROM pg_tables;

-- Exclude system 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;

-- With partition key values
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';

-- Without partition key values
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;

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

-- Query column comments for a table
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;

-- Query table comments and related metadata (owner, size)
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;

-- Query the comment on a specific table
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_'
    AND r.rolname != 'holo_admin'
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)
    );

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