Определение данных

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

Базовая информация по таблицам

Таблица в реляционной базе данных очень похожа на таблицу на бумаге: она состоит из строк и столбцов. Количество и порядок столбцов фиксированы, и у каждого столбца есть имя. Количество строк переменно — оно отражает, сколько данных хранится в данный момент. SQL не дает никаких гарантий относительно порядка строк в таблице. Когда таблица прочитывается, строки появятся в произвольном порядке, если только сортировка не задана явно. Подробнее это освещается в главе Запросы. Кроме того, SQL не присваивает строкам уникальные идентификаторы, поэтому в таблице может быть несколько полностью идентичных строк. Это следствие математической модели, лежащей в основе реляционных СУБД, но обычно такое дублирование нежелательно. Далее в этой главе мы увидим, как бороться с этой проблемой.

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

QHB включает в себя значительный набор встроенных типов данных, которые подходят для многих приложений. Кроме того, пользователи могут определять свои собственные типы данных. Большинство встроенных типов данных имеет очевидные имена и семантику, поэтому подробное их рассмотрение отложим до главы Типы данных. Некоторые из часто используемых типов данных: integer для целых чисел, numeric для возможно дробных чисел, text для символьных строк, date для дат, time для значений времени суток и timestamp для значений, содержащих и дату, и время.

Для создания таблицы используется, соответственно, команда CREATE TABLE. В этой команде следует указать как минимум имя новой таблицы, имена столбцов и тип данных каждого столбца. Например:

CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);

Это создает таблицу с именем my_first_table с двумя столбцами. Первый столбец называется first_column и имеет тип данных text, второй — second_column с типом integer. Имена таблиц и столбцов соответствуют синтаксису идентификатора, описанному в подразделе Идентификаторы и ключевые слова. Имена типов обычно также являются идентификаторами, но есть некоторые исключения. Обратите внимание, что список столбцов разделен запятыми и заключен в скобки.

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

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

(тип numeric может хранить дробные компоненты, что типично для денежных сумм).

Совет
Когда создается много взаимосвязанных таблиц, разумно выбрать единый шаблон именования таблиц и столбцов. Например, можно решить, использовать ли для имен таблиц существительные в единственном или множественном числе (у обоих вариантов есть свои сторонники).

Существует ограничение на количество столбцов в таблице. В зависимости от типов столбцов оно колеблется от 250 до 1600. Однако определение таблицы с таким количеством столбцов весьма необычно и часто бывает признаком сомнительного дизайна.

Если таблица больше не нужна, ее можно удалить с помощью команды DROP TABLE. Например:

DROP TABLE my_first_table;
DROP TABLE products;

Попытка удалить несуществующую таблицу является ошибкой. Тем не менее в файлах скриптов SQL принято удалять каждую таблицу перед ее созданием, игнорируя любые сообщения об ошибках, чтобы скрипт работал независимо от того, существует таблица или нет. (При необходимости можно воспользоваться вариантом DROP TABLE IF EXISTS, чтобы избежать сообщений об ошибках, но это не стандартный SQL).

Если нужно изменить таблицу, которая уже существует, это рассматривается далее в этой главе в разделе Изменение таблиц.

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

Значения по умолчанию

Столбцу может быть назначено значение по умолчанию. Когда создается новая строка и для некоторых столбцов не указаны значения, эти столбцы будут заполнены соответствующими значениями по умолчанию. Команда манипулирования данными тоже может явно запросить, чтобы для столбца было установлено значение по умолчанию, и ей необязательно знать, что это за значение. (Подробнее команды манипулирования данными рассматриваются в главе Манипулирование данными.

Если значение по умолчанию не объявлено явно, им является значение NULL. Обычно это имеет смысл, поскольку значение NULL может рассматриваться как представляющее неизвестные данные.

В определении таблицы значения по умолчанию перечисляются после типа данных столбца. Например:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

Значением по умолчанию может быть выражение, которое будет вычисляться всякий раз, когда вставляется значение по умолчанию (а не при создании таблицы). Типичный пример: для столбца timestamp по умолчанию задано значение CURRENT_TIMESTAMP, поэтому для него устанавливается время добавления строки. Другим распространенным примером является генерирование «серийного номера» для каждой строки. В QHB это обычно делается примерно так:

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

где функция nextval() предоставляет последовательные значения из объекта последовательности (см. раздел Функции для управления последовательностями). Такое объявление достаточно распространено, поэтому для него есть специальное сокращение:

CREATE TABLE products (
    product_no SERIAL,
    ...
);

Сокращение SERIAL рассматривается в подразделе Последовательные типы.

Генерируемые столбцы

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

Чтобы создать генерируемый столбец, используйте предложение GENERATED ALWAYS AS в команде CREATE TABLE, например:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Чтобы выбрать хранимый вариант генерируемого столбца, следует указать ключевое слово STORED. Более подробную информацию см. на справочной странице команды CREATE TABLE.

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

Рассмотрим различия между столбцом со значением по умолчанию и генерируемым столбцом. Значение столбца по умолчанию вычисляется один раз при первом добавлении строки, если не было предоставлено никакого другого значения; генерируемый столбец обновляется при каждом изменении строки и не может быть переопределен. Значение столбца по умолчанию может не ссылаться на другие столбцы таблицы; генерирующее выражение обычно имеет такие ссылки. В значении по умолчанию могут использоваться изменчивые функции, например random(), или функции, относящиеся к текущему времени; для генерируемых столбцов это не допускается.

К определению генерируемых столбцов и таблиц, включающих генерируемые столбцы, применяется несколько ограничений:

В генерирующем выражении могут вызываться только постоянные функции и никак не могут использоваться подзапросы или ссылки на что-либо, кроме текущей строки.
Генерирующее выражение не может ссылаться на другой сгенерированный столбец.
Генерирующее выражение не может ссылаться на системный столбец, за исключением tableoid.
Генерируемый столбец не может иметь значение по умолчанию или определение идентификации.
Генерируемый столбец не может быть частью ключа разбиения.
Сторонние таблицы могут иметь сгенерированные столбцы. Подробную информацию см. на справочной странице команды CREATE FOREIGN TABLE.
Применительно к наследованию:
- Если родительский столбец является генерируемым столбцом, дочерний столбец тоже должен быть генерируемым, причем тем же генерирующим выражением. В определении дочернего столбца следует опустить предложение GENERATED, поскольку оно будет скопировано из родительского.
- В случае множественного наследования, если один родительский столбец является генерируемым, то все родительские столбцы должны быть генерируемыми, причем тем же генерирующим выражением.
- Если родительский столбец не является генерируемым, дочерний столбец может быть определен и как генерируемый, и как обычный.

Дополнительные ограничения относятся к использованию генерируемых столбцов.

Генерируемые столбцы хранят права доступа отдельно от прав нижележащих базовых столбцов. Таким образом, можно расположить их так, чтобы определенная роль могла читать данные из генерируемого столбца, но не из нижележащих базовых столбцов.
Согласно концепции, генерируемые столбцы обновляются после запуска триггеров BEFORE. Поэтому изменения, внесенные в базовые столбцы в триггере BEFORE, будут отражены в генерируемых столбцах. Однако обращение к генерируемым столбцам в триггерах BEFORE, наоборот, запрещено.

Ограничения

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

Исходя из этого, SQL позволяет определять ограничения для столбцов и таблиц. Ограничения дают возможность сколько угодно управлять данными в таблицах. Если пользователь пытается сохранить в столбце данные, нарушающие ограничение, возникает ошибка. Это происходит, даже если значение пришло из определения значения по умолчанию.

Проверочные ограничения

Проверочное ограничение является наиболее общим типом ограничений. Оно позволяет указать, что значение в определенном столбце должно удовлетворять логическому выражению (значению истинности). Например, чтобы предписать только положительные цены на товары, можно использовать:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

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

Также можно задать ограничению отдельное имя. Это уточняет сообщения об ошибках и позволяет ссылаться на ограничение, когда нужно его изменить. Синтаксис при этом следующий:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Итак, чтобы указать именованное ограничение, используйте ключевое слово CONSTRAINT, за которым следует идентификатор, а затем — определение ограничения. (Если не указать имя ограничения таким образом, система выберет имя за вас.)

Также проверочное ограничение может ссылаться на несколько столбцов. Допустим, вы храните обычную цену и цену со скидкой и хотите, чтобы цена со скидкой была ниже обычной цены:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

Первые два ограничения должны выглядеть знакомо. В третьем используется новый синтаксис. Он не привязан к определенному столбцу, а отображается как отдельный элемент в списке столбцов, разделенных запятыми. Определения столбцов и эти определения ограничений можно перечислить в смешанном порядке.

Можно сказать, что первые два ограничения являются ограничениями столбца, тогда как третье ограничение является ограничением таблицы, потому что оно написано отдельно от любого определения одного столбца. Ограничения столбца тоже можно записать как ограничения таблицы, а вот обратное не всегда возможно, поскольку предполагается, что ограничение столбца относится только к тому столбцу, к которому оно прикреплено. (QHB не применяет это правило, но вы должны следовать ему, если хотите, чтобы определения таблиц работали с другими системами баз данных). Приведенный выше пример можно записать и так:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

или даже:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

Это дело вкуса.

Ограничениям таблицы, как и ограничениям столбцов, можно назначить имена:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

Важно отметить, что проверочное ограничение удовлетворяется, если проверочное выражение имеет значение true или NULL. Поскольку большинство выражений будут иметь значение NULL, если любой из операндов равен NULL, они не будут препятствовать записи значений NULL в ограниченные столбцы. Чтобы гарантировать, что столбец не содержит значений NULL, можно воспользоваться ограничением NOT NULL, описанным в следующем разделе.

Примечание!!!
QHB не поддерживает ограничения CHECK, ссылающиеся на данные таблицы, кроме проверки новой или измененной строки. Хотя проверочное ограничение нарушающее это правило, может показаться работающим в простых тестах, это не гарантирует, что база данных не достигнет состояния, в котором условие ограничения ложно (из-за последующих изменений другой строки (строк)). Это может привести к сбою резервного копирования и перезагрузке базы данных. Сбой при перезагрузке может произойти, даже если полное состояние базы данных согласуется с ограничением, из-за того, что строки загружаются не в том порядке, который будет удовлетворять этому ограничению. Для выражения ограничений между строками и между таблицами, по возможности, используйте ограничения UNIQUE, EXCLUDE или FOREIGN KEY.

Если требуется одноразовая проверка относительно других строк при добавлении строки, а не постоянно поддерживаемая гарантия согласованности, для ее реализации можно использовать пользовательский триггер. (Этот подход позволяет избежать проблемы выгрузки/перезагрузки, поскольку qhb_dump переустанавливает триггеры уже после перезагрузки данных, поэтому во время выполнения выгрузки/перезагрузки проверка выполняться не будет.)

Примечание
QHB предполагает, что условия ограничений CHECK неизменны, то есть они всегда будут давать один и тот же результат для одной и той же входной строки. Это предположение оправдывает выполнение проверки на это ограничение только при добавлении или изменении строки, но не в других случаях. (Вышеприведенное предупреждение о том, что не следует ссылаться на другие данные таблицы является частным случаем этого ограничения).

Примером распространенного способа нарушить это предположение является ссылка на пользовательскую функцию в выражении CHECK, а затем изменение поведения этой функции. QHB не запрещает этого, но не заметит, есть ли в таблице строки, которые теперь нарушают ограничение CHECK. Это может привести к сбою последующего резервного копирования и перезагрузки базы данных. Рекомендуемый способ проведения такого изменения состоит в том, чтобы удалить ограничение (командой ALTER TABLE), скорректировать определение функции и повторно добавить ограничение, тем самым перепроверив его для всех строк таблицы.

Ограничения NOT NULL

Ограничение NOT NULL просто указывает, что столбец не должен принимать значение NULL. Пример синтаксиса:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

Ограничение NOT NULL всегда записывается как ограничение столбца. Ограничение NOT NULL функционально равнозначно созданию проверочного ограничения CHECK (имя_столбца IS NOT NULL), но в QHB создание явного ограничения NOT NULL более эффективно. Недостатком является то, что дать явные имена ограничениям NOT NULL, созданным таким образом, невозможно.

Столбец может иметь более одного ограничения. Просто напишите ограничения одно за другим:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

Порядок не имеет значения — он никак не влияет на то, в каком порядке будут проверяться ограничения.

Ограничение NOT NULL имеет обратное ограничение NULL. Это не означает, что столбец должен иметь значение NULL — в таком случае он бесполезен. Вместо этого просто выбирается поведение по умолчанию, согласно которому столбец может иметь значение NULL. Ограничение NULL отсутствует в стандарте SQL и не должно использоваться в переносимых приложениях. (Оно было добавлено только в QHB для совместимости с некоторыми другими СУБД.) Однако некоторым пользователям оно нравится, так как позволяет легко переключать ограничение в файле скрипт. Например, можно начать с:

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

а затем добавить ключевое слово NOT, где это необходимо.

Совет
В основном в базах данных при проектировании большинство столбцов должно быть помечено как NOT NULL.

Ограничения уникальности

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

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

когда оно задается как ограничение столбца, и такой:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

когда оно задается как ограничение таблицы.

Чтобы определить ограничение уникальности для группы столбцов, запишите его как ограничение таблицы с именами столбцов, разделенными запятыми:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Это указывает на то, что комбинация значений в указанных столбцах уникальна для всей таблицы, хотя любой отдельный столбец не должен быть (и обычно не является) уникальным.

Ограничению уникальности можно назначить собственное имя обычным способом:

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

Добавление ограничения уникальности автоматически создаст уникальный индекс B-дерево для столбца или группы столбцов, перечисленных в этом ограничении. Условие уникальности, охватывающее только некоторые строки, нельзя записать как ограничение уникальности, но можно обеспечить такое условие, создав уникальный частичный индекс.

Как правило, ограничения уникальности нарушается, если в таблице имеется более одной строки, в которой значения всех входящих в ограничение столбцов равны. Однако в этом сравнении никогда не считаются равными два значения NULL. Это означает, что даже при наличии уникального ограничения можно хранить повторяющиеся строки, содержащие значение NULL как минимум в одном из ограниченных столбцов. Такое поведение соответствует стандарту SQL, но другие базы данных SQL могут не следовать этому правилу. Поэтому будьте осторожны при разработке переносимых приложений.

Ограничение первичного ключа

Ограничение первичного ключа указывает, что столбец или группа столбцов могут использоваться в качестве уникального идентификатора для строк в таблице. Необходимо, чтобы значения были уникальными и не равными NULL. Так что следующие два определения таблицы принимают одинаковые данные:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Первичные ключи могут занимать более одного столбца; в этом случае синтаксис похож на таковой ограничений уникальности:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Добавление первичного ключа автоматически создаст уникальный индекс B-дерево для столбца или группы столбцов, перечисленных в первичном ключе, и ставит на столбец (или столбцы) метку NOT NULL.

Таблица не может иметь более одного первичного ключа. (Может быть сколько угодно уникальных ограничений и ограничений NOT NULL, которые функционально почти одинаковы, но идентифицировать как первичный ключ можно только одно.) Теория реляционных баз данных требует, чтобы у каждой таблицы был первичный ключ. QHB не настаивает на применении этого правила, но обычно лучше его соблюдать.

Первичные ключи полезны как для целей документирования, так и для клиентских приложений. Например, приложение с графическим интерфейсом, позволяющее изменять значения строк, скорее всего должно знать первичный ключ таблицы, чтобы иметь возможность уникально идентифицировать строки. У СУБД существуют различные способы использования первичного ключа, если он был объявлен; например, первичный ключ определяет целевой столбец (или столбцы) по умолчанию для внешних ключей, ссылающихся на его таблицу.

Ограничение внешнего ключа

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

Допустим, у вас есть таблица товаров, которую мы уже использовали несколько раз:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

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

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

Теперь невозможно создавать заказы с отличными от NULL записями product_no, отсутствующими в таблице товаров.

В этой ситуации мы называем таблицу заказов дочерней (или ссылающейся) таблицей, а таблицу товаров — родительской (или ссылочной). Аналогичным образом именуются дочерние и родительские (или ссылающиеся и ссылочные) столбцы.

Указанную выше команду можно сократить до:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

потому что в отсутствие списка столбцов первичный ключ родительской таблицы используется в качестве ссылочного столбца (или столбцов).

Ограничению внешнего ключа можно назначить собственное имя обычным способом.

Кроме того, внешний ключ может ограничивать и ссылаться на группу столбцов. Как обычно, в этом случае его нужно записать в виде табличного ограничения. Вот пример надуманного синтаксиса:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

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

Иногда полезно задавать в качестве «другой таблицы» ограничения внешнего ключа ту же самую таблицу; это называется самоссылающимся внешним ключом. Например, если вы хотите, чтобы строки таблицы представляли узлы древовидной структуры, можно написать:

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

Для узла верхнего уровня parent_id будет равен NULL, тогда как записи с отличным от NULL parent_id окажутся ограничены и будут ссылаться только на допустимые строки таблицы.

Таблица может иметь более одного ограничения внешнего ключа. Это используется для реализации отношений «многие ко многим» между таблицами. Скажем, у вас есть таблицы с товарами и заказами, но теперь вы хотите, чтобы один заказ мог содержать много товаров (что не разрешено в приведенной выше структуре). Можно использовать следующую структуру таблиц:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Обратите внимание, что в последней таблице первичный ключ перекрывает внешние ключи.

Мы знаем, что внешние ключи запрещают создание заказов, не относящихся ни к каким товарам. Но что, если товар будет удален после создания заказа, который на него ссылается? SQL позволяет справиться и с этим. На первый взгляд, у нас есть несколько вариантов:

Запретить удаление ссылочного товара
Удалить вместе с ним связанные заказы;
Что-то другое?

Чтобы проиллюстрировать это, давайте реализуем следующую политику в приведенном выше примере отношения «многие ко многим»: когда кто-то хочет удалить товар, на который все еще ссылается заказ (через order_items), мы это запрещаем. Если кто-то удаляет заказ, его элементы также удаляются:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Ограничивающие и каскадные удаления являются двумя наиболее распространенными вариантами. RESTRICT предотвращает удаление ссылочной строки. NO ACTION означает, что если при проверке ограничения все еще существуют какие-либо ссылающиеся строки, то возникает ошибка; если ничего не указать, это поведение по умолчанию. (Существенное различие между этими двумя вариантами заключается в том, что NO ACTION позволяет отложить проверку до более поздней стадии транзакции, тогда как RESTRICT этого не позволяет.) CASCADE указывает, что при удалении ссылочной строки ссылающаяся строка (или строки) также должны автоматически удаляться. Есть еще два варианта: SET NULL и SET DEFAULT. При удалении ссылочной строки они устанавливают в ссылающихся столбцах ссылающихся строк значения NULL или их значения по умолчанию соответственно. Обратите внимание, что это не освобождает вас от соблюдения ограничений. Например, если действие указывает SET DEFAULT, но значение по умолчанию не удовлетворяет ограничению внешнего ключа, эта операция завершится ошибкой.

Аналогично указанию ON DELETE также существует ON UPDATE который вызывается при изменении (обновлении) ссылочного столбца. Возможные действия одинаковы. В этом случае CASCADE означает, что обновленные значения ссылочных столбцов должны быть скопированы в ссылающиеся строки.

Как правило, ссылающаяся строка не должна удовлетворять ограничению внешнего ключа, если какой-либо из ее ссылающихся столбцов равен NULL. Если к объявлению внешнего ключа добавляется MATCH FULL, то ссылающаяся строка экранируется, удовлетворяя ограничению, только если все ее ссылающиеся столбцы равны NULL (таким образом, сочетание значений NULL и не-NULL гарантированно приведет к сбою ограничения MATCH FULL). Если вы хотите, чтобы ссылающиеся строки не могли не удовлетворять ограничению внешнего ключа, объявите ссылающиеся столбцы как NOT NULL.

Внешний ключ должен ссылаться на столбцы, которые являются первичным ключом или образуют ограничение уникальности. Это означает, что для ссылочных столбцов всегда существует индекс (обусловливающий первичный ключ или ограничение уникальности), поэтому проверка на соответствие ссылающейся строки будет эффективной. Поскольку команды DELETE для строки из ссылочной таблицы или UPDATE для ссылочного столбца потребуют сканирование ссылающейся таблицы на наличие строк, соответствующих старому значению, часто рекомендуется индексировать и ссылающиеся столбцы. Поскольку это не всегда необходимо и существует множество вариантов индексации, объявление ограничения внешнего ключа не создает автоматически индекс по ссылающимся столбцам.

Более подробная информация об обновлении и удалении данных приведена в главе Манипулирование данными. Также см. описание синтаксиса ограничения внешнего ключа на справочной странице команды CREATE TABLE.

Исключающие ограничения

Исключающие ограничения гарантируют, что если любые две строки сравниваются по заданным столбцам или выражениям с использованием заданных операторов, то по крайней мере один из этих операторов сравнения вернет false или NULL. Синтаксис при этом такой:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Подробную информацию см. на справочной странице команды CREATE TABLE ... CONSTRAINT ... EXCLUDE.

Добавление исключающего ограничения автоматически создаст индекс того типа, который указан в объявлении этого ограничения.

Системные столбцы

Каждая таблица имеет несколько системных столбцов, которые неявно определяются системой. Следовательно, эти имена нельзя использовать в качестве имен пользовательских столбцов. (Обратите внимание, что эти ограничения не зависят от того, является ли имя ключевым словом или нет; заключение имени в кавычки все равно не позволит обойти эти ограничения). На самом деле нет нужды беспокоиться об этих столбцах; достаточно знать, что они существуют.

tableoid
OID таблицы, содержащей эту строку. Этот столбец особенно удобен для запросов, которые делают выборку из партиционированных таблиц (см. раздел Партиционирование таблиц или иерархий наследования (см. раздел Наследование), поскольку без него трудно определить, из какой конкретной таблицы получена строка. Столбец tableoid можно соединить со столбцом oid из таблицы pg_class, чтобы получить имя таблицы.

xmin
Идентификатор (код операции) добавляющей транзакции для этой версии строки. (Версия строки - это отдельное состояние строки; каждое изменение создает новую версию той же логической строки).

cmin
Идентификатор команды (начиная с нуля) внутри добавляющей транзакции.

xmax
Идентификатор (код операции) удаляющей транзакции или ноль для неудаленной версии строки. Этот столбец может быть ненулевым в видимой версии строки. Обычно это означает, что удаляющая транзакция еще не зафиксирована или что произошел откат попытки удаления.

cmax
Идентификатор команды в удаляющей транзакции или ноль.

ctid
Физическое местоположение версии строки в ее таблице. Обратите внимание, что хотя ctid можно использовать для очень быстрого поиска версии строки, ctid строки изменится, если та будет изменена или перемещена командой VACUUM FULL. Поэтому столбец ctid бесполезен в качестве долгосрочного идентификатора строки. Для логической идентификации строк должен использоваться первичный ключ.

Идентификаторы транзакций также являются 32-битными величинами. В долгоживущей базе данных нумерация идентификаторов транзакций может пойти по кругу. При подходящих процедурах обслуживания эта проблема не критична; подробную информацию см. в главе Регулярные задачи обслуживания базы данных. Однако в долгосрочной перспективе (более одного миллиарда транзакций) полагаться на уникальность идентификаторов транзакций неразумно.

Идентификаторы команд также являются 32-битными величинами. Это создает жесткий предел в 2^32 (примерно 4 миллиарда) команд SQL в одной транзакции. На практике это ограничение не является проблемой — обратите внимание, что это ограничение на количество команд SQL, а не количество обработанных строк. Кроме того, идентификатор будут использовать только те команды, которые действительно изменяют содержимое базы данных.

Изменение таблиц

Когда вы создаете таблицу и понимаете, что допустили ошибку или что изменились требования приложения, вы можете удалить таблицу и создать ее заново. Но это не самый удобный вариант, если таблица уже заполнена данными или если на нее ссылаются другие объекты базы данных (например ограничение внешнего ключа). Поэтому QHB предоставляет семейство команд для внесения изменений в существующие таблицы. Обратите внимание, что это принципиально отличается от изменения содержащихся в таблице данных: здесь мы заинтересованы в изменении определения, или структуры, этой таблицы.

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

Добавить столбцы
Удалить столбцы
Добавить ограничения
Удалить ограничения
Изменить значения по умолчанию
Изменить типы данных столбцов
Переименовать столбцы
Переименовать таблицы

Все эти действия выполняются с помощью команды ALTER TABLE, справочная страница которой содержит более подробную информацию, чем представленная ниже.

Добавление столбца

Чтобы добавить столбец, можно воспользоваться такой командой:

ALTER TABLE products ADD COLUMN description text;

Новый столбец изначально заполняется любым заданным значением по умолчанию (или значением NULL, если не указано предложение DEFAULT).

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

Однако если значение по умолчанию является изменчивым (как, например, clock_timestamp()), каждую строку нужно будет изменить, записав в нее значение, вычисленное во время выполнения ALTER TABLE. Во избежание потенциально длительной операции изменения, в частности, если вы в любом случае намереваетесь заполнить столбец в основном не значениями по умолчанию, возможно, будет лучше добавить столбец без значения по умолчанию, вставить верные значения с помощью UPDATE, а затем добавить любое желаемое значение по умолчанию, как описано ниже.

Можно также одновременно установить ограничения для этого столбца, используя обычный синтаксис:

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

На самом деле в этой команде можно использовать все параметры, применимые к описанию столбца в CREATE TABLE. Однако имейте в виду, что значение по умолчанию должно удовлетворять заданным ограничениям, иначе операция ADD завершится с ошибкой. Как вариант, можно добавить ограничения позже (см. ниже), после правильного заполнения нового столбца.

Удаление столбца

Чтобы удалить столбец, можно воспользоваться такой командой:

ALTER TABLE products DROP COLUMN description;

Какие бы данные ни были в столбце, они исчезнут. Ограничения таблицы, касающиеся этого столбца, тоже удаляются. Однако если на столбец ссылается ограничение внешнего ключа другой таблицы, QHB не станет молча удалять это ограничение. Разрешить удаление всего, что зависит от столбца, можно, добавив указание CASCADE:

ALTER TABLE products DROP COLUMN description CASCADE;

Общий механизм, стоящий за этим, описан в разделе Отслеживание зависимостей.

Добавление ограничения

Чтобы добавить ограничение, используется синтаксис ограничения таблицы. Например:

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

Чтобы добавить ограничение NOT NULL, которое нельзя записать как ограничение таблицы, используйте этот синтаксис:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

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

Удаление ограничения

Чтобы удалить ограничение, нужно знать его имя. Если имя задано, это легко. В противном случае система присваивает сгенерированное имя, которое необходимо выяснить. Здесь может быть полезна команда psql \d имя_таблицы; другие интерфейсы тоже могут обеспечить способ проверки информации о таблице. После этого можно воспользоваться следующей командой:

ALTER TABLE products DROP CONSTRAINT some_name;

(Если вы имеете дело со сгенерированным именем ограничения, например $2, не забывайте, что его нужно будет заключить в кавычки, чтобы сделать действительным идентификатором.)

Как и в случае удаления столбца, если нужно удалить ограничение, от которого зависит что-то еще, следует добавить указание CASCADE. Примером служит ограничение внешнего ключа, зависящее от ограничения уникальности или первичного ключа на ссылочных столбцах.

Это работает одинаково для всех типов ограничений, кроме ограничений NOT NULL. Чтобы удалить ограничение NOT NULL, используйте:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

(Вспомните, что у ограничений NOT NULL нет имен.)

Изменение значения столбца по умолчанию

Чтобы установить для столбца новое значение по умолчанию, используйте следующую команду:

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Обратите внимание, что выполнение этой команды не влияет на существующие строки в таблице, она просто меняет значение по умолчанию для будущих команд INSERT.

Чтобы удалить любое значение по умолчанию, используйте:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

Это практически то же самое, что установить значением по умолчанию NULL. Как следствие, сброс значения по умолчанию, если оно не было определено, не будет ошибкой, потому что значение по умолчанию неявно равно значению NULL.

Изменение типа данных столбца

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

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

Команда завершится успешно, только если каждую существующую запись в столбце можно преобразовать в новый тип путем неявного приведения. Если требуется более сложное преобразование, можно добавить предложение USING, в котором указано, как вычислить новые значения из старых.

QHB будет пытаться преобразовать значение столбца по умолчанию (если оно есть) в новый тип, и все ограничения, связанные с этим столбцом. Но эти преобразования могут завершиться ошибкой или привести к неожиданным результатам. Зачастую перед изменением типа столбца лучше удалить любые его ограничения, а затем снова их добавить, но уже измененными подходящим образом.

Переименование столбца

Чтобы переименовать столбец, выполните:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

Переименование таблицы

Чтобы переименовать таблицу, выполните:

ALTER TABLE products RENAME TO items;

Права

При создании объекта ему назначается владелец. Обычно владельцем является роль, выполнившая создающую команду. Для большинства видов объектов начальное состояние таково, что только владелец (или суперпользователь) может что-либо делать с этим объектом. Чтобы другие роли могли его использовать, необходимо предоставить им права.

Существуют различные виды прав: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE и USAGE. Права, применимые к конкретному объекту, различаются в зависимости от типа объекта (таблица, функция и т. д.). Более подробная информация о значениях этих прав приведена ниже. В следующих разделах и главах также будет описано, как используются эти права.

Право изменять или удалять объект является неотъемлемым правом владельца объекта и не может быть назначено или отозвано само по себе. (Однако, как и все права, это право может быть унаследовано членами роли-владельца; см. раздел Членство в роли.)

Объекту можно назначить нового владельца с помощью команды ALTER соответствующего объекту вида, например:

ALTER TABLE имя_таблицы OWNER TO новый_владелец;

Суперпользователи всегда могут это сделать; обычные роли могут делать это только в том случае, если они являются одновременно текущим владельцем объекта (или членом роли-владельца) и членом новой роли-владельца.

Для назначения прав используется команда GRANT. Например, если joe является существующей ролью, а accounts — существующей таблицей, право на изменение таблицы можно предоставить так:

GRANT UPDATE ON accounts TO joe;

Запись ALL вместо определенного права предоставляет все права, которые относятся к типу объекта.

Для предоставления права каждой роли в системе можно использовать специальное имя «роли» PUBLIC. Кроме того, когда пользователей базы данных много, можно установить «групповые» роли, что поможет управлять правами, — подробности см. в главе Роли в базе данных.

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

REVOKE ALL ON accounts FROM PUBLIC;

Обычно только владелец объекта (или суперпользователь) может предоставить или отозвать права для объекта. Тем не менее можно предоставить право «с функцией передачи», которая дает получателю возможность в свою очередь предоставлять его другим лицам. Если функция предоставления впоследствии отзывается, то все, кто получил право от этого получателя (напрямую или через цепочку предоставлений), потеряют это право. Подробную информацию см. на справочных страницах команд GRANT и REVOKE.

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

Доступные права:

SELECT
Позволяет выбирать с помощью SELECT любой или заданный столбец (столбцы) таблицы, представления, материализованного представления или другого табличного объекта. Также позволяет использовать COPY TO. Это право также необходимо для обращения по ссылке на существующие значения столбца в UPDATE или DELETE. Кроме того для последовательностей это право позволяет использовать функцию currval. Для больших объектов это право позволяет прочитать данные в объекте.

INSERT
Разрешает добавлять с помощью INSERT новую строку в таблицу, представление и т. д. Может быть предоставлено на заданные столбцы, и тогда в команде INSERT можно присваивать значения только этим столбцам (поэтому другие столбцы будут получать значения по умолчанию). Также позволяет использовать COPY FROM.

UPDATE
Разрешает изменять с помощью UPDATE любой или заданный столбец (столбцы) таблицы, представления и т. д. (На практике любой нетривиальной команде UPDATE также потребуется право SELECT, поскольку она должна обращаться к столбцам таблицы, чтобы определить, какие строки изменять, и/или чтобы вычислить новые значения для столбцов.) Для команды SELECT ... FOR UPDATE и SELECT ... FOR SHARE, в дополнение к праву SELECT, также требуется это право как минимум для одного столбца. Для последовательностей это право позволяет использовать функции nextval и setval. Для больших объектов это право позволяет записывать данные в объект или опустошать его.

DELETE
Позволяет удалять с помощью DELETE строки из таблицы, представления и т. д. (На практике любой нетривиальной команде DELETE также потребуется право SELECT, поскольку она должна обращаться к столбцам таблицы, чтобы определить, какие строки нужно удалить).

TRUNCATE
Позволяет опустошать таблицу с помощью TRUNCATE.

REFERENCES
Позволяет создать ограничение внешнего ключа, ссылающегося на таблицу или определенные столбцы таблицы.

TRIGGER
Позволяет создать триггер для таблицы, представления и т. д.

CREATE
Для баз данных позволяет создавать в них новые схемы и публикации, а также устанавливать доверенные расширения.

Для схем позволяет создавать в них новые объекты. Чтобы переименовать существующий объект, нужно быть его владельцем и иметь это право для содержащей его схемы.

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

Обратите внимание, что отзыв этого права не изменит существование или расположение существующих объектов.

CONNECT
Позволяет правополучателю подключаться к базе данных. Это право проверяется при установлении соединения (в дополнение к проверке любых ограничений, налагаемых qhb_hba.conf).

TEMPORARY
Позволяет создавать временные таблицы при использовании конкретной базы данных.

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

USAGE

Для процедурных языков позволяет использовать язык для создания функций на этом языке. Это единственный тип прав, применимый к процедурным языкам.

Для схем разрешает доступ к содержащимся в них объектам (при условии, что собственные требования к правам объектов также выполнены). По сути, это позволяет правополучателю «искать» объекты в схеме. Без этого разрешения имена объектов все равно можно увидеть, например, запросив системные каталоги. Кроме того, после отзыва этого разрешения в существующих сеансах могут быть операторы, ранее выполнявшие этот поиск, так что этот способ предотвращения доступа к объекту не вполне безопасен.

Для последовательностей позволяет использовать функции currval и nextval.

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

Для оберток сторонних данных позволяет создавать с их помощью новые серверы.

Для сторонних серверов позволяет создавать с их помощью сторонние таблицы. Кроме того, правополучатели могут создавать, изменять или удалять свои собственные сопоставления пользователей, связанные с этим сервером.
.

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

QHB по умолчанию предоставляет роли PUBLIC права для некоторых типов объектов при создании этих объектов. Для таблиц, столбцов таблиц, последовательностей, оберток сторонних данных, сторонних серверов, больших объектов, схем или табличных пространств PUBLIC никаких прав по умолчанию не получает. Для других типов объектов правами по умолчанию, предоставляемыми PUBLIC, являются: CONNECT и TEMPORARY (создание временных таблиц) для баз данных; право EXECUTE для функций и процедур; право USAGE для языков и типов данных(включая домены). Разумеется, владелец объекта может отозвать с помощью REVOKE как права по умолчанию, так и права, предоставленные явно. (Для обеспечения максимальной безопасности выполните команду REVOKE в той же транзакции, в которой создается объект; тогда не образуется промежуток, во время которого другой пользователь может использовать объект). Кроме того, эти настройки прав по умолчанию можно изменить с помощью команды ALTER DEFAULT PRIVILEGES.

В Таблице 1 перечислены однобуквенные сокращения, которые используются для этих типов прав в значениях ACL (Access Control List, список управления доступом). Эти буквы можно увидеть в выходных данных перечисленных ниже команд psql или при просмотре столбцов ACL системных каталогов.

Таблица 1. Сокращения для прав в ACL

Право	Сокращение	Применимые типы объектов
SELECT	r («read», читать)	LARGE OBJECT, SEQUENCE, TABLE (и табличные объекты), столбец таблицы
INSERT	a («append», добавить)	TABLE, столбец таблицы
UPDATE	w («write», записать)	LARGE OBJECT, SEQUENCE, TABLE, столбец таблицы
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE, столбец таблицы
TRIGGER	t	TABLE
CREATE	C	DATABASE, SCHEMA, TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

В Таблице 2 сведены права, доступные для каждого типа объекта SQL, с использованием вышеприведенных сокращений. Кроме того, здесь перечислены команды psql, с помощью которых можно проверить настройки прав для каждого типа объекта.

Таблица 2. Сводка прав доступа

Тип объекта	Все права	Права PUBLIC по умолчанию	Команда psql
DATABASE	CTc	Tc	`\l`
DOMAIN	U	U	`\dD+`
FUNCTION или PROCEDURE	X	X	`\df+`
FOREIGN DATA WRAPPER	U	нет	`\dew+`
FOREIGN SERVER	U	нет	`\des+`
LANGUAGE	U	U	`\dL+`
LARGE OBJECT	rw	нет
SCHEMA	UC	нет	`\dn+`
SEQUENCE	rwU	нет	`\dp`
TABLE (и табличные объекты)	arwdDxt	нет	`\dp`
Столбец таблицы	arwx	нет	`\dp`
TABLESPACE	C	нет	`\db+`
TYPE	U	U	`\dT+`

Права, которые были предоставлены для определенного объекта, отображаются в виде списка записей aclitem, где каждая запись aclitem описывает разрешения одного правополучателя, которые были предоставлены конкретным праводателем. Например, calvin=r*w/hobbes указывает, что роль calvin имеет право SELECT (r) с возможностью его передачи (*), а также не подлежащее передаче право UPDATE (w), и оба они предоставлены ролью hobbes. Если calvin также имеет некоторые права на тот же объект, предоставленные другим праводателем, они будут отображаться в виде отдельной записи aclitem. Пустое поле правополучателя в aclitem означает роль PUBLIC.

В качестве примера предположим, что пользователь miriam создает таблицу mytable и выполняет:

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

Тогда команда psql \dp покажет:

=> \dp mytable
                                  Access privileges
 Schema |  Name   | Type  |   Access privileges   |   Column privileges   | Policies
--------+---------+-------+-----------------------+-----------------------+----------
 public | mytable | table | miriam=arwdDxt/miriam+| col1:                +|
        |         |       | =r/miriam            +|   miriam_rw=rw/miriam |
        |         |       | admin=arw/miriam      |                       |
(1 row)

Если столбец «Access privileges» для данного объекта пуст, это означает, что на объект имеются права по умолчанию (то есть запись его прав в соответствующем системном каталоге равна NULL). Права по умолчанию всегда включают все права для владельца и могут включать некоторые права для PUBLIC в зависимости от типа объекта, как описано выше. Первая команда GRANT или REVOKE реализует права по умолчанию (создав, например, miriam=arwdDxt/miriam), а затем изменит их согласно указанному запросу. Аналогично записи в «Column privileges» отображаются только для столбцов с правами не по умолчанию. (Примечание: для этой цели «права по умолчанию» всегда означают встроенные права по умолчанию для типа объекта. Объект, у которого права были изменены командой ALTER DEFAULT PRIVILEGES, всегда будет отображаться с явной записью прав, включающей эффект ALTER).

Обратите внимание, что неявные возможности владельца передавать права в отображении прав доступа не отмечаются. А знак * появится, только когда возможности передачи прав были кому-либо предоставлены явно.

Политики защиты строк

В дополнение к стандартной системе прав SQL, доступной посредством команды GRANT, таблицы могут иметь политики защиты строк, ограничивающие для пользователей строки, которые могут быть возвращены обычными запросами или добавлены, изменены или удалены с помощью команд изменения данных. Эта функция также известна как защита на уровне строк (Row-Level Security, RLS). По умолчанию у таблиц нет никаких политик, поэтому, если у пользователя есть права доступа к таблице в соответствии с системой прав SQL, все строки в ней одинаково доступны для запроса или изменения.

Когда в таблице включена защита строк (с помощью ALTER TABLE ... ENABLE ROW LEVEL SECURITY), все обычные обращения к таблице для выбора или изменения строк должны быть разрешены политикой защиты строк. (Однако на владельца таблицы политики защиты строк обычно не распространяются.) Если для таблицы не существует политики, по умолчанию используется политика запрета, означающая, что строки не видны или не могут быть изменены. На операции, которые применяются ко всей таблице, например TRUNCATE и REFERENCES, защита строк не действует.

Политики защиты строк могут применяться к конкретным командам, ролям или тому и другому. Можно указать политику как применяемую ко всем командам (ALL) либо к SELECT, INSERT, UPDATE или DELETE. Заданную политику можно связать с несколькими ролями, и при этом применяются обычные правила членства и наследования ролей.

Чтобы указать, какие строки являются видимыми или изменяемыми в соответствии с политикой, требуется выражение, возвращающее логический результат. Это выражение будет вычисляться для каждой строки перед проверкой любых условий или выполнении функций, поступающих из пользовательского запроса. (Единственным исключением из этого правила являются функции leakproof, которые гарантированно не допускают утечки информации; оптимизатор может выбрать применение таких функций перед проверкой защиты строк). Строки, для которых это выражение не возвращает true, обрабатываться не будут. Для обеспечения независимого управления видимыми строками и строками, которые можно изменять, возможно задать отдельные выражения. Выражения политики выполняются как часть запроса с правами выполняющего его пользователя, хотя для доступа к данным, недоступным вызывающему пользователю, можно использовать функции определения настроек защиты.

Суперпользователи и роли с атрибутом BYPASSRLS при доступе к таблице всегда обходят систему защиты строк. Владельцы таблиц обычно тоже обходят защиту строк, хотя они могут включить ее для себя с помощью ALTER TABLE ... FORCE ROW LOWEL SECURITY.

Право на включение и выключение защиты строк, а также добавление политик в таблицу всегда имеет только владелец таблицы.

Политики создаются с помощью команды CREATE POLICY, изменяются с помощью команды ALTER POLICY и удаляются с помощью команды DROP POLICY. Чтобы включать и выключать защиту строк для конкретной таблицы, воспользуйтесь командой ALTER TABLE.

У каждой политики есть имя, и для таблицы можно определить несколько политик. Поскольку политики привязаны к таблице, каждая такая политика должна иметь уникальное имя. Разные таблицы могут иметь политики с одинаковым именем.

Когда к данному запросу применяется несколько политик, они объединяются либо с помощью OR (для разрешающих политик, которые используются по умолчанию), либо с помощью AND (для ограничительных политик). Это похоже на правило, согласно которому отдельно взятая роль обладает правами всех ролей, членом которых она является. Разрешительные и ограничительные политики рассматриваются ниже.

В качестве простого примера далее показано, как создать политику в отношении account, чтобы разрешить доступ к строкам только членам роли managers и при этом только к строкам их собственных учетных записей:

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

Приведенная выше политика неявно предоставляет предложение WITH CHECK, идентичное ее предложению USING, поэтому ограничение применяется как к строкам, выбранным командой (таким образом, менеджер не может выполнять SELECT, UPDATE или DELETE для существующих строк, принадлежащих другому менеджеру), так и к строкам, измененным командой (таким образом, строки, принадлежащие другому менеджеру, нельзя создать с помощью INSERT или UPDATE).

Если роль не указана или используется специальное имя пользователя PUBLIC, то политика применяется ко всем пользователям в системе. Чтобы разрешить всем пользователям доступ только к своей строке в таблице users, можно использовать простую политику:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

Это работает аналогично предыдущему примеру.

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

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);

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

Защиту строк также можно выключить с помощью команды ALTER TABLE. Выключение защиты строк не приводит к удалению политик, определенных для таблицы; они просто игнорируются. Затем все строки в таблице становятся видимыми и изменяемыми в соответствии с системой прав стандарта SQL.

Ниже приведен развернутый пример того, как эту функциональность можно использовать в производственных средах. Таблица passwd эмулирует файл паролей Unix:

-- Простой пример на основе файла passwd
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Администратор
CREATE ROLE bob;    -- Обычный пользователь
CREATE ROLE alice;  -- Обычный пользователь

-- Наполнение таблицы
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Обязательно нужно включить для этой таблицы защиту на уровне строк
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Создание политик
-- Администратор может видеть и добавлять любые строки
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Обычные пользователи могут видеть все строки
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Обычные пользователи могут изменять свои записи, но
-- не могут задавать любую произвольную оболочку
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Администратор получает все обычные права
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Пользователи получают доступ на чтение только к общедоступным столбцам
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Пользователям разрешается изменять определенные столбцы
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

Как и при любых настройках защиты, важно проверить политики и убедиться, что эта система работает должным образом. В приведенном выше примере показано, что система разрешений работает правильно.

-- администратор может видеть все строки и поля
qhb=> set role admin;
SET
qhb=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- Проверка того, что может делать Элис
qhb=> set role alice;
SET
qhb=> table passwd;
ERROR:  permission denied for relation passwd
-- ОШИБКА: отсутствие прав доступа для отношения passwd
qhb=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

qhb=> update passwd set user_name = 'joe';
ERROR:  permission denied for relation passwd
-- Элис разрешено изменить свое имя (поле real_name), но не другие имена
qhb=> update passwd set real_name = 'Alice Doe';
UPDATE 1
qhb=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
qhb=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
-- ОШИБКА: новая роль нарушает ПАРАМЕТР WITH CHECK для "passwd"
qhb=> delete from passwd;
ERROR:  permission denied for relation passwd
qhb=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd
-- Элис может изменить свой пароль; изменение других строк RLS молча предотвращает
qhb=> update passwd set pwhash = 'abc';
UPDATE 1

Все созданные до сих пор политики были разрешающими, то есть при применении нескольких политик они объединяются с помощью логического оператора «ИЛИ». Хотя разрешающие политики могут создаваться так, чтобы разрешать доступ к строкам только в желаемых случаях, может быть проще объединить разрешающие политики с ограничительными (которым должны удовлетворять записи и которые объединяются с помощью логического оператора «И»). На основе вышеприведенного примера мы добавляем ограничительную политику, требующую, чтобы для доступа к записям таблицы passwd администратора подключался через локальный сокет домена Unix:

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

Затем мы можем удостовериться, что администратор, подключающийся по сети, не увидит никаких записей из-за этой ограничительной политики:

=> SELECT current_user;
 current_user
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr
------------------
 127.0.0.1
(1 row)

=> SELECT current_user;
 current_user
--------------
 admin
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

Проверки ссылочной целостности, такие как ограничения уникальности или первичных ключей, а также ссылки на внешние ключи, всегда обходят защиту строк для обеспечения целостности данных. При разработке схем и политик на уровне строк необходимо соблюдать осторожность во избежание утечек информации по «скрытому каналу» посредством таких проверок ссылочной целостности.

В некоторых случаях важно сделать так, чтобы защита строк не применялась. Например, если при создании резервной копии вследствие политики безопасности строк некоторые строки окажутся неявно из нее исключены, это может привести к катастрофическим последствиям. В такой ситуации можно выключить (установить значение off) параметр конфигурации row_security. Само по себе это не обходит защиту строк; если результаты какого-либо запроса окажутся отфильтрованы политикой, будет выдана ошибка. Затем можно будет выяснить и устранить причину этой ошибки.

В приведенных выше примерах выражения политик учитывают только текущие значения в запрашиваемой или изменяемой строке. Это самый простой и наиболее эффективный вариант; лучше всего по возможности разрабатывать приложения для защиты строк именно таким образом. Если для принятия решения о политике необходимо обратиться к другим строкам или таблицам, это можно делать в выражениях политик с помощью подзапросов SELECT или функций, содержащих SELECT. Однако имейте в виду, что такой доступ может создать состояния гонки, чреватые утечкой информации, если не принять меры предосторожности. В качестве примера рассмотрим следующую конструкцию таблицы:

-- определение групп прав
CREATE TABLE groups (group_id int PRIMARY KEY,
                     group_name text NOT NULL);

INSERT INTO groups VALUES
  (1, 'low'),
  (2, 'medium'),
  (5, 'high');

GRANT ALL ON groups TO alice;  -- alice является администратором
GRANT SELECT ON groups TO public;

-- определение уровней прав пользователей
CREATE TABLE users (user_name text PRIMARY KEY,
                    group_id int NOT NULL REFERENCES groups);

INSERT INTO users VALUES
  ('alice', 5),
  ('bob', 2),
  ('mallory', 2);

GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;

-- таблица, содержащая требующую защиты информацию
CREATE TABLE information (info text,
                          group_id int NOT NULL REFERENCES groups);

INSERT INTO information VALUES
  ('barely secret', 1),
  ('slightly secret', 2),
  ('very secret', 5);

ALTER TABLE information ENABLE ROW LEVEL SECURITY;

-- строка должна быть доступна для чтения/изменения пользователям с group_id защиты
-- большим или равным group_id данной строки
CREATE POLICY fp_s ON information FOR SELECT
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));

-- мы защищаем таблицу с информацией, полагаясь только на RLS
GRANT ALL ON information TO public;

Теперь предположим, что alice хочет изменить «слегка секретную» информацию, но решает, что mallory не следует показывать новое содержимое этой строки, поэтому она делает так:

BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

Это выглядит безопасно — нет промежутка между выполнением команд, в котором mallory смогла бы увидеть строку «secret from mallory». Тем не менее здесь присутствует состояние гонки. Если mallory одновременно делает, скажем,

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

и ее транзакция находится в режиме READ COMMITTED, она может видеть «secret from mallory». Это происходит, если ее транзакция достигает строки information сразу после транзакции alice. Она блокируется, ожидая фиксации транзакции alice, а затем извлекает измененное содержимое строки благодаря предложению FOR UPDATE. Однако она не извлекает измененную строку из users для неявного SELECT, потому что этот подзапрос SELECT не содержал FOR UPDATE; вместо этого строка users читается из снимка, сделанного в начале запроса. Поэтому выражение политики проверяет старое значение уровня прав mallory и позволяет ей видеть эту измененную строку.

Есть несколько способов обойти эту проблему. Одним простым ответом является использование SELECT ... FOR SHARE в подзапросах SELECT в политиках защиты строк. Однако для этого необходимо предоставить заинтересованным пользователям право UPDATE для ссылочной таблицы (в данном случае users), что может быть нежелательным. (Но можно применить другую политику защиты строк, чтобы не дать им фактически использовать это право, или встроить подзапрос SELECT в функцию определения настроек защиты.) Кроме того, интенсивное одновременное использование разделяемых блокировок строки в ссылочной таблице может привести к снижению производительности, особенно если строку часто изменяют. Другое решение, имеющее смысл, если изменения ссылочной таблицы происходят нечасто, — установить блокировку ссылочной таблицы в режиме ACCESS EXCLUSIVE при ее изменении, чтобы никакие параллельные транзакции не могли проверять старые значения строк. Или можно просто дождаться завершения всех параллельных транзакций после фиксации изменения в ссылочной таблице, прежде чем вносить изменения, рассчитанные на новую ситуацию в области безопасности.

Дополнительную информацию см. на справочных страницах команд CREATE POLICY и ALTER TABLE.

Схемы

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

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

База данных содержит одну или несколько именованных схем, которые, в свою очередь, содержат таблицы. Схемы содержат и другие виды именованных объектов, включая типы данных, функции и операторы. Одно и то же имя объекта можно без конфликтов использовать в разных схемах; например, и schema1 и myschema могут содержать таблицы с именем mytable. В отличие от баз данных, схемы разделены не жестко: пользователь может обращаться к объектам в любой схеме в базе данных, к которой он подключен, если у него есть для этого права.

Существует несколько причин, по которым стоит использовать схемы:

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

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

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

Чтобы создать схему, используйте команду CREATE SCHEMA. Дайте схеме имя по вашему выбору. Например:

CREATE SCHEMA myschema;

Чтобы создать или получить доступ к объектам в схеме, напишите полное имя, состоящее из имени схемы и имени таблицы, разделенных точкой:

схема.таблица

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

На самом деле, можно использовать даже более общий синтаксис:

база_данных.схема.таблица

но в настоящее время он существует только для формального соответствия стандарту SQL. Если вы напишете имя базы данных, оно должно совпадать с именем базы, к которой вы подключены.

Итак, чтобы создать таблицу в новой схеме, используйте:

CREATE TABLE myschema.mytable (
 ...
);

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

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

Описание стоящего за этим общего механизма см. в разделе Отслеживание зависимостей.

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

CREATE SCHEMA имя_схемы AUTHORIZATION имя_пользователя;

Можно даже опустить имя схемы, и в этом случае оно будет совпадать с именем пользователя. Возможности применения этого см. в подразделе Шаблоны использования.

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

Схема Public

В предыдущих разделах мы создавали таблицы без указания имен схем. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему с именем «public». Каждая новая база данных содержит такую схему. Таким образом, команда

CREATE TABLE products (...);

равнозначна команде

CREATE TABLE public.products (...);

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

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

Возможность создавать объекты с одинаковыми именами в разных схемах усложняет написание запроса, который каждый раз ссылается на одни и те же объекты. Это также открывает возможность одним пользователям злонамеренно или случайно изменять поведение запросов других пользователей. Из-за преобладания неполных имен в запросах и их использования во внутренних структурах QHB, добавление схемы в search_path по сути доверяет всем пользователям, имеющим право CREATE для этой схемы. Когда вы запускаете обычный запрос, злонамеренный пользователь, способный создавать объекты в схеме вашего пути поиска, может взять на себя управление и выполнять произвольные функции SQL, как если бы их выполняли вы.

Первая схема, названная в пути поиска, называется текущей схемой. Это не только первая искомая схема — в ней будут создаваться новые таблицы, если в команде CREATE TABLE не задано имя схемы.

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

SHOW search_path;

В настройках по умолчанию это возвращает:

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

Первый элемент указывает, что следует искать схему с именем текущего пользователя. Если такой схемы не существует, эта запись игнорируется. Второй элемент ссылается на схему public, которую мы уже видели.

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

Чтобы поместить нашу новую схему в путь, необходимо использовать:

SET search_path TO myschema,public;

(Мы опускаем здесь $user, потому что прямо сейчас оно не нужно.) И тогда можно обратиться к таблице без указания схемы:

DROP TABLE mytable;

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

Также можно написать:

SET search_path TO myschema;

Теперь у нас больше нет доступа к схеме public без ее явного указания. В схеме public нет ничего особенного, за исключением того, что она существует по умолчанию. Ее тоже можно удалить.

Другие способы управления путем поиска схемы см. также в разделе Системные информационные функции и операторы.

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

OPERATOR(схема.оператор)

Это необходимо, чтобы избежать синтаксической неоднозначности. Пример:

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

Схемы и права

По умолчанию пользователи не могут получить доступ к объектам в схемах, которыми они не владеют. Для этого владелец схемы должен предоставить право USAGE для этой схемы. Чтобы разрешить пользователям использовать объекты в схеме, могут потребоваться дополнительные права, подходящие для этого объекта.

Пользователю также может быть разрешено создавать объекты в чужой схеме. Для этого необходимо предоставить право CREATE для этой схемы. Обратите внимание, что по умолчанию все имеют права CREATE и USAGE в схеме public. Это позволяет всем пользователям, которые могут подключаться к указанной базе данных, создавать объекты в ее схеме public. Некоторые шаблоны использования требуют отзыва этого права:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(Первое слово «public» означает схему, второе — «каждый пользователь». В первом случае это идентификатор, во втором — ключевое слово, отсюда и другой регистр; см. разъяснения в подразделе Идентификаторы и ключевые слова.)

Схема системного каталога

В дополнение к public и созданным пользователями схемам каждая база данных содержит схему pg_catalog, содержащую системные таблицы и все встроенные типы данных, функции и операторы. В сущности, pg_catalog всегда является частью пути поиска. Если она не указана в пути явно, то она неявно просматривается перед поиском в остальных схемах пути. Это гарантирует, что встроенные имена всегда будет можно найти. Тем не менее если вы предпочитаете, чтобы имена, определяемые пользователем, переопределяли встроенные имена, можно явно разместить pg_catalog в конце пути поиска.

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

Шаблоны использования

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

Ограничить обычных пользователей частными схемами. Для этого выполните REVOKE CREATE ON SCHEMA public FROM PUBLIC и создайте для каждого пользователя схему с его же именем. Напомним, что путь поиска по умолчанию начинается с имени $user, которое преобразуется в имя пользователя. Поэтому, если у каждого пользователя есть отдельная схема, они по умолчанию получают доступ к своим схемам. После принятия этого шаблона в базе данных, куда уже вошли ненадежные пользователи, рассмотрите возможность проверки схемы public на предмет наличия объектов с именами как у объектов в схеме pg_catalog. Этот шаблон является безопасным шаблоном использования схемы, за исключением случаев, когда недоверенный пользователь является владельцем базы данных или имеет право CREATEROLE — тогда никакой безопасный шаблон использования схемы невозможен.
Удалить схему public из пути поиска по умолчанию, изменив qhb.conf или введя команду ALTER ROLE ALL SET search_path = "$user". При этой все пользователи сохраняют возможность создавать объекты в схеме public, но выбирать эти объекты возможно, только используя полные имена. Тогда как обращения к таблицам по полному имени вполне допустимы, вызовы функций в схеме public будут небезопасными или ненадежными. Если вы создаете в схеме public функции или расширения, используйте вместо этого первый шаблон. Иначе, как и в первом шаблоне, это безопасно за исключением случаев, когда ненадежный пользователь является владельцем базы данных или имеет право CREATEROLE.
Сохранить поведение по умолчанию. Все пользователи неявно имеют доступ к схеме public. Это моделирует ситуацию, когда схемы вообще недоступны, обеспечивая плавный переход из среды без схем. Однако это не безопасный шаблон. Он допустим только в том случае, если в базе данных имеется всего один или несколько доверяющих друг другу пользователей.

Какой бы шаблон вы не выбрали, при установке разделяемых приложений (таблиц, которые будут использоваться всеми, дополнительных функции, предоставленных третьими лицами, и т. д.) поместите их в отдельные схемы. Не забудьте предоставить соответствующие права, чтобы открыть доступ к ним другим пользователям. Тогда пользователи смогут обращаться к этим дополнительным объектам, указывая в их именах имя схемы, или при желании помещать эти дополнительные схемы в свой путь поиска.

Возможность переноса

В стандарте SQL не существует концепции, что объекты в одной схеме могут принадлежать разным пользователям. Более того, некоторые реализации СУБД не позволяют создавать схемы, имена которых отличаются от имен их владельцев. Фактически в СУБД, реализующей только базовую поддержку схемы, указанную в стандарте, понятия схемы и пользователя почти равнозначны. Поэтому многие пользователи считают, что в действительности полные имена записываются как имя_пользователя.имя_таблицы. Именно так будет вести себя QHB, если вы создадите схемы для каждого пользователя.

Кроме того, в стандарте SQL отсутствует понятие схемы public. Для максимального соответствия стандарту использовать схему public не следует.

Конечно, некоторые СУБД SQL могут вообще не реализовывать схемы или предоставлять поддержку пространства имен, разрешая (возможно, с ограничениями) перекрестный доступ к базам данных. Если вам потребуется работать с этими системами, максимальной переносимости можно будет достичь, если вообще не использовать схемы.

Наследование

QHB реализует наследование таблиц, что может быть полезным инструментом для разработчиков баз данных. (SQL:1999 и более поздние версии определяют возможность наследования типов, которая во многих отношениях отличается от описанных здесь функциональностей.)

Давайте начнем с примера: предположим, что мы пытаемся построить модель данных для городов. В каждом штате много городов, но только одна столица. Мы хотим иметь возможность быстро получить столицу для любого конкретного штата. Это можно сделать, создав две таблицы: одну для столиц штатов, другую для городов, которые не являются столицами. Но что происходит, когда мы хотим запросить данные о городе, независимо от того, является он столицей или нет? Возможность наследования может помочь решить эту проблему. Мы определяем таблицу capitals так, чтобы она наследовала cities:

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- в футах
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

В этом случае таблица capitals наследует все столбцы своей родительской таблицы cities. Столицы штатов также имеют дополнительный столбец state, в котором указан их штат.

В QHB таблица может наследоваться от нуля и более других таблиц, а запрос может ссылаться либо на все строки таблицы, либо на все строки таблицы плюс всех ее потомков. Последнее является поведением по умолчанию. Например, следующий запрос находит названия всех городов, включая столицы штатов, которые расположены на высоте более 500 футов:

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

Учитывая пример данных из руководства QHB (см. раздел Введение), этот запрос возвращает:

name      | altitude
----------+----------
Las Vegas |     2174
Mariposa  |     1953
Madison   |      845

С другой стороны, следующий запрос находит все города, которые не являются столицами штатов и расположены на высоте более 500 футов:

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953

Здесь ключевое слово ONLY указывает, что запрос должен применяться только к cities, но не к таблицам ниже ее в иерархии наследования. Многие из команд, которые мы уже обсуждали — SELECT, UPDATE и DELETE — поддерживают ключевое слово ONLY.

Кроме того, можно написать имя таблицы с завершающим *, чтобы явно указать, что включены и дочерние таблицы:

SELECT name, altitude
    FROM cities*
    WHERE altitude > 500;

Запись * не обязательна, так как по умолчанию всегда подразумевается это поведение.

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

SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

этот запрос возвращает:

tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845

(Если вы попытаетесь воспроизвести этот пример, вы, вероятно, получите в OID другие числа.) Объединив oid с pg_class, можно увидеть фактические имена таблиц:

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 AND c.tableoid = p.oid;

этот запрос возвращает:

relname  |   name    | altitude
----------+-----------+----------
cities   | Las Vegas |     2174
cities   | Mariposa  |     1953
capitals | Madison   |      845

Другой способ получить тот же эффект — использовать псевдоним типа regclass, который выведет OID таблицы в символьном виде:

SELECT c.tableoid::regclass, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

Наследование не распространяет автоматически данные из команд INSERT или COPY в другие таблицы в иерархии наследования. В нашем примере следующий оператор INSERT завершится ошибкой:

INSERT INTO cities (name, population, altitude, state)
VALUES ('Albany', NULL, NULL, 'NY');

Можно надеяться, что данные будут каким-то образом перенаправлены в таблицу capitals, но этого не происходит: INSERT всегда вставляет данные точно в указанную таблицу. В некоторых случаях возможно перенаправить добавление с помощью правила перезаписи запросов. Однако в приведенном выше примере это не поможет, поскольку таблица cities не содержит столбец state, и поэтому команда будет отвергнута до того, как можно будет применить это правило.

Все проверочные ограничения и ограничения NOT NULL родительской таблицы автоматически наследуются ее дочерними таблицами, если явно не указано какое-либо предложение NO INHERIT. Другие типы ограничений (уникальности, первичного и внешнего ключей) не наследуются.

Таблица может наследоваться от нескольких родительских таблиц, и в этом случае она будет объединять в себе столбцы, определенные родительскими таблицами. К ним добавляются все столбцы, объявленные в определении дочерней таблицы. Если в нескольких родительских таблицах или в определении как родительской, так и дочерней таблицы встречаются столбцы с одинаковым именем, то эти столбцы «сливаются» таким образом, чтобы в дочерней таблице был только один такой столбец. Сливаемые столбцы должны иметь одинаковые типы данных, иначе возникает ошибка. Наследуемые проверочные ограничения и ограничения NOT NULL сливаются аналогичным образом. Так, например, слитный столбец будет помечен как NOT NULL, если любое из определений столбцов, из которых он получен, помечено как NOT NULL. Проверочные ограничения сливаются, если они имеют одинаковые имена, но если их условия различаются, слияние завершится ошибкой.

Отношение наследования между таблицами обычно устанавливается при создании дочерней таблицы с помощью предложения INHERITS оператора CREATE TABLE. Как вариант, к таблице, которая уже определена сообразным способом, можно добавить новое родительское отношение, используя вариант ALTER TABLE с предложением INHERIT. Для этого новая дочерняя таблица уже должна включать столбцы с теми же именами и типами, что и у столбцов родительской таблицы. Кроме того она должна включать проверочные ограничения с теми же именами и проверочными выражениями, что и у родительской. Схожим образом можно удалить из дочернего элемента наследственную связь, используя вариант ALTER TABLE с предложением NO INHERIT. Динамическое добавление и удаление таких наследственных связей может быть полезно, когда отношение наследования используется для партиционирования таблиц (см. раздел Партиционирование таблиц).

Одним из удобных способов создания совместимой таблицы, которая впоследствии станет новым потомком, является использование предложения LIKE в команде CREATE TABLE. Это создает новую таблицу с теми же столбцами, что и у исходной таблицы. Если в исходной таблице есть какие-либо проверочные ограничения, следует указать в LIKE параметр INCLUDING CONSTRAINTS, поскольку чтобы считаться совместимым, новый потомок должен иметь ограничения, соответствующие родительским.

Родительскую таблицу нельзя удалить, пока существуют ее потомки. Также нельзя удалить или изменить столбцы или проверочные ограничения дочерних таблиц, если они унаследованы от каких-либо родительских таблиц. Если требуется удалить таблицу и всех ее потомков, это легко сделать, удалив родительскую таблицу с параметром CASCADE (см. раздел Отслеживание зависимостей).

Команда ALTER TABLE будет распространять вниз по иерархии наследования любые изменения в определениях данных столбцов и в проверочных ограничениях. Опять же, удаление столбцов, зависящих от других таблиц, возможно только с помощью параметра CASCADE. ALTER TABLE следует тем же правилам слияния и отклонения дубликатов столбцов, которые применяются при выполнении CREATE TABLE.

Унаследованные запросы выполняют проверки прав доступа только для родительской таблицы. Так, например, предоставление разрешения UPDATE для таблицы cities подразумевает также разрешение на изменение строк в таблице capitals при обращении к ним через cities. Это сохраняет видимость того, что данные находятся (также) в родительской таблице. Но напрямую таблицу capitals нельзя изменить без дополнительных прав. Аналогичным образом политики защиты строк родительской таблицы (см. раздел Политики защиты строк) применяются к строкам, приходящим из дочерних таблиц при выполнении унаследованного запроса. Политики дочерней таблицы, если таковые имеются, применяются только в том случае, если ее имя явно указано в запросе; при этом любые политики, связанные с его родителем (или родителями), игнорируются.

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

Предупреждения относительно наследования

Обратите внимание, что не все команды SQL способны работать в иерархиях наследования. Команды, используемые для запроса или изменения данных либо изменения схемы (например, SELECT, UPDATE, DELETE, большинство вариантов ALTER TABLE, но не INSERT или ALTER TABLE ... RENAME), обычно по умолчанию включают дочерние таблицы и поддерживают указание ONLY, чтобы исключить их. Команды, выполняющие обслуживание и настройку базы данных (например REINDEX, VACUUM), обычно работают только с отдельными физическими таблицами и не поддерживают рекурсию по иерархиям наследования. Соответствующее поведение каждой отдельной команды описывается на ее справочной странице (глава Команды SQL).

Серьезным ограничением возможности наследования является то, что индексы (включая ограничения уникальности) и ограничения внешних ключей применяются только к отдельным таблицам, но не к их потомкам. Это верно как для ссылающихся, так и для ссылочных сторон ограничения внешнего ключа. Таким образом, применительно к вышеописанному примеру:

Если мы объявляем cities.name с ограничением UNIQUE или PRIMARY KEY, это не помешает таблице capitals иметь строки с именами, дублирующими строки в cities. И эти дублирующиеся строки по умолчанию будут отображаться в запросах из cities. На самом деле, по умолчанию таблица capitals вообще не имеет ограничения уникальности, и поэтому может содержать несколько строк с одинаковыми именами. Можно добавить для capitals ограничение уникальности, но это не предотвратит дублирование относительно cities.
Точно так же, если бы мы указали, что cities.name ссылается (REFERENCES) на какую-то другую таблицу, это ограничение не стало бы автоматически распространяться на capitals. В этом случае ситуацию можно исправить, вручную добавив такое же ограничение REFERENCES в capitals.
Указание на то, что столбец другой таблицы ссылается (REFERENCES) на cities(name), позволит этой другой таблице содержать названия городов, но не столиц. Для этого случая хорошего решения нет.

Некоторая функциональность, не реализованная для иерархий наследования, реализована для декларативного партиционирования. Тщательно взвесьте все за и против, прежде чем принять решение о том, подойдет ли для вашего приложения партиционирование с помощью устаревшего наследования.

Партиционирование таблиц

QHB поддерживает базовое партиционирование таблиц. В этом разделе описывается, почему и как нужно реализовать партиционирование при проектировании вашей базы данных.

Обзор

Партиционирование позволяет логически разделить одну большую таблицу на более мелкие физические части. Партиционирование может дать несколько преимуществ:

В определенных ситуациях можно значительно улучшить производительность запросов, особенно когда большинство часто используемых строк таблицы находятся в одной партиции или небольшом числе партиций. По сути партиционирование заменяет верхние уровни деревьев индексов, повышая вероятность того, что интенсивно используемые части этих индексов поместятся в памяти.
Когда запросы или изменения обращаются к значительной доле одной партиции, производительность можно улучшить, воспользовавшись преимуществом последовательного сканирования этой партиции вместо поиска по индексу, которое потребовало бы чтения с произвольным обращением к данным, разбросанным по всей таблице.
Массовые загрузки и удаления можно выполнить путем добавления или удаления партиций, если такой шаблон использования был предусмотрен при проектировании партиционирования. Выполнение команды ALTER TABLE DETACH PARTITION или удаление отдельной партиции с помощью DROP TABLE происходят намного быстрее, чем массовая операция. Кроме того, эти команды полностью исключают издержки, связанные с выполнением VACUUM после массовой операции DELETE.
Редко используемые данные можно перенести на более дешевые и медленные носители.

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

QHB предлагает встроенную поддержку для следующих форм партиционирования:

Партиционирование по диапазону
Таблица разбивается на «диапазоны», определяемые ключевым столбцом или набором столбцов, без перекрытия диапазонов значений, назначенных различным партициям. Например, можно разделить по диапазонам дат или идентификаторов для определенных бизнес-объектов. Границы каждого диапазона считаются включающими нижнее значение и исключающими верхнее. Например, если у одной партиции задан диапазон значений от 1 до 10, а у следующей — от 10 до 20, то значение 10 относится ко второй партиции, а не к первой.

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

Партиционирование по хешу
Таблица разбивается путем указания модуля и остатка для каждой партиции. Каждая партиция будет содержать строки, для которых при делении значения хеша ключа разбиения на указанный модуль получится заданный остаток.

Если вашему приложению нужно использовать другие, не перечисленные выше, формы партиционирования, вместо них можно использовать альтернативные методы, например наследование и представления UNION ALL. Такие методы предлагают гибкость, но, в сравнении с партиционированием, не дают некоторых преимуществ в производительности.

Декларативное партиционирование

QHB позволяет декларировать, как поделить таблицу на партиции. Такая разделенная таблица называется партиционированной таблицей. Эта декларация включает метод партиционирования (один из описанных выше) и список столбцов или выражений, которые будут использоваться в качестве ключа разбиения.

Сама партиционированная таблица является «виртуальной» и как таковая не хранится. Вместо этого хранилище относится к ее партициям, которые во всем остальном являются обычными таблицами, просто связанными с партиционированной. В каждой партиции хранится подмножество данных, определенное ее границами партиции. Все строки, добавленные в партиционированную таблицу, будут перенаправлены в соответствующие партиции в зависимости от значений столбца(ов) ключа разбиения. Если при изменении ключа разбиения строки она перестанет удовлетворять значениям границ исходной партиции, эта строка будет перемещена в другую партицию.

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

Невозможно превратить обычную таблицу в партиционированную или наоборот. Тем не менее, в партиционированную таблицу можно добавить в качестве партиции уже существующую обычную или партиционированную таблицу или удалить партицию из партиционированной таблицы, превратив ее в отдельную таблицу; это может упростить и ускорить многие процессы обслуживания. Более подробно вложенные команды ATTACH PARTITION и DETACH PARTITION описаны на справочной странице команды ALTER TABLE.

Кроме того, партиции могут быть сторонними таблицами, хотя при этом требуется немалая доля осмотрительности, поскольку именно пользователь отвечает за то, чтобы содержимое сторонней таблицы удовлетворяло правилу партиционирования. Также имеются и другие ограничения. Подробную информацию см. на справочной странице команды CREATE FOREIGN TABLE.

Пример декларативного партиционирования

Предположим, мы конструируем базу данных для большой компании по производству мороженого. Компания каждый день измеряет пиковые температуры, а также продажи мороженого в каждом регионе. Концептуально нам нужна таблица вроде этой:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

Мы знаем, что большинство запросов будут обращаться только к данным за последнюю неделю, месяц или квартал, поскольку основное использование этой таблицы будет заключаться в подготовке онлайн-отчетов для руководства. Чтобы уменьшить объем старых данных, которые необходимо хранить, мы решили сохранить данные за последние 3 года. В начале каждого месяца мы будем удалять данные самого старого месяца. В этой ситуации мы можем использовать партиционирование для удовлетворения всех наших требований к таблице результатов измерений.

Чтобы использовать декларативное партиционирование в этом случае, выполните следующие действия:

Создайте таблицу measurement как партиционированную таблицу, указав предложение PARTITION BY, включающее метод партиционирования (в данном случае RANGE) и список столбцов, используемых в качестве ключа разбиения.
```
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
```
Создайте партиции. В определении каждой партиции должны быть заданы границы, соответствующие методу партиционирования и ключу разбиения родительской таблицы. Обратите внимание, что указание границ таким образом, при котором значения новой партиции будут пересекаться со значениями в одной или нескольких существующих партициях, приведет к ошибке.

Созданные таким образом партиции во всех смыслах являются обычными таблицами QHB (или, возможно, сторонними таблицами). Для каждой партиции можно по отдельности указать табличное пространство и параметры хранения.

Для нашего примера в каждой партиции должны храниться данные за один месяц, чтобы соответствовать требованию об удалении данных по месяцам. Таким образом, команды могут выглядеть так:
```
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;
```
(Вспомните, что у соседних партиций может быть общее значение границы, поскольку верхние границы воспринимаются как исключающие.)

Чтобы реализовать вложенное партиционирование, укажите предложение PARTITION BY в командах, используемых для создания отдельных партиций, например:
```
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    PARTITION BY RANGE (peaktemp);
```
После создания партиции measurement_y2006m02 любые данные, добавленные в таблицу measurement, которая сопоставлена с measurement_y2006m02 (или данные, непосредственно вставленные в measurement_y2006m02, при условии, что это удовлетворяет ограничению ее партиции), будут затем перенаправлены в одну из партиций на основе столбца peaktemp. Указанный ключ разбиения может перекрываться с ключом разбиения родительской таблицы, хотя при указании границ вложенной партиции следует соблюдать осторожность, чтобы принимаемое ей множество данных входило в подмножество, допускаемое собственными границами партиции; система не пытается проверить, так ли это на самом деле.

Добавление данных в родительскую таблицу, не сопоставленную с одной из существующих партиций, вызовет ошибку; подходящую партицию следует создать вручную.

Вручную создавать табличные ограничения, описывающие границы партиций, нет необходимости. Подобные ограничения будут созданы автоматически.
Создайте для партиционированной таблицы индекс по ключевому столбцу (или столбцам), а также любые другие индексы, которые могут понадобиться. (В ключевом индексе нет особой необходимости, но в большинстве сценариев он полезен). Это автоматически создает соответствующий индекс для каждой партиции, и все партиции, которые вы создаете или присоединяете позже, тоже будут содержать индекс. Индекс или ограничение уникальности, декларируемые для партиционированной таблицы, являются «виртуальными» наравне с самой этой таблицей: фактические данные находятся в дочерних индексах отдельных таблиц-партиций.
```
CREATE INDEX ON measurement (logdate);
```
Убедитесь, что параметр конфигурации enable_partition_pruning в qhb.conf не выключен. Если это так, запросы не будут оптимизированы должным образом.

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

Обслуживание партиций

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

Самый простой вариант удаления старых данных — удалить ненужную партицию:

DROP TABLE measurement_y2006m02;

Таким образом можно очень быстро удалить миллионы записей, потому что не нужно индивидуально удалять каждую запись. Однако обратите внимание, что приведенная выше команда требует установления блокировки ACCESS EXCLUSIVE на родительскую таблицу.

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

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

Это позволяет выполнять дальнейшие операции с данными до удаления таблицы. Например, зачастую это подходящее время для проведения резервного копирования данных с помощью COPY, qhb_dump или подобных им инструментов. Также в этот момент может быть удобно агрегировать данные в более компактные форматы, выполнить с ними другие манипуляции или запустить формирование отчетов. Первая форма этой команды требует блокировки для ACCESS EXCLUSIVE родительской таблицы. Добавление квалификатора CONCURRENTLY, как во второй форме, позволяет требовать для операции отсоединения только блокировку SHARE UPDATE EXCLUSIVE для родительской таблицы, но подробнее эти ограничения описаны в [ALTER TABLE ... DETACH PARTITION].

Схожим образом мы можем добавить новую партицию для обработки новых данных. Мы можем создать пустую партицию в партиционированной таблице так же, как выше были созданы исходные партиции:

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

В качестве альтернативы иногда удобнее создать новую таблицу вне структуры партиций, а затем сделать ей подходящую партицию. Это позволяет загружать, проверять и преобразовывать данные до их появления в партиционированной таблице. Избежать утомительного повторения определения родительской таблицы помогает вариант CREATE TABLE ... LIKE:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'
-- возможно, какая-то дополнительная работа по подготовке данных

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

Команда ATTACH PARTITION требует установки блокировки SHARE UPDATE EXCLUSIVE для партиционированной таблицы.

Перед запуском команды ATTACH PARTITION рекомендуется создать ограничение CHECK для присоединяемой таблицы, соответствующее ожидаемому ограничению партиции, как показано выше. Таким образом, система сможет пропустить сканирование, которому в противном случае потребуется проверить неявное ограничение партиции. Без ограничения CHECK таблица будет сканироваться для проверки ограничения партиции, при этом удерживая блокировку ACCESS EXCLUSIVE для этой партиции. После завершения ATTACH PARTITION рекомендуется сбросить ставшее лишним ограничение CHECK. Если присоединяемая таблица сама является партиционированной, то каждая из ее вложенных партиций будет рекурсивно блокироваться и сканироваться до тех пор, пока не встретится подходящее ограничение CHECK или не будут достигнуты листовые партиции.

Аналогично, если в партиционированной таблице есть партиция DEFAULT, рекомендуется создать ограничение CHECK, исключающее ограничение присоединяемой партиции. Если этого не сделать, то партиция DEFAULT будет просканирована, чтобы убедиться, что она не содержит записей, которые должны быть расположены в присоединяемой партиции. Эта операция будет осуществляться при удержании блокировки ACCESS EXCLUSIVE на партиции DEFAULT. Если сама партиция DEFAULT является партиционированной таблицей, то каждая из ее партиций будет рекурсивно проверяться таким же образом, как и присоединяемая таблица, как упоминалось выше.

Как объяснено выше, для партиционированных таблиц можно создавать индексы так, чтобы они автоматически применялись ко всей иерархии. Это очень удобно, поскольку будут проиндексированы не только существующие партиции, но и любые партиции, которые будут созданы в будущем. Единственное ограничение заключается в том, что при создании такого партиционированного индекса невозможно использовать квалификатор CONCURRENTLY. Чтобы избежать длительных блокировок, можно использовать для партиционированной таблицы CREATE INDEX ON ONLY; такой индекс помечается как недопустимый, и партиции не получают индекс автоматически. Индексы для партиций можно создавать отдельно с помощью CONCURRENTLY, а затем присоединять к индексу на родительской таблице с помощью ALTER INDEX .. ATTACH PARTITION. Как только индексы для всех партиций присоединяются к родительскому индексу, тот автоматически помечается как допустимый. Пример:

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
...

Этот метод также можно использовать с ограничениями UNIQUE и PRIMARY KEY; индексы создаются неявно при создании ограничения. Пример:

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...

Ограничения

К партиционированным таблицам применяются следующие ограничения:

Ограничения уникальности (и, следовательно, первичных ключей) для партиционированных таблиц должны включать все столбцы ключа разбиения. Это ограничение существует, потому что отдельные индексы, образующие ограничение, могут напрямую обеспечивать уникальность только в своих партициях. Поэтому сама структура партиционирования должна гарантировать отсутствие дубликатов в разных партициях.
Невозможно создать исключающее ограничение, охватывающее всю партиционированную таблицу. Такое ограничение можно только наложить на каждую листовую партицию по отдельности. Это, опять же, проистекает из невозможности установить межпартициальные ограничения.
Триггеры BEFORE ROW для INSERT не могут изменить партицию, которая в итоге станет пунктом назначения для новой строки.
Смешивание временных и постоянных отношений в одном дереве партиционирования не допускается. Следовательно, если партиционированная таблица является постоянной, то такими же должны быть и ее партиции; то же касается временных партиционированных таблиц. При использовании временных отношений все члены дерева партиционирования должны быть из одного сеанса.

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

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

Партиции не могут иметь столбцы, отсутствующие в родительской таблице. Такие столбцы невозможно ни указать при создании партиций с помощью CREATE TABLE, ни добавить в партиции постфактум с помощью ALTER TABLE. Таблицы можно добавить в качестве партиций с помощью ALTER TABLE ... ATTACH PARTITION, только если их столбцы полностью соответствуют родительским.
Ограничения CHECK и NOT NULL, относящиеся к партиционированной таблице, всегда наследуются всеми ее партициями. Ограничения CHECK, помеченные как NO INHERIT, в партиционированных таблицах создавать нельзя. Кроме того, невозможно снять ограничение NOT NULL со столбца партиции, если такое же ограничение имеется в родительской таблице.
Использование указания ONLY при добавлении или удалении ограничения только в партиционированной таблице поддерживается, только пока в ней нет партиций. Если партиции существуют, применение ONLY вызовет ошибку. Вместе с тем, ограничения в самих партициях можно как добавлять, так и удалять (если они отсутствуют в родительской таблице).
Поскольку сама партиционированная таблица не содержит никаких данных, при попытках использовать для нее TRUNCATE ONLY всегда будет возвращаться ошибка.

Партиционирование через наследование

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

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

Пример партиционирования через наследование

В этом примере формируется структура партиционирования, равнозначная структуре из примера декларативного партиционирования выше. Выполните следующие действия:

Создайте «корневую» таблицу, от которой будут наследоваться все «дочерние» таблицы. Эта таблица не будет содержать данных. Не определяйте для этой таблицы никакие проверочные ограничения, если только вы не собираетесь применять их и ко всем дочерним таблицам. Также нет смысла определять для нее какие-либо индексы или ограничения уникальности. В нашем примере корневая таблица — это таблица measurement, как она была определена изначально:
```
CREATE TABLE measurement (
city_id         int not null,
logdate         date not null,
peaktemp        int,
unitsales       int
);
```
Создайте несколько «дочерних» таблиц, каждая из которых наследуется от корневой таблицы. Обычно в этих таблицах нет добавочных столбцов, кроме набора, унаследованного от корневой. Как и при декларативном партиционировании, эти таблицы во всех отношениях являются обычными таблицами QHB (или сторонними таблицами).
```
CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
```
Добавьте неперекрывающиеся ограничения таблиц в дочерние таблицы, чтобы определить в каждой из них допустимые значения ключей.

Типичные примеры:
```
CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
```
Убедитесь, что ограничения обеспечивают отсутствие пересечений между значениями ключей, разрешенными в разных дочерних таблицах. Распространенной ошибкой является установка ограничений диапазона следующим образом:
```
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
```
Это неправильно, так как неясно, к какой дочерней таблице относится значение ключа 200. Вместо этого было бы лучше создать дочерние таблицы так:
```
CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);

...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);
```

Для каждой дочерней таблицы создайте индекс по ключевому столбцу (или столбцам), а также любые другие индексы, которые могут понадобиться.

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

Мы хотим, чтобы приложение могло сказать INSERT INTO measurement... и данные были перенаправлены в соответствующую дочернюю таблицу. Это можно устроить, присоединив к корневой таблице подходящую триггерную функцию. Если данные будут добавляться только к последнему потомку, можно вызвать очень простую триггерную функцию:
```
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
```
После создания функции мы создаем триггер, который вызывает эту триггерную функцию:
```
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
```
Эту триггерную функцию следует переопределять ежемесячно, чтобы она всегда указывала на текущую дочернюю таблицу. Однако определение самого триггера обновлять не нужно.

Нам может понадобиться, чтобы при добавлении данных сервер автоматически находил дочернюю таблицу, в которую должна быть добавлена строка. Это можно сделать с помощью более сложной триггерной функции, например:
```
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
         INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
```
Определение триггера такое же, как и раньше. Обратите внимание, что каждая проверка IF должна точно соответствовать ограничению CHECK для своей дочерней таблицы.

Хотя эта функция сложнее варианта с одним месяцем, ее не требуется так часто обновлять, поскольку при необходимости ветви можно добавить заранее.

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

Другой подход к перенаправлению добавлений данных в соответствующую дочернюю таблицу заключается в установке в корневой таблице правил вместо триггера. Например:
```
CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
```
Правило гораздо затратнее триггера, но эти затраты относятся ко всему запросу, а не к каждой строке, поэтому такой метод может быть выигрышным при массовом добавлении данных. Однако в большинстве случаев метод с триггером будет обеспечивать лучшую производительность.

Имейте в виду, что COPY игнорирует правила. Если вы хотите использовать для добавления данных эту команду, придется копировать их в правильную дочернюю таблицу, а не непосредственно в корневую. При этом COPY запускает триггеры, поэтому если вы используете вариант с триггерами, эту команду можно применять как обычно.

Другой недостаток варианта с правилами заключается в том, что не существует простого способа вызвать ошибку, если набор правил не охватывает дату добавления; вместо этого данные будут просто отправлены в корневую таблицу.
Убедитесь, что в qhb.conf не выключен параметр конфигурации constraint_exclusion; иначе дочерние таблицы могут сканироваться, когда в этом нет необходимости.

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

Обслуживание таблиц, партиционированных через наследование

Чтобы быстро удалить старые данные, просто удалите дочернюю таблицу, которая больше не нужна:

DROP TABLE measurement_y2006m02;

Чтобы удалить дочернюю таблицу из таблицы иерархии наследования, но сохранить доступ к ней как к отдельной таблице:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

Чтобы добавить новую дочернюю таблицу для обработки новых данных, создайте пустую дочернюю таблицу так же, как выше были созданы исходные дочерние таблицы:

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

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

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- возможно, какая-то дополнительная работа по подготовке данных
ALTER TABLE measurement_y2008m02 INHERIT measurement;

Предупреждения относительно партиционирования через наследование

К партиционированию, реализованному через наследование, применяются следующие предупреждения:

Не существует автоматического способа проверить, являются ли все ограничения CHECK взаимоисключающими. Безопаснее создать код, генерирующий дочерние таблицы и создающий и/или изменяющий связанные объекты, чем каждый раз делать это вручную.
Индексы и ограничения внешнего ключа применяются к отдельным таблицам, а не к наследующим от них потомкам, поэтому следует учитывать некоторые касающиеся их предупреждения.
Показанные здесь схемы предполагают, что значения ключевых столбцов строки никогда не меняются или, по крайней мере, не меняются настолько, чтобы требовать их перемещения в другую партицию. Команда UPDATE, которая попытается сделать это, закончится с ошибкой из-за ограничений CHECK. Если нужно обработать такие случаи, можно поместить в дочерние таблицы подходящие триггеры изменения, но это значительно усложняет управление общей структурой.
Если вы используете команды VACUUM или ANALYZE вручную, не забывайте, что их нужно запускать для каждой дочерней таблицы по отдельности. Например, подобная команда:
```
ANALYZE measurement;
```
будет обрабатывать только корневую таблицу.
Операторы INSERT с предложениями ON CONFLICT вряд ли будут работать должным образом, поскольку действие ON CONFLICT выполняется только в случае нарушений уникальности в указанном целевом отношении, но не в его дочерних отношениях.
Если приложение явно не осведомлено о схеме партиционирования, для трассировки строк в нужную дочернюю таблицу будут необходимы триггеры или правила. Триггеры могут быть сложными в написании и будут намного медленнее, чем трассировка кортежей, выполняемая внутренне посредством декларативного партиционирования.

Отсечение партиций

Отсечение партиций — это метод оптимизации запросов, повышающий производительность для декларативно партиционированных таблиц. Например:

SET enable_partition_pruning = on;                 -- по умолчанию
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

Без отсечения партиций вышеупомянутый запрос будет сканировать каждую из партиций таблицы measurement. При включенном отсечении партиций планировщик изучит определение каждой партиции и докажет, что ее не нужно сканировать, поскольку в ней не может быть строк, соответствующих предложению WHERE из запроса. Когда планировщик может доказать это, он исключает (отсекает) партицию из плана запроса.

Используя команду EXPLAIN и параметр конфигурации enable_partition_pruning, можно показать разницу между планом, для которого партиции были отсечены, и тем, для которого они не отсекались. Типичный неоптимизированный план для этого типа установки таблицы:

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

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

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

Обратите внимание, что отсечение партиций обусловлено только ограничениями, неявно определенными ключами разбиения, а не наличием индексов. Поэтому нет необходимости определять индексы по ключевым столбцам. Необходимость создания индекса для данной партиции зависит от того, какую ее часть будут сканировать обращающиеся к этой партиции запросы: значительную или малую. Индекс будет полезен в последнем случае, но не в первом.

Отсечение партиций можно провести не только во время планирования данного запроса, но и во время его выполнения. Это полезно, поскольку позволяет отсечь больше партиций, когда предложения содержат выражения, значения которых неизвестны во время планирования запроса (например параметры, определенные в операторе PREPARE), значение, полученное из подзапроса, или параметризованное значение на внутренней стороне соединения с вложенным циклом. Отсечение партиций во время выполнения запроса можно провести в любой из следующих моментов времени:

Во время инициализации плана запроса. Здесь можно выполнить отсечение партиций для значений параметров, которые становятся известны на этапе инициализации выполнения. Партиции, отсеченные на этом этапе, не будут отображаться в запросе EXPLAIN или EXPLAIN ANALYZE. Определить количество удаленных на этом этапе партиций можно, наблюдая за свойством «Subplans Removed» в выводе EXPLAIN.
Во время фактического выполнения плана запроса. Здесь также можно выполнить отсечение партиций, чтобы удалить их с помощью значений, которые становятся известны только во время фактического выполнения запроса. Это включает в себя значения из подзапросов и из параметров времени выполнения, например, значения из параметризованных соединений с вложенным циклом. Поскольку значение этих параметров во время выполнения запроса может изменяться много раз, отсечение партиции выполняется всякий раз, когда изменяется один из используемых для отсечения параметров выполнения. Определение того, были ли партиции отсечены на этом этапе, требует тщательной проверки свойства loops в выводе EXPLAIN ANALYZE. Подпланы, соответствующие различным партициям, могут иметь разные значения, в зависимости от того, сколько раз каждый из них отсекался во время выполнения. Некоторые могут быть показаны, как (never executed) (никогда не выполнялся) если они постоянно отсекались.

Отсечение партиций можно выключить, изменив значение enable_partition_pruning.

Партиционирование и исключение по ограничению

Исключение по ограничению — это метод оптимизации запросов, схожий с отсечением партиций. Хотя он в основном применяется для партиционирования, реализованного с использованием устаревшего метода наследования, он может применяться и для других целей, в том числе с декларативным партиционированием.

Исключение по ограничению работает практически так же, как отсечение партиций, за исключением того, что оно использует ограничения CHECK каждой таблицы – откуда и получило свое название — тогда как отсечение партиций использует границы партиций таблицы, которые существуют только в случае декларативного партиционирования. Другое отличие состоит в том, что исключение ограничений применяется только во время планирования; во время выполнения партиции не удаляются.

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

Значением по умолчанию (и рекомендуемым) для параметра constraint_exclusion является не on (включен) и не off (выключен), а промежуточное значение, называемое partition, при котором метод применяется только к запросам, которые предположительно будут работать с таблицами, партиционированными через наследование. При значении on планировщик будет проверять ограничения CHECK во всех запросах, в том числе простых, в которых вышеупомянутое преимущество вряд ли проявится.

К исключению по ограничению применимы следующие предупреждения:

Исключение по ограничению применяется только при планировании запросов, в отличие от отсечения партиций, которое также может применяться и при их выполнении.
Исключение по ограничению работает только тогда, когда предложение WHERE в запросе содержит константы (или предоставленные извне параметры). Например, сравнение с изменяемой функцией, такой как CURRENT_TIMESTAMP, оптимизировать нельзя, поскольку планировщик не может знать, в какую дочернюю таблицу попадет значение функции во время выполнения.
Делайте ограничения на партиции простыми, иначе планировщик не сможет доказать, что дочерние таблицы необязательно посещать. Используйте простые условия равенства для партиционирования по списку или простые проверки диапазона для партиционирования по диапазону, как показано в предыдущих примерах. Хорошее практическое правило заключается в том, что ограничения партиционирования должны содержать только сравнения столбца(ов) разбиения с константами, где используются операторы, поддерживающие индексы B-деревья, поскольку в ключе разбиения допускаются только столбцы, индексируемые по B-дереву.
Во время исключения по ограничению проверяются все ограничения для всех потомков родительской таблицы, поэтому большое количество потомков может значительно увеличить время планирования запросов. Таким образом, устаревшее партиционирование через наследование будет хорошо работать, пока число дочерних таблиц не перевалит за сто; не пытайтесь применять его с многими тысячами дочерних таблиц.

Оптимальные методы декларативного партиционирования

Выбирать способ партиционирования таблицы следует тщательно, так как неудовлетворительный проект может негативно повлиять на производительность планирования и выполнения запросов.

Одним из наиболее важных проектных решений является выбор столбца (или столбцов), по которым вы партиционируете свои данные. Зачастую лучшим выбором будет партиционирование по столбцу или набору столбцов, которые чаще всего встречаются в предложениях WHERE запросов, выполняемых в партиционированной таблице. Те предложения WHERE, которые совместимы с ограничениями границ партиций, можно использовать для отсечения ненужных партиций. Тем не менее требования ограничений PRIMARY KEY или UNIQUE могут заставить вас выбрать другие столбцы. Удаление нежелательных данных также является фактором, который следует учитывать при планировании стратегии партиционирования. Целую партицию можно отсоединить довольно быстро, поэтому может быть полезно разработать стратегию партиционирования таким образом, чтобы все данные, удаляемые одновременно, находились в одной партиции.

Выбор целевого числа партиций, на которые следует разделить таблицу, тоже является критически важным решением. Отсутствие достаточного количества партиций может означать, что индексы остаются слишком большими, а локальность данных плохой, что может привести к низкому проценту попадания в кэш. Однако разделение таблицы на слишком большое число партиций также может вызвать проблемы. Слишком большое количество партиций может означать увеличение времени планирования запросов и более высокому потреблению памяти при планировании и выполнении запросов, что подробно описано ниже. Кроме того, при выборе способа партиционирования таблицы важно учитывать, какие изменения могут произойти в будущем. Например, если вы решили иметь по одной партицию для каждого клиента и в настоящее время у вас небольшое количество крупных клиентов, подумайте о последствиях, если через несколько лет у вас будет много мелких клиентов. Возможно, в таком случае, лучше выбрать партиционирование по хешу (HASH) и создать разумное количество партиций, вместо того чтобы пытаться партиционировать их по списку (LIST) и надеяться, что количество клиентов не превысит порог, по которому целесообразно партиционировать данные.

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

Важно учитывать издержки партиционирования при планировании и выполнении запросов. Планировщик запросов обычно способен достаточно хорошо обрабатывать иерархии партиционирования с несколькими тысячами партиций при условии, что типичные запросы позволяют ему отсекать значительную часть партиций. Если после отсечения партиций планировщиком их остается еще много, время планирования увеличивается, а потребление памяти возрастает. Еще одна причина для беспокойства по поводу большого количества партиций заключается в том, что со временем потребление памяти сервером может значительно возрасти, особенно если множество сеансов обращается к огромному числу партиций. Это связано с тем, что каждая партиция требует загрузки своих метаданных в локальную память каждого сеанса, который к ней обращается.

С рабочими нагрузками типа хранилища данных имеет смысл использовать больше партиций, чем с рабочей нагрузкой типа OLTP. Как правило, в хранилищах данных планирование запросов занимает меньше времени, так как большая часть времени обработки тратится на выполнение запросов. При любом из этих двух типов рабочих нагрузок важно принимать правильные решения как можно раньше, поскольку процесс переразбиения больших объемов данных может оказаться чрезвычайно медленным. Для оптимизации стратегии партиционирования зачастую бывает полезно моделирование предполагаемой рабочей нагрузки. Никак нельзя просто исходить из предположения, что чем больше партиций, тем лучше, или наоборот.

Сторонние данные

QHB реализует части спецификации SQL/MED, позволяя получать доступ к данным, находящимся вне QHB, используя обычные запросы SQL. Такие данные называются сторонними.

Доступ к сторонним данным осуществляется с помощью обертки сторонних данных. Обертка сторонних данных представляет собой библиотеку, которая может взаимодействовать с внешним источником данных, скрывая детали подключения к нему и получения из него данных. Несколько оберток сторонних данных доступны в виде модулей из каталога share/extension; см. раздел Расширения. Другие типы оберток сторонних данных можно найти среди продуктов сторонних производителей. Если ни одна из существующих оберток сторонних данных не соответствует вашим потребностям, вы можете написать свою собственную; см. главу Написание обертки сторонних данных.

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

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

Дополнительную информацию см. на справочных страницах команд CREATE FOREIGN DATA WRAPPER, CREATE SERVER, CREATE USER MAPPING, CREATE FOREIGN TABLE и IMPORT FOREIGN SCHEMA.

Другие объекты базы данных

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

Представления
Функции, процедуры и операторы
Типы данных и домены
Триггеры и правила перезаписи

Более подробно эти темы рассматриваются в главе Серверное программирование.

Отслеживание зависимостей

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

Чтобы обеспечить целостность структуры всей базы данных, QHB делает так, чтобы вы не могли удалить объекты, от которых все еще зависят другие объекты. Например, попытка удалить таблицу товаров, которая рассматривалась в подразделе Ограничение внешнего ключа, с зависящей от нее таблицей заказов, приведет к появлению сообщения об ошибке приблизительно такого содержания:

DROP TABLE products;

ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint orders_product_no_fkey on table orders depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- ОШИБКА: удалить таблицу products невозможно, потому что от нее зависят другие объекты
-- ПОДРОБНОСТИ: ограничение orders_product_no_fkey для таблицы orders зависит от таблицы products
-- СОВЕТ: Для удаления зависимых объектов используйте DROP ... CASCADE

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

DROP TABLE products CASCADE;

и все зависимые объекты будут удалены, равно как и любые объекты, которые рекурсивно зависят от них. В данном случае таблица заказов не удаляется, а удаляется только ограничение внешнего ключа. На этом все заканчивается, потому что от ограничения внешнего ключа ничего не зависит. (Если вы хотите проверить, что будет делать DROP ... CASCADE, запустите DROP без CASCADE и прочитайте вывод DETAIL.)

В QHB почти все команды DROP поддерживают указание CASCADE. Конечно, характер возможных зависимостей зависит от типа объекта. Кроме того, вместо CASCADE можно написать RESTRICT, чтобы получить поведение по умолчанию, которое должно предотвратить удаление объектов, от которых зависят любые другие объекты.

Примечание
Согласно стандарту SQL, в команде DROP требуется явно указывать либо RESTRICT, либо CASCADE. На самом деле ни в одной СУБД это правило не действует, но будет ли поведением по умолчанию RESTRICT или CASCADE, зависит от конкретной СУБД.

Если в команде DROP перечисляется несколько объектов, CASCADE требуется только при наличии зависимостей вне указанной группы. Например, в команде, скажем, DROP TABLE tab1, tab2 наличие внешнего ключа, ссылающегося на tab1 из tab2 не будет означать, что для успешного выполнения команды необходим CASCADE.

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

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow',
                             'green', 'blue', 'purple');

CREATE TABLE my_colors (color rainbow, note text);

CREATE FUNCTION get_color_note (rainbow) RETURNS text AS
  'SELECT note FROM my_colors WHERE color = $1'
  LANGUAGE SQL;

(Функции языка SQL рассматриваются в разделе Функции на языке запросов (SQL).) QHB будет знать, что функция get_color_note зависит от типа rainbow: удаление типа приведет к принудительному удалению функции, поскольку тип ее аргумента больше не определяется. Но QHB не будет считать, что get_color_note зависит от таблицы my_colors, и поэтому не будет удалять эту функцию при удалении таблицы. Хотя у этого подхода есть недостатки, но есть и преимущества. При отсутствии таблицы функция в некотором смысле все еще будет действующей, хотя ее выполнение вызовет ошибку; создание новой таблицы с тем же именем позволит функции снова заработать.