Tracking: System Table in pg_catalog

[yezizp2012]

Since pg_catalog is already supported, when integrating some tools like psql(mostly in psql's slash commands), pgcli and Metabase(#2613) etc, we still missing some system tables. Please feel free to add or implement any tables you want.

System tables

Ref PostgreSQL: System Catalog.

• pg_type: https://www.postgresql.org/docs/current/catalog-pg-type.html
• pg_cast: https://www.postgresql.org/docs/current/catalog-pg-cast.html
• pg_enum: https://www.postgresql.org/docs/current/catalog-pg-enum.html
• pg_namespace: https://www.postgresql.org/docs/current/catalog-pg-namespace.html
• pg_class: https://www.postgresql.org/docs/current/catalog-pg-class.html
• pg_user: https://www.postgresql.org/docs/current/view-pg-user.html
• Support pg_opclass #3431: https://www.postgresql.org/docs/current/catalog-pg-opclass.html
• feat: more pg_catalog support #6837 pg_description: https://www.postgresql.org/docs/current/catalog-pg-description.html
• Support pg_stat_activity or something like mysql show processlist #5337
• Support pg_views #6118: https://www.postgresql.org/docs/current/view-pg-views.html
• pg_depend https://www.postgresql.org/docs/15/catalog-pg-depend.html

System Information Functions and Operators

Ref Function Info

• implement current_database()
• implement pg_table_is_visible()
• implement pg_get_userbyid()

System Administration Functions

Database Object Management Functions

• implement system administration functions that get the size of table, index, and MV #7766

[TennyZhuang]

I don't think they are the high-priority tables. The key point is make psql's slash commands happy, so we can refer to exec_command_d

At lease, some common used commands like \d \dt, \dt+ and \dT+ using pg_tables.

[TennyZhuang]

I'd prefer to support the belows firstly:

• \d
• \dt tables
• \di indexes
• \dm materialized views

[fuyufjh]

To support commands like \d or \dt, some additional functions under pg_catalog, such as pg_catalog.pg_get_userbyid(), need to be introduced. This looks troublesome. How do you think? @TennyZhuang @yezizp2012

PS. By adding a -E in psql the actual query will be printed to console:

dev=# \dt
********* QUERY **********
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 't' THEN 'TOAST table' 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
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

dev=# \d
********* QUERY **********
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 't' THEN 'TOAST table' 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
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

[mrayva]

Trying to use duckdb postgres extension to access risingwave. It errors out due to relpages column missing from pg_class table

D ATTACH 'dbname=dev user=root host=127.0.0.1 port=4566 ' AS postgres_db (TYPE POSTGRES);
D CREATE TABLE postgres_db.s1.tbl (id INTEGER, name VARCHAR);
Invalid Error: Failed to execute query "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT oid, nspname
FROM pg_namespace
ORDER BY oid;

SELECT pg_namespace.oid AS namespace_id, relname, relpages, attname,
    pg_type.typname type_name, atttypmod type_modifier, pg_attribute.attndims ndim,
    attnum, pg_attribute.attnotnull AS notnull, NULL constraint_id,
    NULL constraint_type, NULL constraint_key
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
JOIN pg_type ON atttypid=pg_type.oid
WHERE attnum > 0 AND relkind IN ('r', 'v', 'm', 'f', 'p')
UNION ALL
SELECT pg_namespace.oid AS namespace_id, relname, NULL relpages, NULL attname, NULL type_name,
    NULL type_modifier, NULL ndim, NULL attnum, NULL AS notnull,
    pg_constraint.oid AS constraint_id, contype AS constraint_type,
    conkey AS constraint_key
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_constraint ON (pg_class.oid=pg_constraint.conrelid)
WHERE relkind IN ('r', 'v', 'm', 'f', 'p') AND contype IN ('p', 'u')
ORDER BY namespace_id, relname, attnum, constraint_id;

SELECT n.oid, enumtypid, typname, enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
JOIN pg_namespace AS n ON (typnamespace=n.oid)
ORDER BY n.oid, enumtypid, enumsortorder;

SELECT n.oid, t.typrelid AS id, t.typname as type, pg_attribute.attname, sub_type.typname
FROM pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_class ON pg_class.oid = t.typrelid
JOIN pg_attribute ON attrelid=t.typrelid
JOIN pg_type sub_type ON (pg_attribute.atttypid=sub_type.oid)
WHERE pg_class.relkind = 'c'
AND t.typtype='c'
ORDER BY n.oid, t.oid, attrelid, attnum;

SELECT pg_namespace.oid, tablename, indexname
FROM pg_indexes
JOIN pg_namespace ON (schemaname=nspname)
ORDER BY pg_namespace.oid;
": ERROR:  Failed to run the query

Caused by these errors (recent errors listed first):
  1: Failed to bind expression: relpages
  2: Item not found: Invalid column: relpages