Схемы и путь поиска
Предусловие: изучен модуль «Базы данных и их управление».
В этом разделе мы рассмотрим организацию пространства имен внутри базы данных – схемы, их управление, а также механизм поиска объектов (search_path).
Схемы
Кластер состоит из баз данных. База данных — хранилище объектов, например таблиц. У каждого объекта есть имя и каждый объект обязательно принадлежит той или иной схеме.
Полное имя объекта состоит из имени схемы, которой принадлежит объект, и через точку имени объекта. Например, acc.income — полное имя объекта income в схеме acc.
Имя объекта без указания схемы является неполным, и в базе данных может иметься несколько абсолютно разных объектов с одинаковыми именами, но они обязательно будут расположены в разных схемах. Схема определяет пространство имен.
Преимущество схем заключается в возможности распределения разных объектов с одинаковыми именами по разным схемам, тематически связанным, например, с различными приложениями или пользователями. Тем не менее пользователь и схема — разные понятия, и их имена вовсе не обязаны совпадать.
Во многих случаях бывает удобно создавать для пользователей их индивидуальные схемы с именами, совпадающими с именами пользователей. Но в общем такого требования нет.
Еще одно преимущество схем состоит в том, что у схем имеются права, которые могут разрешать создание в схемах объектов и использование этих объектов. Пользователи, не имеющие прав на использование объектов в какой-либо схеме, не смогут получить к этим объектам доступ даже при наличии привилегий на сами объекты.
Исходный список схем
Создадим базу данных sch_db и посмотрим, какие схемы в ней существуют по умолчанию:
postgres@postgres=# CREATE DATABASE sch_db;
CREATE DATABASE
postgres@postgres=# \c sch_db
You are now connected to database "sch_db" as user "postgres".
Выведем список всех схем метакомандой \dn (без системных):
postgres@sch_db=# \dn
Список схем
Имя | Владелец
--------+-------------------
public | pg_database_owner
(1 row)
Стандарт SQL требует наличие схемы public для размещения в ней объектов общего пользования. В ранних версиях PostgreSQL любой пользователь мог создать объект, например таблицу. Однако сейчас это может сделать исходно лишь владелец базы данных.
Выведем список всех схем, включая системные (метакоманда \dnS):
postgres@sch_db=# \dnS
Список схем
Имя | Владелец
--------------------+-------------------
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
public | pg_database_owner
(4 rows)
Информация о схемах содержится в таблице pg_namespace системного каталога.
Системные схемы:
- pg_catalog — схема для объектов системного каталога, содержащего метаданные о всех имеющихся объектах
- information_schema — схема с метаданными об имеющихся объектах, соответствующая стандарту SQL
- pg_toast — схема для специальных таблиц, хранящих значения полей, не помещающихся на обычные страницы данных
Создание схемы
Команда CREATE SCHEMA создает новую схему в базе данных. Имя новой схемы должно отличаться от любых существующих в базе схем. Стандарт SQL обязывает владельца схемы также владеть и всеми объектами в ней. Но в PostgreSQL в схеме могут быть объекты с владельцами, отличающимися от владельца схемы.
Создадим новую схему work и назначим ее владельцем пользователя student:
postgres@sch_db=# CREATE SCHEMA work AUTHORIZATION student;
CREATE SCHEMA
postgres@sch_db=# \dn
Список схем
Имя | Владелец
-------+-------------------
public | pg_database_owner
work | student
(2 rows)
Для создания схемы необходимо иметь право на создание объектов в базе данных.
Создание объектов в схеме
Переключимся на пользователя student и попробуем создать таблицу:
postgres@sch_db=# \c - student
You are now connected to database "sch_db" as user "student".
student@sch_db=> CREATE TABLE zp (tabno integer, salary numeric(10,2));
ERROR: permission denied for schema public
LINE 1: CREATE TABLE zp (tabno integer, salary numeric(10,2));
^
Укажем полное имя – создадим таблицу в схеме work (владельцем которой является student):
student@sch_db=> CREATE TABLE work.zp (tabno integer, salary numeric(10,2));
CREATE TABLE
Для создания объектов в схеме необходимо обладать соответствующим правом. Исходно лишь владелец схемы и обладающие правами суперпользователя могут создавать объекты в схемах, включая схему public. Если право на создание объектов в схеме имеется, то создать новый объект в ней можно, указав полное имя создаваемого объекта с этой схемой. Если не указывать полное имя объекта, то схема, в которой будет произведена попытка создания объекта, определяется автоматически на основе значения параметра настройки search_path.
Этот параметр определяет последовательность просмотра схем при доступе к объекту по неполному имени, и он же определяет схему, в которой будет создаваться новый объект. В параметре search_path содержатся перечисленные через запятую имена схем, в которых последовательно слева направо будут искать требуемый объект при доступе по неполному имени. Первая схема, в которой обнаружится объект с искомым неполным именем, будет использована при доступе к объекту. При создании объекта, имя которого указано без схемы, будет подставлена первая существующая схема.
В примере создается таблица zp без указания схемы, поэтому по search_path определяется, что существует схема public. Поэтому производится попытка создать таблицу public.zp. Но пользователь student не имеет прав на создание объектов в ней. Следующая попытка создает таблицу zp в схеме work, которой при ее создании был назначен владельцем student. В результате таблица work.zp успешно создается.
Путь поиска
Посмотрим текущее значение search_path:
student@sch_db=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
Настройка параметра search_path по умолчанию: "$user", public. Соответственно вначале проверяется наличие схемы с именем, совпадающим с именем пользователя в сеансе. Если таковая отсутствует, происходит обращение к схеме public.
Узнать, в какой схеме при данных настройках search_path будет создан объект, можно функцией current_schema():
student@sch_db=> SELECT current_schema();
current_schema
----------------
public
(1 row)
Параметр search_path сессионный и может быть установлен в любой сессии:
student@sch_db=> \dconfig+ search_path
List of configuration parameters
-[ RECORD 1 ]-----+----------------
Parameter | search_path
Value | "$user", public
Type | string
Context | user
Access privileges |
Реальный путь поиска
Проведем эксперимент. Переключимся на суперпользователя и переименуем схему work в student:
student@sch_db=> \c - postgres
postgres@sch_db=# ALTER SCHEMA work RENAME TO student;
ALTER SCHEMA
Теперь схема с именем student существует. В таком случае при существующей настройке значения search_path схема с именем пользователя будет просматриваться сначала.
Проверим, как изменится current_schema() при подключении от пользователя student:
postgres@sch_db=# \c - student
student@sch_db=> SELECT current_schema();
current_schema
----------------
student
(1 row)
Это подтверждается вызовом функции current_schema() — она показывает имя той схемы, в которой будут создаваться новые объекты, так как это первая существующая схема в пути поиска.
Другая важная функция — current_schemas(). Она выводит реальный набор схем, которые будут просматриваться в поиске объекта, указанного неполным именем. Если параметр этой функции установлен true, то будут показаны и системные схемы:
student@sch_db=> SELECT current_schemas(true);
current_schemas
-----------------------------
{pg_catalog,student,public}
(1 row)
Схемы для временных таблиц
Работа с временными таблицами — особый случай. Эти таблицы создаются на срок жизни сессии или транзакции. Потом они автоматически удаляются. Временные таблицы не защищены журналом WAL и не кешируются в буферном кеше. Все это делается для максимальной скорости доступа к временным таблицам. С этой же целью создаются специальные схемы, просматриваемые первыми, для хранения временных таблиц. Когда создается временная таблица, то именно к ней должен осуществляться доступ по неполному имени. Соответственно схема, которой принадлежит временная таблица, должна быть в пути поиска на первом месте.
Имена схем для временных объектов назначаются автоматически и формируются следующим образом: pg_temp_#, где # является автоматически назначенным номером. Так, в примере выше временная таблица tmp_tab была создана в схеме pg_temp_6.
Посмотрим реальный путь поиска:
student@sch_db=> SELECT current_schemas(true);
current_schemas
-----------------------------
{pg_catalog,student,public}
(1 row)
Создадим временную таблицу:
student@sch_db=> CREATE TEMP TABLE tmp_tab AS SELECT now() AS timepoint;
SELECT 1
Проверим, в какой схеме она создалась:
student@sch_db=> \dt tmp_tab
Список отношений
Схема | Имя | Тип | Владелец
-----------+---------+---------+----------
pg_temp_6 | tmp_tab | таблица | student
(1 row)
Снова посмотрим реальный путь поиска – схема временных таблиц теперь на первом месте:
student@sch_db=> SELECT current_schemas(true);
current_schemas
---------------------------------------
{pg_temp_6,pg_catalog,student,public}
(1 row)
Для удобства обращения к временным таблицам имеется специальный псевдоним pg_temp:
student@sch_db=> SELECT * FROM pg_temp.tmp_tab;
timepoint
-------------------------------
2024-10-12 18:16:20.861332+03
(1 row)
Удаление схем
Суперпользователь или владелец схемы может ее удалить. Если в схеме имеются объекты, то простой команды DROP SCHEMA недостаточно и необходимо добавить ключевое слово CASCADE. При этом и схема, и объекты в ней будут безвозвратно удалены.
Посмотрим текущий список схем:
student@sch_db=> \dn
Список схем
Имя | Владелец
---------+-------------------
public | pg_database_owner
student | student
(2 rows)
Попробуем удалить схему student:
student@sch_db=> DROP SCHEMA student;
ERROR: cannot drop schema student because other objects depend on it
DETAIL: table zp depends on schema student
HINT: Use DROP ... CASCADE to drop the dependent objects too.
В схеме student есть объекты (таблица zp), поэтому команда DROP SCHEMA без дополнительных опций не выполняется. Воспользуемся CASCADE, чтобы удалить схему вместе со всеми зависимыми объектами:
student@sch_db=> DROP SCHEMA student CASCADE;
NOTICE: drop cascades to table zp
DROP SCHEMA