Схемы

Андрей Аксенов

Contents

Обзор
Схемы по умолчанию
Создание новых схем
Изменение схем
Просмотр информации о схемах
- Метакоманды psql
- SQL-команды
Путь поиска схемы
Удаление схем

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

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

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

После инициализации кластера Greengage DB доступны несколько схем по умолчанию. Например, административная схема gp_toolkit позволяет выполнять запросы к системным каталогам, лог-файлам и среде выполнения для получения информации о состоянии системы. public — схема, в которой по умолчанию создаются новые объекты базы данных.

Вы также можете создавать новые схемы, которые помогают при решении следующих задач:

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

ПРИМЕЧАНИЕ

Обратите внимание на то, что схемы не могут быть вложенными.

Ниже перечислены схемы, которые создаются в кластере Greengage DB по умолчанию:

public

Схема по умолчанию для новых объектов базы данных, если не указана другая схема или не изменен путь поиска схемы. Все роли базы данных имеют привилегии CREATE и USAGE для схемы public. Если вы создаете новую схему, необходимо выдать привилегии вашим пользователям для доступа к этой схеме.
gp_toolkit

Административная схема, позволяющая выполнять запросы к системным каталогам, лог-файлам и среде выполнения для получения информации о состоянии системы.
pg_catalog

Содержит информацию об объектах базы данных: таблицах системного каталога, встроенных типах данных, функциях и операторах. Обратите внимание, что эта схема всегда включена в путь поиска схемы, даже если не указана явно.
information_schema

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

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

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

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

Предварительные требования

Для выполнения команд, описанных в последующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql.

Этот раздел показывает, как создавать новые схемы.

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

CREATE DATABASE books_store;
\c books_store

Для создания схем в этой базе данных используйте команду CREATE SCHEMA:

CREATE SCHEMA catalog;
CREATE SCHEMA sales;

При необходимости вы можете добавить комментарии к созданным схемам с помощью команды COMMENT:

COMMENT ON SCHEMA catalog IS 'Schema for storing book metadata';
COMMENT ON SCHEMA sales IS 'Schema for storing sales-related data';

Чтобы изменить созданные схемы, используйте команду ALTER SCHEMA.

Данная команда создает новую роль:

CREATE ROLE bob WITH LOGIN;

Следующая команда назначает созданную роль владельцем схемы sales:

ALTER SCHEMA sales OWNER TO bob;

В этом разделе описано, как получить информацию о схемах с помощью метакоманд psql.

Для просмотра пользовательских схем выполните метакоманду \dn:

\dn+

Результат должен выглядеть следующим образом:

                                  List of schemas
    Name    |  Owner  | Access privileges  |              Description
------------+---------+--------------------+---------------------------------------
 catalog    | gpadmin |                    | Schema for storing book metadata
 gp_toolkit | gpadmin | gpadmin=UC/gpadmin+|
            |         | =U/gpadmin         |
 public     | gpadmin | gpadmin=UC/gpadmin+| standard public schema
            |         | =UC/gpadmin        |
 sales      | bob     |                    | Schema for storing sales-related data

Столбец Access privileges для новых схем пуст, что означает, что привилегии не настроены. Смотрите раздел Роли и привилегии, чтобы узнать, как предоставить пользователям доступ к этим схемам.

Чтобы увидеть все схемы базы данных, используйте метакоманду \dnS:

\dnS+

Результат должен выглядеть следующим образом:

                                                 List of schemas
        Name        |  Owner  | Access privileges  |                         Description
--------------------+---------+--------------------+-------------------------------------------------------------
 catalog            | gpadmin |                    | Schema for storing book metadata
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 information_schema | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/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        |
 sales              | bob     |                    | Schema for storing sales-related data

Следующие команды показывают, как увидеть информацию о схемах с помощью запросов к системным каталогам.

Получить информацию о схемах из таблицы pg_catalog.pg_namespace:

SELECT *
FROM pg_catalog.pg_namespace
ORDER BY nspname;

Результат должен выглядеть следующим образом:

      nspname       | nspowner |              nspacl
--------------------+----------+----------------------------------
 catalog            |       10 |
 gp_toolkit         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 information_schema |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 pg_aoseg           |       10 |
 pg_bitmapindex     |       10 |
 pg_catalog         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 pg_toast           |       10 |
 public             |       10 | {gpadmin=UC/gpadmin,=UC/gpadmin}
 sales              |    57418 |

Получить информацию о схемах из таблицы information_schema.schemata:

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
--------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+----------
 books_store  | catalog            | gpadmin      |                               |                              |                            |
 books_store  | gp_toolkit         | gpadmin      |                               |                              |                            |
 books_store  | information_schema | gpadmin      |                               |                              |                            |
 books_store  | pg_aoseg           | gpadmin      |                               |                              |                            |
 books_store  | pg_bitmapindex     | gpadmin      |                               |                              |                            |
 books_store  | pg_catalog         | gpadmin      |                               |                              |                            |
 books_store  | pg_toast           | gpadmin      |                               |                              |                            |
 books_store  | public             | gpadmin      |                               |                              |                            |
 books_store  | sales              | bob          |                               |                              |                            |

Greengage DB предоставляет три способа доступа к объектам базы данных:

database.schema.object — в этом формате путь к объекту включает имена базы данных и схемы. Данный формат поддерживается для совместимости с ANSI SQL, поскольку можно работать только с одной базой данных одновременно. Если вы используете этот формат для доступа к объекту базы данных, название базы данных должно совпадать с той, к которой вы в данный момент подключены.
schema.object — в этом формате путь к объекту содержит название схемы.
object — в этом формате название схемы опускается. Данный синтаксис использует путь поиска схемы (schema search path) для нахождения нужного объекта.

Путь поиска — это упорядоченный список схем, разделенных запятыми, который используется для поиска объектов в том случае, когда схема не указана явно. Объект ищется последовательно в схемах, указанных в списке: сначала в первой, затем во второй, и так далее. Если объект не найден в ни одной из схем, возвращается ошибка.

Ниже приведен пример пути поиска схемы:

catalog, sales, public

В этом случае объект ищется в следующем порядке: catalog → sales → public.

ПРИМЕЧАНИЕ

Схема pg_catalog всегда является частью пути поиска схемы, даже если она явно в нем не указана.

Схема, указанная первой в пути поиска, является текущей. Она используется при создании новых объектов в том случае, если схема явно не указана.

Для настройки пути поиска используйте параметр search_path. Путь поиска схемы можно задать на разных уровнях с приоритетами, применяемыми в следующем порядке — от наименьшего к наибольшему:

Глобально (на уровне кластера) с помощью соответствующего параметра конфигурации сервера.
На уровне базы данных.
Для указанной роли.
Во время выполнения, например, в рамках сессии.

Чтобы узнать текущий путь поиска схемы, используйте команду SHOW:

SHOW search_path;

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

  search_path
----------------
 "$user",public

"$user" указывает на схему, название которой совпадает с именем текущего пользователя. Если такая схема не существует, это значение игнорируется.
public — это предопределенная схема, используемая по умолчанию для вновь создаваемых объектов базы данных.

Чтобы узнать текущую схему, используйте функцию current_schema():

SELECT current_schema();

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

 current_schema
----------------
 public

Это означает, что схемы с именем, совпадающим с именем текущего пользователя, не существует.

Чтобы узнать путь поиска схемы, используемый на уровне кластера, используйте команду gpconfig -s:

$ gpconfig -s search_path

Результат может выглядеть следующим образом:

Values on all segments are consistent
GUC          : search_path
Master  value: "$user",public
Segment value: "$user",public

Чтобы изменить путь поиска, используйте команду gpconfig -c:

$ gpconfig -c search_path -v '"$user"'

Затем перезагрузите конфигурацию с помощью команды gpstop, чтобы применить изменения:

$ gpstop -u

Чтобы задать путь поиска для базы данных, используйте команду ALTER DATABASE:

ALTER DATABASE books_store SET search_path TO catalog, sales, public;

Чтобы применить изменения, переподключитесь к базе данных с помощью метакоманды \c:

\c books_store

Затем проверьте путь поиска с помощью SHOW:

SHOW search_path;

Результат должен выглядеть следующим образом:

      search_path
------------------------
 catalog, sales, public

Первая схема catalog — это текущая схема, используемая для создания новых объектов. Создайте новую таблицу books, не указывая имя схемы:

CREATE TABLE books
(
    book_id SERIAL
)
    DISTRIBUTED BY (book_id);

Создайте таблицу customers с явным указанием схемы sales:

CREATE TABLE sales.customers
(
    customer_id SERIAL
)
    DISTRIBUTED BY (customer_id);

Проверьте, что для обеих таблиц используются необходимые схемы, с помощью метакоманды \dt:

\dt

Результат должен выглядеть следующим образом:

                List of relations
 Schema  |   Name    | Type  |  Owner  | Storage
---------+-----------+-------+---------+---------
 catalog | books     | table | gpadmin | heap
 sales   | customers | table | gpadmin | heap

Перед установкой пути поиска схемы создайте роль и выдайте ей привилегию USAGE на схему:

CREATE ROLE alice WITH LOGIN;
GRANT USAGE ON SCHEMA sales TO alice;

Чтобы задать путь поиска схемы для роли, используйте команду ALTER ROLE:

ALTER ROLE alice SET search_path TO sales;

Для применения изменений переподключитесь к базе данных под данной ролью, используя метакоманду \c:

\c books_store alice

Затем проверьте путь поиска с помощью SHOW:

SHOW search_path;

Результат должен выглядеть следующим образом:

 search_path
-------------
 sales

Обратите внимание, что путь поиска роли для alice имеет приоритет над путем поиска для базы данных books_store, который установлен в разделе Установка пути поиска для базы данных.

Вы можете задать путь поиска схемы во время выполнения с помощью команды SET. Например, вы можете изменить путь поиска на уровне сессии.

Переподключитесь к базе данных books_store под пользователем gpadmin:

\c books_store gpadmin

Затем используйте команду SET для изменения пути поиска:

SET search_path TO public;

Проверьте результат с помощью команды SHOW:

SHOW search_path;

Вы можете сбросить путь поиска на значение по умолчанию с помощью команды RESET:

RESET search_path;

Если повторно выполнить команду SHOW search_path, вывод должен показать, что путь поиска сброшен на значение, установленное для базы данных books_store:

      search_path
------------------------
 catalog, sales, public

По умолчанию схема должна быть пустой, прежде чем ее можно будет удалить. Перед удалением схемы необходимо вручную удалить ее объекты:

DROP TABLE catalog.books;
DROP SCHEMA catalog;

Для удаления схемы и всех содержащихся в ней объектов используйте команду DROP SCHEMA с параметром CASCADE:

DROP SCHEMA sales CASCADE;

Табличные пространства

Типы таблиц

Схемы

Обзор

Схемы по умолчанию

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

Изменение схем

Просмотр информации о схемах

Метакоманды psql

SQL-команды

Путь поиска схемы

Обзор

Путь поиска по умолчанию

Установка пути поиска глобально

Установка пути поиска для базы данных

Установка пути поиска для роли

Изменение пути поиска во время выполнения

Удаление схем