Некоторые объекты системного каталога

Создадим базу данных и тестовые объекты: 

=> CREATE DATABASE data_catalog;

CREATE DATABASE

=> \c data_catalog

You are now connected to database "data_catalog" as user "postgres".

=> CREATE TABLE employees(id serial PRIMARY KEY, name text, manager integer);

CREATE TABLE

=> CREATE VIEW top_managers AS
  SELECT * FROM employees WHERE manager IS NULL;

CREATE VIEW

Некоторые таблицы системного каталога нам уже знакомы из предыдущей темы. Это базы данных: 

=> SELECT * FROM pg_database WHERE datname = 'data_catalog' \gx

-[ RECORD 1 ]-+-------------
datname       | data_catalog
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12327
datfrozenxid  | 548
datminmxid    | 1
dattablespace | 1663
datacl        |

И схемы: 

=> SELECT * FROM pg_namespace WHERE nspname = 'public' \gx

-[ RECORD 1 ]---------------------------------
nspname  | public
nspowner | 10
nspacl   | {postgres=UC/postgres,=UC/postgres}

Важная таблица pg_class хранит описание целого ряда объектов: таблиц, представлений (включая материализованные), индексов, последовательностей. Все эти объекты называются в PostgreSQL общим словом "отношение" (relation), отсюда и префикс "rel" в названии столбцов: 

=> SELECT relname, relkind, relnamespace, relfilenode, relowner, reltablespace 
FROM pg_class WHERE relname ~ '^(emp|top).*';

     relname      | relkind | relnamespace | relfilenode | relowner | reltablespace 
------------------+---------+--------------+-------------+----------+---------------
 employees_id_seq | S       |         2200 |       16454 |       10 |             0
 employees        | r       |         2200 |       16456 |       10 |             0
 employees_pkey   | i       |         2200 |       16463 |       10 |             0
 top_managers     | v       |         2200 |       16465 |       10 |             0
(4 rows)

Типы объектов различаются по столбцу relkind.

Конечно, для каждого типа объектов имеет смысл только часть столбцов; кроме того, удобнее смотреть не на многочисленные OID, а на нормальные значения. Для этого существуют различные представления, например: 

=> SELECT schemaname, tablename, tableowner, tablespace
FROM pg_tables WhERE schemaname = 'public';

 schemaname | tablename | tableowner | tablespace 
------------+-----------+------------+------------
 public     | employees | postgres   | 
(1 row)


=> SELECT * 
FROM pg_views WHERE schemaname = 'public';

 schemaname |   viewname   | viewowner |              definition              
------------+--------------+-----------+--------------------------------------
 public     | top_managers | postgres  |  SELECT employees.id,               +
            |              |           |     employees.name,                 +
            |              |           |     employees.manager               +
            |              |           |    FROM employees                   +
            |              |           |   WHERE (employees.manager IS NULL);
(1 row)

Использование команд psql

Список всех "отношений" можно посмотреть командой \d* в psql, где * - символ, обозначающий тип объекта (как relkind). Например, таблицы: 

=> \dt

           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | employees | table | postgres
(1 row)

Или представления: 

=> \dv

            List of relations
 Schema |     Name     | Type |  Owner   
--------+--------------+------+----------
 public | top_managers | view | postgres
(1 row)

Эти команды можно снабдить модификатором "+", чтобы получить больше информации: 

=> \dt+

                        List of relations
 Schema |   Name    | Type  |  Owner   |    Size    | Description 
--------+-----------+-------+----------+------------+-------------
 public | employees | table | postgres | 8192 bytes | 
(1 row)

Чтобы получить детальную информацию о конкретном объекте, надо воспользоваться командой \d (без дополнительной буквы): 

=> \d top_managers

             View "public.top_managers"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 id      | integer |           |          | 
 name    | text    |           |          | 
 manager | integer |           |          |

Модификатор "+" остается в силе. 

=> \d+ top_managers

                         View "public.top_managers"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description 
---------+---------+-----------+----------+---------+----------+-------------
 id      | integer |           |          |         | plain    | 
 name    | text    |           |          |         | extended | 
 manager | integer |           |          |         | plain    | 
View definition:
 SELECT employees.id,
    employees.name,
    employees.manager
   FROM employees
  WHERE employees.manager IS NULL;

Помимо "отношений", аналогичным образом можно смотреть и на другие объекты, такие, как схемы (\dn) или функции (\df).

Модификатор "S" позволяет вывести не только пользовательские, но и системные объекты. А с помощью шаблона можно ограничить выборку: 

=> \dfS pg*size

                                   List of functions
   Schema   |          Name          | Result data type | Argument data types |  Type  
------------+------------------------+------------------+---------------------+--------
 pg_catalog | pg_column_size         | integer          | "any"               | normal
 pg_catalog | pg_database_size       | bigint           | name                | normal
 pg_catalog | pg_database_size       | bigint           | oid                 | normal
 pg_catalog | pg_indexes_size        | bigint           | regclass            | normal
 pg_catalog | pg_relation_size       | bigint           | regclass            | normal
 pg_catalog | pg_relation_size       | bigint           | regclass, text      | normal
 pg_catalog | pg_table_size          | bigint           | regclass            | normal
 pg_catalog | pg_tablespace_size     | bigint           | name                | normal
 pg_catalog | pg_tablespace_size     | bigint           | oid                 | normal
 pg_catalog | pg_total_relation_size | bigint           | regclass            | normal
(10 rows)

Psql предлагает большое количество команд для просмотра системного каталога. Как правило, они имеют мнемонические имена. Например, \df - describe function, \sf - show function: 

=> \sf pg_catalog.pg_database_size(oid)

CREATE OR REPLACE FUNCTION pg_catalog.pg_database_size(oid)
 RETURNS bigint
 LANGUAGE internal
 PARALLEL SAFE STRICT
AS $function$pg_database_size_oid$function$

Полный список всегда можно посмотреть в документации или командой \?.

Изучение структуры системного каталога

Все команды psql, описывающие объекты, обращаются к таблицам системного каталога. Чтобы посмотреть, какие запросы на самом деле выполняет psql, можно установить переменную ECHO_HIDDEN: 

=> \set ECHO_HIDDEN on







=> \dt employees





********* 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 'f' THEN 'foreign table' WHEN 'p' THEN 'table' 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','s','')
      AND n.nspname !~ '^pg_toast'
  AND c.relname ~ '^(employees)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | employees | table | postgres
(1 row)






=> \unset ECHO_HIDDEN










OID и reg-типы




Как мы видели, описания таблиц и представлений хранятся в pg_class. А столбцы
располагаются в отдельной таблице pg_attribute. Чтобы получить список столбцов
конкретной таблицы, надо соединить pg_class и pg_attribute:





=> SELECT a.attname, a.atttypid
FROM pg_attribute a
WHERE a.attrelid = (
  SELECT oid FROM pg_class WHERE relname = 'employees'
)
AND a.attnum > 0;





 attname | atttypid 
---------+----------
 id      |       23
 name    |       25
 manager |       23
(3 rows)







Используя reg-типы, запрос можно написать проще, без явного обращения
к pg_class:





=> SELECT a.attname, a.atttypid
FROM pg_attribute a
WHERE a.attrelid = 'employees'::regclass
AND a.attnum > 0;





 attname | atttypid 
---------+----------
 id      |       23
 name    |       25
 manager |       23
(3 rows)







Здесь мы преобразовали строку 'employees' к типу OID. Аналогично мы можем
вывести OID как текстовое значение:





=> SELECT a.attname, a.atttypid::regtype
FROM pg_attribute a
WHERE a.attrelid = 'employees'::regclass
AND a.attnum > 0;





 attname | atttypid 
---------+----------
 id      | integer
 name    | text
 manager | integer
(3 rows)







Конец демонстрации.