Узнаем что такое системный каталог в postgresql, зачем он нужен и как с ним работать. Узнаем как получать информацию из системного каталога с помощью запросов и psql утилиты. Дополнительно вы можете почитать о системном каталоге здесь.
Системный каталог 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
То есть мы увидим не только результат команды, но и тот запрос, который выдал нам этот результат.
