Hologres:System tables

Query table properties and indexes

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

Retrieve the DDL for a table or view

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

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

If the query fails, install the hg_toolkit extension first:

CREATE EXTENSION hg_toolkit;

Query the instance endpoint

SHOW hg_frontend_endpoints;

List all databases in the current instance

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;

List all user mappings in the current database

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;

List all schemas in the current database

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;

List all tables, foreign tables, and views in the current database

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;

List all tables and owners in the current schema (excluding system tables)

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

List child tables of a parent table

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

List child tables with creation time and parent table

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;

List all foreign tables and their corresponding MaxCompute tables

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

List all views in the current database

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;

Find views that depend on a table

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

Query column comments and table comments

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

Replace <schema_name>.<table_name> with the actual schema and table name.

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

List all users and roles in a 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_'
    AND r.rolname != 'holo_admin'
ORDER BY 1;

List all extensions in a 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;

Check the permissions of an account

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

List all users of an instance and their permissions

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;

List all tables on which a user has permissions

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

Replace <user_id> with the actual user ID.

List all users who have permissions on a table

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

Replace <schema_name>.<table_name> with the actual schema and table name.