Системный каталог
В этом разделе мы рассмотрим системный каталог – хранилище метаданных о всех объектах кластера. Вы узнаете, какие данные содержатся в системных таблицах, как с ними работать с помощью reg-типов, а также как psql взаимодействует с системным каталогом для выполнения своих метакоманд.
В данном разделе для удобства восприятия некоторые выводы команд представлены в переводе на русский язык. В реальной системе вы увидите англоязычные сообщения.
Содержимое системного каталога
В документации PostgreSQL таблицы, содержащие данные об объектах баз данных кластера (метаданные), называются системными каталогами. Например, pg_class — это системный каталог, содержащий свойства отношений (таблиц, индексов и тому подобное), а pg_attribute — системный каталог с данными о столбцах всех отношений.
Для упрощения будем далее именовать системным каталогом схему pg_catalog, которой принадлежат все таблицы и представления с метаданными.
Когда приложению требуется получить информацию о каком-либо объекте базы данных, это делается с помощью обычного SELECT-выражения. Но изменять метаданные командами DML (Data Manipulation Language — Язык манипулирования данными) нельзя. Вместо этого используются команды DDL (Data Definition Language — язык определения данных). Это CREATE, ALTER, DROP и тому подобное.
Схема pg_catalog содержит исчерпывающие метаданные кластера баз данных PostgreSQL, но стандарт SQL предписывает иметь специальную схему information_schema для метаданных, а также описывает наименования и структуру объектов в ней. PostgreSQL поддерживает information_schema.
Большинство объектов в pg_catalog описывают объекты текущей базы данных, но в pg_catalog есть описание и глобальных объектов, например метаданные ролей и самих баз данных кластера.
Структура таблиц системного каталога
Информация о схемах хранится в таблице pg_namespace. Посмотрим ее структуру:
student@student=> \d pg_namespace
Таблица "pg_catalog.pg_namespace"
Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию
----------+-----------+--------------------+-------------------+--------------
oid |oid | | not null |
nspname | name | | not null |
nspowner | oid | | not null |
nspacl | aclitem[] |
Индексы:
"pg_namespace_oid_index" PRIMARY KEY, btree (oid)
"pg_namespace_nspname_index" UNIQUE CONSTRAINT, btree (nspname)
Традиционно первые три символа в именах столбцов обычно являются аббревиатурой имени таблицы системного каталога. Здесь nsp — аббревиатура слова namespace. Это соглашение не распространяется на все имена столбцов, например oid.
В PostgreSQL большинство стоблцов в таблицах системного каталога именуется с помощью аббревиатуры, построенной из имени таблицы. Например, метаданные баз данных — таблица pg_database:
student=> \d pg_database
Таблица "pg_catalog.pg_database"
Столбец | Тип | Правило сортировки | Допустимость NULL |
----------------+-----------+--------------------+-------------------+-
oid | oid | | not null |
datname | name | | not null |
datdba | oid | | not null |
encoding | integer | | not null |
datlocprovider | "char" | | not null |
datistemplate | boolean | | not null |
datallowconn | boolean | | not null |
datconnlimit | integer | | not null |
datfrozenxid | xid | | not null |
datminmxid | xid | | not null |
dattablespace | oid | | not null |
datcollate | text | C | not null |
datctype | text | C | not null |
daticulocale | text | C | |
datcollversion | text | C | |
datacl | aclitem[] | | |
Индексы:
"pg_database_oid_index" PRIMARY KEY, btree (oid), табл. пространство "pg_global"
"pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), табл. пространство "pg_global"
Табличное пространство: "pg_global"
Большинство столбцов именуются, начиная с dat — аббревиатура имени таблицы. Обратите внимание на табличное пространство pg_global — таблица pg_database описывает глобальные объекты.
Тип OID и reg-типы
Таблицы системного каталога проиндексированы по полю OID (Object identifiers), таблицы часто связывают в запросах по этому полю. Для некоторых таблиц системного каталога можно преобразовывать OID в имя объекта и наоборот с помощью специальных reg-типов:
student=> \dT reg*
Список типов данных
Схема | Имя | Описание
------------+---------------+--------------------------------------
pg_catalog | regclass | registered class
pg_catalog | regcollation | registered collation
pg_catalog | regconfig | registered text search configuration
pg_catalog | regdictionary | registered text search dictionary
pg_catalog | regnamespace | registered namespace
pg_catalog | regoper | registered operator
pg_catalog | regoperator | registered operator (with args)
pg_catalog | regproc | registered procedure
pg_catalog | regprocedure | registered procedure (with args)
pg_catalog | regrole | registered role
pg_catalog | regtype | registered type
(11 строк)
Пусть необходимо получить список столбцов таблицы. Например:
student=> CREATE TABLE tablo(n int);
student=> SELECT attrelid, attname, atttypid FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'tablo') AND attnum > 0;
attrelid | attname | atttypid
----------+---------+----------
24596 | n | 23
(1 row)
Поле attrelid таблицы pg_attribute (таблица с метаданными столбцов всех таблиц БД) содержит OID таблицы, в строках которой есть эти столбцы. Без reg-преобразования пришлось выполнить подзапрос для определения OID таблицы, а для удобного вывода типа столбца необходим еще один подзапрос или соединение.
Используя reg-типы:
student=> SELECT attrelid::regclass, attname, atttypid::regtype FROM pg_attribute WHERE attrelid = 'tablo'::regclass AND attnum > 0;
attrelid | attname | atttypid
----------+---------+----------
tablo | n | integer
(1 row)
PostgreSQL использует reg-тип таким образом: отбрасывается приставка reg, например regclass -> class, и добавляется префикс pg_, class -> pg_class. Таким образом, для приведения типа OID attrelid к имени таблицы attrelid::regclass была использована таблица pg_class, а для приведения из OID типа atttypid::regtype таблица pg_type.
Работа скрытых запросов в psql
Клиент psql предоставляет удобную возможность увидеть скрытые запросы к объектам системного каталога, выполняемые метакомандами. Для вывода запросов, выполняемых psql, установите значение встроенной переменной psql ECHO_HIDDEN в значение ON. Когда режим вывода запросов не будет далее необходим, переключите переменную в OFF или просто сбросьте ее метакомандой \unset:
student=> \set ECHO_HIDDEN on
student=> \db
********* ЗАПРОС *********
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location" FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************
Список табличных пространств
Имя | Владелец | Расположение
------------+----------+--------------
pg_default | postgres |
pg_global | postgres |
(2 строки)
student=> \set ECHO_HIDDEN off