Системный каталог в PostgreSQL

Узнаем что такое системный каталог в postgresql, зачем он нужен и как с ним работать. Как получать информацию из системного каталога с помощью запросов и psql утилиты.
Tags:

Системный каталог PostgreSQL

В системном каталоге хранится информация об объектах в кластере баз данных. По стандарту SQL всегда должен присутствовать системный каталог, но в стандарте он называется информационной схемой.

В системном каталоге есть информация о базах данных, таблицах, индексах, представлениях, функциях и других объектах.

То есть, если мы создадим новую базу данных, то её описание можно будет получить из системного каталога. Если мы в базе данных создадим таблицу, то её описание будет в системном каталоге.

Системный каталог PostgreSQL это набор таблиц, представлений и функций и все они располагаются в схеме pg_catalog.

Посмотреть таблицы или представления из системного каталога можно как с помощью SQL запросов, так и с помощью команд psql.

Схема pg_catalog есть в каждой базе данных. Таблички в pg_catalog будут описывать объекты для своей базы. Но есть и общие объекты кластера, которые не принадлежать какой-либо базе данных. Например список баз данных (pg_database), список табличных пространств, список пользователей — это общие объекты кластера. К общим объектам кластера можно обращаться из любой базы.

Можно обратить внимание, что все таблички в системном каталоге начинаются на pg_, а все столбцы начинаются с префикса связанного с таблицей. Например так: pg_database.datname.

Все таблички системного каталога имеют специальный столбец с уникальным идентификатором: OID. Именно OID используется для идентификации объектов. Поэтому любую базу данных легко переименовать, так как кластер её знает по OID, а не по имени. Кроме баз данных по OID идентифицируются имена пользователей, схемы, таблицы и так далее.

Дополнительно вы можете почитать о системном каталоге  здесь .

Практика

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

Создадим базу данных и подключимся к ней:

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# CREATE DATABASE data_catalog;
CREATE DATABASE
Time: 83,557 ms

postgres@postgres=# \c data_catalog
You are now connected to database "data_catalog" as user "postgres".

Создадим табличку employees (сотрудники), со столбцами:

  • id — первичный ключ, где значения берутся из определённой последовательности (serial PRIMARY KEY);
  • name — имя сотрудника имеет текстовый тип (text);
  • manager — номер его менеджера, имеет числовой тип (integer):
postgres@data_catalog=# CREATE TABLE employees(id serial PRIMARY KEY, name text, manager integer);
CREATE TABLE
Time: 6,125 ms

Создадим представление (CREATE VIEW … AS …), которое будет показывать топ менеджеров. Это сотрудники у которых нет своего менеджера, то есть в столбце manager записан NULL:

postgres@data_catalog=# CREATE VIEW top_managers AS SELECT * FROM employees WHERE manager IS NULL;
CREATE VIEW
Time: 1,554 ms

Смотрим информацию о созданных объектах

Воспользуемся представлением pg_database из системного каталога и посмотрим на нашу базу data_catalog, которую мы только что сделали:

postgres@data_catalog=# SELECT * FROM pg_database WHERE datname = 'data_catalog' \gx
-[ RECORD 1 ]-+-------------
oid           | 16448
datname       | data_catalog
datdba        | 10
encoding      | 6
datcollate    | ru_RU.UTF-8
datctype      | ru_RU.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12663
datfrozenxid  | 478
datminmxid    | 1
dattablespace | 1663
datacl        |
Time: 0,930 ms

Вот так мы посмотрели описание нашей новой базы данных. Её oid = 24633, а oid владельца datdba = 10. Тут видна и другая информация.

Теперь из представления pg_namespace (список схем) посмотрим записи связанные со схемой public:

postgres@data_catalog=# SELECT * FROM pg_namespace WHERE nspname = 'public' \gx
-[ RECORD 1 ]---------------------------------
oid      | 2200
nspname  | public
nspowner | 10
nspacl   | {postgres=UC/postgres,=UC/postgres}
Time: 0,341 ms

Уникальный идентификатор этой схемы = 2200, а владелец схемы имеет OID = 10.

Таблица системного каталога pg_class

Теперь посмотрим на табличку pg_class, в которой хранятся таблички и представления, индексы и последовательности. Все эти объекты в SQL называются relation (отношения), отсюда и префикс rel.

postgres@data_catalog=# SELECT relname, relkind, relnamespace, relowner FROM pg_class WHERE relname ~ '^(emp|top).*';
     relname      | relkind | relnamespace | relowner
------------------+---------+--------------+----------
 employees_id_seq | S       |         2200 |       10
 employees        | r       |         2200 |       10
 employees_pkey   | i       |         2200 |       10
 top_managers     | v       |         2200 |       10
(4 rows)
Time: 1,133 ms

В столбце relkind типы объектов:

  • S — последовательность employees_id_seq;
  • r — таблица employees;
  • i — индекс employees_pkey;
  • v — представление top_managers.

Представление pg_tables и имена вместо OID

Теперь посмотрим на представление pg_tables и найдем все записи, где имя схемы public:

postgres@data_catalog=# SELECT * FROM pg_tables WhERE schemaname = 'public' \gx
-[ RECORD 1 ]----------
schemaname  | public
tablename   | employees
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f
Time: 2,498 ms

Тут мы видим табличку employees и её владельца postgres. То есть представление смогло нам показать имена вместо OID. Чуть позже разберем как сработало это представление.

Просмотр информации из системного каталога с помощью psql

Утилита psql может упростить работу с системным каталогом. Есть следующие команды:

  • \dt — таблицы;
  • \dv — представления;
  • \dn — схемы;
  • \df — функции;
  • \dfS — служебные функции.

К каждой команде можно добавить +, например \dt+ для получения дополнительной информации.

Чтобы посмотреть свойства определенного объекта, например представления top_managers можно воспользоваться командой \d <имя объекта>. Тут также можно использовать + для получения дополнительной информации:

postgres@data_catalog=# \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;

В качестве дополнительной информации у представления показан запрос, который это представление формирует!

Когда мы смотрим список системных функций можно использовать шаблон имени, чтобы отфильтровать список:

postgres@data_catalog=# \dfS pg*size
                                  List of functions
   Schema   |          Name          | Result data type | Argument data types | Type
------------+------------------------+------------------+---------------------+------
 pg_catalog | pg_column_size         | integer          | "any"               | func
 pg_catalog | pg_database_size       | bigint           | name                | func
 pg_catalog | pg_database_size       | bigint           | oid                 | func
 pg_catalog | pg_indexes_size        | bigint           | regclass            | func
 pg_catalog | pg_relation_size       | bigint           | regclass            | func
 pg_catalog | pg_relation_size       | bigint           | regclass, text      | func
 pg_catalog | pg_table_size          | bigint           | regclass            | func
 pg_catalog | pg_tablespace_size     | bigint           | name                | func
 pg_catalog | pg_tablespace_size     | bigint           | oid                 | func
 pg_catalog | pg_total_relation_size | bigint           | regclass            | func
(10 rows)

Чтобы посмотреть на саму функцию, можно использовать \sf <имя функции>:

postgres@data_catalog=# \sf pg_tablespace_size(oid)
CREATE OR REPLACE FUNCTION pg_catalog.pg_tablespace_size(oid)
 RETURNS bigint
 LANGUAGE internal
 PARALLEL SAFE STRICT
AS $function$pg_tablespace_size_oid$function$

Дополнительно можно установить переменной ECHO_HIDDEN значение on, чтобы получать информацию о выполняемых командах psql:

postgres@data_catalog=# \set ECHO_HIDDEN on

postgres@data_catalog=# \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 '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','s','')
      AND n.nspname !~ '^pg_toast'
  AND c.relname OPERATOR(pg_catalog.~) '^(employees)$' COLLATE pg_catalog.default
  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)

postgres@data_catalog=# \set ECHO_HIDDEN off

То есть мы увидим не только результат команды, но и тот запрос, который выдал нам этот результат.

Если понравилась статья, подпишись на мой канал в  VK  или  Telegram .