Hologres:System tables

Overview

Limits

• A table whose name starts with hg is a Hologres system table. A table whose name starts with pg is a PostgreSQL system table. You cannot query data by joining PostgreSQL system tables with Hologres internal tables that you created. You cannot import data from the PostgreSQL system tables to the Hologres internal tables.

  Note

  In Hologres V1.3.22 and later, you can query data by joining PostgreSQL system tables with Hologres internal tables that you created, and you can import data from the PostgreSQL system tables to the Hologres internal tables. If you want to perform these operations, upgrade your Hologres instance to V1.3.22 or later.

• In Hologres, the object identifier (OID) field in a system table specifies the unique identifier of the relation among objects such as tables, indexes, and views. PostgreSQL is a standalone system, whereas Hologres is a distributed system. A Hologres instance has multiple FE nodes. In most cases, OID values of all FE nodes are different. If query results contain OID values, the OID values may be inconsistent.

hologres.hg_table_properties

pg_catalog.pg_tables

The pg_catalog.pg_tables system table contains metadata of tables. The following table describes the columns in the pg_catalog.pg_tables system table.

pg_catalog.pg_locks

pg_catalog.pg_class

The pg_catalog.pg_class system table contains all PostgreSQL system information. The following table describes the columns in the pg_catalog.pg_class system table.

hologres_statistic.hg_table_statistic

The hologres_statistic.hg_table_statistic system table contains statistics of Hologres. The following table describes the columns in the hologres_statistic.hg_table_statistic system table.

pg_catalog.pg_stats

The pg_catalog.pg_stats system table contains statistics of PostgreSQL. The following table describes the columns in the pg_catalog.pg_stats system table.

pg_catalog.pg_roles

The pg_catalog.pg_roles system table contains roles in Hologres instances and their permissions. The following table describes the columns in the pg_catalog.pg_roles system table.

information_schema.role_table_grants

Common SQL statements

Some common statements can be executed in psql, which is a PostgreSQL client application. For more information, see psql. You can use the following common SQL statements based on your business requirements.

Query indexes and properties of a table in Hologres

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

The tablename parameter specifies the name of the table.

Query the DDL statements that are used to create a table and a view

select hg_dump_script('<tablename>'); -- Query the DDL statement that is used to create a table.
select hg_dump_script('<viewname>');  -- Query the DDL statement that is used to create a view.

create extension hg_toolkit;

Query the endpoint of an instance

show hg_frontend_endpoints;

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

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

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

Query all internal 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;

Query all tables (excluding system tables) in the current schema and the owners of these tables

-- Query all tables in the current database. System tables are included.
SELECT * FROM pg_tables


-- Query all tables in the current schema and the owners of these tables. System tables are excluded.
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;
            

Query all child tables to which a parent table corresponds

-- Query child tables to which a parent table corresponds by specifying 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'
;



-- Query child tables to which a parent table corresponds without specifying 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';

Query the creation time and parent tables of all child tables

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;

Query all foreign tables and the MaxCompute tables to which the foreign tables correspond

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

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

Query the views that depend on a table

select * from information_schema.view_table_usage where table_name = '<table_name>';

Query comments on a table and comments on fields in a table

• Query comments on fields in 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.tablename>'::regclass::oid
  ORDER BY a.attnum;

  In this example, schema.tablename indicates {Schema name}.{Table name}.

• Query comments and related information about a table, such as the table owner.

  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;
  
  
  -- The following result is returned:
                          List of relations
   Schema | Name | Type  |      Owner       |  Size   | Description
  --------+------+-------+------------------+---------+-------------
   public | a    | table | 1365937xxxx | xxxx bytes | abcdef
  (1 row)

• Query comments on a specified table.

  select pg_catalog.obj_description('<tablename>'::regclass::oid, 'pg_class') as "Description";
  
  -- The following result is returned:
  Description
  ------------
  abcdef

  In this example, tablename indicates the name of the table whose comments you want to query.

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

Query 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 != 'holo_dump_stat'
    AND e.extname != 'query_log'
    AND e.extname != 'plpgsql'
ORDER BY
    1;

Query the permissions of an account

SELECT * FROM pg_roles where rolname='<uid>'

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

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

Query all users who have permissions on a table

select rolname from pg_roles where has_table_privilege(rolname, '<schemaname>.<tablename>',
 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER');