Схемы

Дарья Барышева

Обзор

Схема — это логическое объединение сущностей внутри одной базы данных. В отличие от БД, схемы разделены не так жестко: пользователи могут обращаться к нескольким схемам одновременно (при наличии соответствующих прав). Схемы могут быть полезны для решения следующих задач:

  • Организация одновременного доступа нескольких пользователей к одной базе данных.

  • Разбиение базы данных на несколько логических групп. Это может быть особенно полезно при настройке прав доступа к различным объектам БД.

  • Выделение различных схем под сторонние приложения — так, чтобы не возникало конфликтов с именами объектов в различных схемах.

РЕКОМЕНДАЦИЯ
Обратите внимание, что допускается использовать объекты одного и того же типа (например, таблицы) с одинаковым именем в одной БД — при условии, что эти объекты определены в различных схемах.

Приведенная ниже таблица описывает схемы, создаваемые по умолчанию в базе данных ADB.

Схемы, используемые по умолчанию
Название Описание

arenadata_toolkit

Хранит настройки ADB и информацию о распределении таблиц и выполненных операциях

diskquota

Используется ADB-расширением Diskquota. Это расширение предназначено для ограничения дискового пространства, выделяемого под схемы и роли

gp_toolkit

Служебная схема, содержащая внешние таблицы, представления и функции, к которым можно обратиться с помощью SQL-команд — для просмотра логов и других системных метрик (включая размеры таблиц, bloat, индексы, ресурсные очереди, spill-файлы и так далее)

information_schema

Состоит из стандартизированного набора представлений, содержащих информацию об объектах базы данных. Эти представления извлекают информацию из таблиц системного каталога в соответствии со стандартом ANSI SQL 2008

kadb

Используется в Kafka to ADB Connector

madlib

Хранит информацию о функциях библиотеки Apache Madlib

pg_aoseg

Хранит служебную информацию о таблицах append-optimized. Схема используется для внутренних нужд СУБД

pg_bitmapindex

Хранит служебную информацию о bitmap-индексах. Схема используется для внутренних нужд СУБД

pg_catalog

Содержит полную информацию об объектах БД: таблицах системного каталога, встроенных типах данных, функциях и операторах

pg_toast

Хранит большие объекты, объем которых превышает размер страницы (TOAST, Oversized-Attribute Storage Technique). Схема используется для внутренних нужд СУБД

public

Схема, использумая по умолчанию при создании новых объектов БД — за исключением случаев, когда явно указывается иная схема (или изменено значение schema search path). Все роли (пользователи) имеют привилегии CREATE и USAGE в схеме public
ВНИМАНИЕ
Не рекомендуется удалять либо изменять схемы, приведенные выше.

Просмотр существующих схем

Для получения списка схем в текущем ADB-кластере необходимо выполнить следующие шаги:

  1. Подключиться к Master-серверу ADB (например, через SSH) под пользователем gpadmin, который создается по умолчанию:

    $ sudo su - gpadmin

  2. Подключиться к БД через psql (или иную клиентскую программу):

    $ psql adb

    Результат команды:

    psql (9.4.24)
Type "help" for help.

  3. Выполнить одну из следующих команд:

    • Сделать выборку из таблицы pg_catalog.pg_namespace, содержащей данные обо всех схемах БД.

      SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;

      Результат команды:

            nspname       | nspowner |              nspacl
--------------------+----------+----------------------------------
 arenadata_toolkit  |       10 | {gpadmin=UC/gpadmin}
 diskquota          |       10 |
 gp_toolkit         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 information_schema |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 kadb               |       10 |
 madlib             |       10 |
 pg_aoseg           |       10 |
 pg_bitmapindex     |       10 |
 pg_catalog         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 pg_toast           |       10 |
 public             |       10 | {gpadmin=UC/gpadmin,=UC/gpadmin}
(11 rows)

    • Сделать выборку из таблицы information_schema.schemata. По сравнению с pg_catalog.pg_namespace, эта таблица хранит данные о схемах БД в соответствии со стандартом ANSI SQL 2008.

      SELECT * FROM information_schema.schemata ORDER BY schema_name;

      Результат команды:

       catalog_name |    schema_name     | schema_owner | default_character_set_catalog | default_character_set_schema | default_character_set_name | sql_path
--------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+----------
 adb          | arenadata_toolkit  | gpadmin      |                               |                              |                            |
 adb          | diskquota          | gpadmin      |                               |                              |                            |
 adb          | gp_toolkit         | gpadmin      |                               |                              |                            |
 adb          | information_schema | gpadmin      |                               |                              |                            |
 adb          | kadb               | gpadmin      |                               |                              |                            |
 adb          | madlib             | gpadmin      |                               |                              |                            |
 adb          | pg_aoseg           | gpadmin      |                               |                              |                            |
 adb          | pg_bitmapindex     | gpadmin      |                               |                              |                            |
 adb          | pg_catalog         | gpadmin      |                               |                              |                            |
 adb          | pg_toast           | gpadmin      |                               |                              |                            |
 adb          | public             | gpadmin      |                               |                              |                            |
(11 rows)

    • При работе с терминальным клиентом psql для вывода списка схем БД можно воспользоваться мета-командой \dnS+.

      \dnS+

      Результат команды:

                                                       List of schemas
        Name        |  Owner  | Access privileges  |                         Description
--------------------+---------+--------------------+-------------------------------------------------------------
 arenadata_toolkit  | gpadmin | gpadmin=UC/gpadmin |
 diskquota          | gpadmin |                    |
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 information_schema | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 kadb               | gpadmin |                    |
 madlib             | gpadmin |                    |
 pg_aoseg           | gpadmin |                    | Reserved schema for Append Only segment list and eof tables
 pg_bitmapindex     | gpadmin |                    | Reserved schema for internal relations of bitmap indexes
 pg_catalog         | gpadmin | gpadmin=UC/gpadmin+| system catalog schema
                    |         | =U/gpadmin         |
 pg_toast           | gpadmin |                    | reserved schema for TOAST tables
 public             | gpadmin | gpadmin=UC/gpadmin+| standard public schema
                    |         | =UC/gpadmin        |
(11 rows)

Создание схемы

Чтобы создать новую схему базы данных, необходимо использовать команду CREATE SCHEMA и затем указать имя схемы.

В следующем примере создается новая схема test_schema (в текущей базе данных adb).

CREATE SCHEMA test_schema;

Результат команды:

CREATE SCHEMA

Если теперь воспользоваться любой из команд для вывода списка схем БД, результат будет следующим:

adb=# \dnS+
                                                 List of schemas
        Name        |  Owner  | Access privileges  |                         Description
--------------------+---------+--------------------+-------------------------------------------------------------
 arenadata_toolkit  | gpadmin | gpadmin=UC/gpadmin |
 diskquota          | gpadmin |                    |
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 information_schema | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 kadb               | gpadmin |                    |
 madlib             | gpadmin |                    |
 pg_aoseg           | gpadmin |                    | Reserved schema for Append Only segment list and eof tables
 pg_bitmapindex     | gpadmin |                    | Reserved schema for internal relations of bitmap indexes
 pg_catalog         | gpadmin | gpadmin=UC/gpadmin+| system catalog schema
                    |         | =U/gpadmin         |
 pg_toast           | gpadmin |                    | reserved schema for TOAST tables
 public             | gpadmin | gpadmin=UC/gpadmin+| standard public schema
                    |         | =UC/gpadmin        |
 test_schema        | gpadmin |                    |
(12 rows)

Schema search path

Для доступа к объектам БД можно использовать две основные формы записи имен: соответствующую ASCII-стандарту запись database.schema.object или более короткий вариант schema.object. Вторая форма предпочтительна, поскольку в рамках одного соединения можно обращаться лишь к одной БД. Но существует также третий вариант записи, который не требует явного указания имени схемы и позволяет оперировать лишь именами объектов (например, именами таблиц). Это возможно благодаря использованию пути для поиска схем — schema search path.

Путь schema search path настраивается через параметр search_path. Этот параметр определяет порядок, в котором будут просматриваться существующие схемы при поиске объектов БД. Фактически он представляет собой список схем, разделенных запятыми. Запрашиваемый объект БД ищется в первой схеме списка, в случае неудачного поиска — во второй схеме, и так далее. Если объект БД не найден ни в одной из схем списка, возвращается ошибка — даже в случае если объект присутствует в других схемах БД. Первая схема в списке является схемой по умолчанию — default-схемой (ее также называют current-схемой). Это не только первая схема, в которой выполняется поиск, но и схема, в которой создаются новые объекты БД, если имя схемы для них явно не определено.

Для вывода текущего значения пути schema search path можно использовать следующую команду:

SHOW search_path;

По умолчанию команда возвращает следующее значение:

   search_path
-----------------
 "$user", public
(1 row)

Первый элемент списка "$user" указывает на схему с тем же названием, что имя текущего пользователя. При отсутствии схемы это значение игнорируется. Второй элемент списка указывает на схему public. Именно по этой причине схема public используется для всех новых объектов БД по умолчанию. Для вывода схемы default (или current) можно использовать следующий запрос:

SELECT current_schema();

По умолчанию команда возвращает следующее значение:

 current_schema
----------------
 public
(1 row)
ПРИМЕЧАНИЕ
Схема pg_catalog всегда является частью пути schema search path, даже если она явно в нем не указана.

Изменение параметра search_path

Изменение параметра search_path возможно на уровне базы данных (через SQL-команду ALTER DATABASE) и на уровне роли (через SQL-команду ALTER ROLE). Ниже приведена последовательность шагов для первого метода:

  1. Изменить значение параметра search_path на уровне базы данных.

    ALTER DATABASE adb SET search_path TO test_schema,public;

    Результат команды:

    ALTER DATABASE

  2. Выйти из psql (или любого иного используемого клиента) — чтобы перезапустить клиентскую сессию.

    \q

  3. Повторно покдлючиться к базе данных adb.

    $ psql adb

    Результат команды:

    psql (9.4.24)
Type "help" for help.

  4. Создать любую таблицу без явного указания схемы.

    CREATE TABLE test(id1 INT) DISTRIBUTED BY(id1);

    Результат команды:

    CREATE TABLE

  5. Вывести схему, в которой создана таблица test.

    \dt test

    Убедиться, что это схема test_schema, а не public.

                   List of relations
   Schema    | Name | Type  |  Owner  | Storage
-------------+------+-------+---------+---------
 test_schema | test | table | gpadmin | heap
(1 row)

  6. Вернуть исходное значение параметра search_path и повторно перезапустить клиентскую сессию.

    adb=# ALTER DATABASE adb SET search_path TO "$user",public;
ALTER DATABASE
adb=# \q
ПРИМЕЧАНИЕ

Изменить значение search_path возможно также на уровне сессии — с помощью запроса SET search_path TO <new_path_value>; (где <new_path_value> — это новое значение параметра, например, test_schema,public). Тем не менее, мы рекомендуем изменять этот параметр на уровне БД или роли, чтобы он не сбрасывался при каждом перезапуске сессии.
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней