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

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

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

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

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

QHB включает в себя значительный набор встроенных типов данных, которые подходят для многих приложений. Пользователи также могут определять свои собственные типы данных. Большинство встроенных типов данных имеют очевидные имена и семантику, подробное объяснение см. главу Типы данных. Некоторыми из часто используемых типов данных являются integer для целых чисел, numeric для возможно дробных чисел, text для символьных строк, date для дат, time для значений timestamp дня и 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 для деталей.

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

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

  • Сгенерированные столбцы обновляются после запуска триггеров. Поэтому изменения, внесенные в базовые столбцы в триггере BEFORE будут отражены в сгенерированных столбцах. Но, наоборот, не допускается доступ к сгенерированным столбцам в триггерах BEFORE.

Ограничения

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

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

Контрольные ограничения

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

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. Поскольку большинство выражений будут иметь нулевое значение, если какой-либо операнд будет нулевым, они не будут предотвращать нулевые значения в ограниченных столбцах. Чтобы столбец не содержал нулевых значений, можно использовать ограничение NOT NULL, описанное в следующем разделе.

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

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

Ограничения not-null

Ограничение not-null (Not-Null constraints) указывает, что столбец не должен принимать null значение. Пример синтаксиса:

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

Ограничение not-null всегда записывается как ограничение столбца. Ограничение not-null функционально эквивалентно созданию контрольного ограничения CHECK (column_name 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 отсутствует в стандарте SQL и не должно использоваться в переносимых приложениях. (Оно было добавлено только в QHB для совместимости с некоторыми системами баз данных). Однако такой синтаксис позволяет легко переключать ограничение в файле сценария. Например, вы можете начать с:

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

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

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

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

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 могут не следовать этому правилу. Поэтому будьте осторожны при разработке приложений, предназначенных для переносимости.

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

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

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.

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

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

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

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

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

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, которые отсутствуют в таблице продуктов.

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

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

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 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 (таким образом, сочетание нулевых и ненулевых значений гарантированно приведет к сбою ограничения MATCH FULL). Если вы не хотите, чтобы ссылающиеся строки не удовлетворяли ограничению внешнего ключа, объявите ссылающиеся столбцы как NOT NULL.

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

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

Ограничения исключения

Ограничения исключения (Exclusion constraints) гарантируют, что, если любые две строки сравниваются по указанным столбцам или выражениям с использованием указанных операторов, то по крайней мере один из этих операторов сравнения вернет false или null. Синтаксис:

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

Смотрите также CREATE TABLE ... CONSTRAINT ... EXCLUDE для подробностей.

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

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

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

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

Имя столбцаНазначение
tableoidOID таблицы, содержащей эту строку. Этот столбец особенно удобен для запросов, которые выбираются из иерархий наследования (см. раздел Наследование, поскольку без него трудно определить, из какой отдельной таблицы получена строка. 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 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;

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

Снятие ограничения

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

ALTER TABLE products DROP CONSTRAINT some_name;

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

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

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

ALTER TABLE products ALTER COLUMN product_no DROP 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 table_name OWNER TO new_owner;

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

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

GRANT UPDATE ON accounts TO joe;

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

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

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

REVOKE ALL ON accounts FROM PUBLIC;

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

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

Доступные привилегии:

ПривилегияОписание
SELECTПозволяет выполнять выбор из любого столбца или определенного столбца (столбцов) таблицы, представления, материализованного представления или другого табличного объекта. Также позволяет использовать *COPY TO. Эта привилегия также необходима для ссылки на существующие значения столбца в *UPDATE или DELETE. Для последовательностей эта привилегия также позволяет использовать функцию currval. Для больших объектов эта привилегия позволяет объекту быть прочитанным.
INSERTРазрешает вставку новой строки в таблицу, представление и т. д. Может быть предоставлено определенным столбцам, в этом случае только командам INSERT могут быть назначены только эти столбцы (поэтому другие столбцы будут получать значения по умолчанию). Также позволяет использовать COPY FROM
UPDATEРазрешает обновление любого столбца или определенного столбца (столбцов) таблицы, представления и т. Д. (На практике любая нетривиальная команда UPDATE также требует привилегии SELECT, поскольку она должна ссылаться на столбцы таблицы, чтобы определить, какие строки обновлять, и / или для вычисления новых значений для столбцов). SELECT ... FOR UPDATE и SELECT ... FOR SHARE также требуют эту привилегию как минимум для одного столбца в дополнение к привилегии SELECT. Для последовательностей эта привилегия позволяет использовать функции nextval и setval. Для больших объектов эта привилегия позволяет писать или очищать (TRUNCATE) объект.
DELETEПозволяет удалить строки из таблицы, представления и т. д. (На практике любой нетривиальной команде DELETE также потребуется привилегия SELECT, поскольку она должна ссылаться на столбцы таблицы, чтобы определить, какие строки следует удалить).
TRUNCATEПозволяет очистить таблицы, представления и т. д.
REFERENCESПозволяет создать ограничение внешнего ключа, ссылающегося на таблицу или определенные столбцы таблицы.
TRIGGERПозволяет создать триггер для таблицы, представления и т. д.
CREATEДля баз данных позволяет создавать новые схемы и публикации в базе данных. Для схем позволяет создавать новые объекты внутри схемы. Чтобы переименовать существующий объект, вы должны владеть объектом и иметь эту привилегию для содержащей схемы.Для табличных пространств позволяет создавать таблицы, индексы и временные файлы в табличном пространстве и позволяет создавать базы данных, в которых табличное пространство является табличным пространством по умолчанию. (Обратите внимание, что отзыв этой привилегии не изменит размещение существующих объектов).
CONNECTПозволяет грантополучателю подключаться к базе данных. Эта привилегия проверяется при запуске соединения (в дополнение к проверке любых ограничений, налагаемых qhb_hba.conf).
TEMPORARYПозволяет создавать временные таблицы при использовании базы данных.
EXECUTEПозволяет вызывать функцию или процедуру, включая использование любых операторов, которые реализованы поверх функции. Это единственный тип привилегий, применимый к функциям и процедурам.
USAGEДля процедурных языков позволяет использовать язык для создания функций на этом языке. Это единственный тип привилегий, применимый к процедурным языкам.Для схем разрешает доступ к объектам, содержащимся в схеме (при условии, что собственные требования к привилегиям объектов также выполнены). По сути, это позволяет получателю « искать » объекты в схеме. Без этого разрешения все еще можно увидеть имена объектов, например, путем запроса системных каталогов. Кроме того, после отзыва этого разрешения существующие сеансы могут иметь операторы, которые ранее выполняли этот поиск, так что это не является полностью безопасным способом предотвращения доступа к объекту.Для последовательностей позволяет использовать функции currval и nextval.Для типов и доменов позволяет использовать тип или домен при создании таблиц, функций и других объектов схемы. (Обратите внимание, что эта привилегия не управляет всем « использованием » типа, например значениями типа, появляющимися в запросах. Она только предотвращает создание объектов, зависящих от типа. Основная цель этой привилегии - контролировать, какие пользователи могут создать зависимости от типа, которые могут помешать владельцу изменить тип позже).Для оболочек сторонних данных позволяет создавать новые серверы с использованием *foreign-data wrapper. Для сторонних серверов позволяет создавать сторонние таблицы с использованием сервера. Получатели могут также создавать, изменять или удалять свои собственные сопоставления пользователей, связанные с этим сервером.

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

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

  • привилегии CONNECT и TEMPORARY (создание временных таблиц) для баз данных; EXECUTE привилегию для функций и процедур;
  • привилегия USAGE для языков и типов данных(включая домены).

Владелец объекта может, конечно, отозвать права по умолчанию и явно предоставленные привилегии. (Для обеспечения максимальной безопасности выполните команду REVOKE в той же транзакции, в которой создается объект тогда не будет промежутка, в котором другой пользователь может использовать объект). Кроме того, эти настройки привилегий по умолчанию можно изменить с помощью команды ALTER DEFAULT PRIVILEGES.

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

Таблица Сокращения привилегий ACL

ЛьготаСокращенное названиеПрименимые типы объектов
SELECTr («читать»)LARGE OBJECT, SEQUENCE, TABLE (и табличные объекты), столбец таблицы
INSERTa («добавить»)TABLE, столбец таблицы
UPDATEw («написать»)LARGE OBJECT, SEQUENCE, TABLE, столбец таблицы
DELETEdTABLE
TRUNCATEDTABLE
REFERENCESxTABLE, столбец таблицы
TRIGGERtTABLE
CREATECDATABASE, SCHEMA, TABLESPACE
CONNECTcDATABASE
TEMPORARYTDATABASE
EXECUTEXFUNCTION, PROCEDURE
USAGEUDOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

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

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

Тип объектаВсе привилегииPUBLIC привилегии по умолчанию
DATABASECTcTc
DOMAINUU
FUNCTION или PROCEDUREXX
FOREIGN DATA WRAPPERUnone
FOREIGN SERVERUnone
LANGUAGEUU
LARGE OBJECTrwnone
SCHEMAUCnone
SEQUENCErwUnone
TABLE (и табличные объекты)arwdDxtnone
Столбец таблицыarwxnone
TABLESPACECnone
TYPEUU

Привилегии, которые были предоставлены для определенного объекта, отображаются в виде списка записей 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;

Тогда команда qsql \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 в зависимости от типа объекта, как описано выше. При первом предоставлении или отзыве объекта создаются экземпляры привилегий по умолчанию (например, miriam=arwdDxt/miriam), а затем они изменяются в соответствии с указанным запросом. Аналогично, записи отображаются в «Column privileges» только для столбцов с привилегиями не по умолчанию. (Примечание: для этой цели «привилегии по умолчанию» всегда означают встроенные привилегии по умолчанию для типа объекта. Объект, чьи привилегии были затронуты командой ALTER DEFAULT PRIVILEGES, всегда будет отображаться с явной записью привилегии, включающей эффекты ALTER).

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

Политики безопасности строк

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

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

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

Чтобы указать, какие строки являются видимыми или изменяемыми в соответствии с политикой, требуется выражение, которое возвращает логический результат. Это выражение будет оцениваться для каждой строки перед проверкой любых условий или выполнения функций, поступающих из пользовательского запроса. (Единственным исключением из этого правила являются герметичные функции, которые гарантированно не допускают утечки информации - оптимизатор может выбрать применение таких функций перед проверкой безопасности строк). Строки, для которых выражение не возвращает 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:

-- Simple passwd-file based example
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;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
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');

-- Be sure to enable row level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
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')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

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

-- admin can view all rows and fields
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)

-- Test what Alice is able to do
qhb=> set role alice;
SET
qhb=> table passwd;
ERROR:  permission denied for relation 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
-- Alice is allowed to change her own real_name, but no others
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"
qhb=> delete from passwd;
ERROR:  permission denied for relation passwd
qhb=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd
-- Alice can change her own password; RLS silently prevents updating other rows
qhb=> update passwd set pwhash = 'abc';
UPDATE 1

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

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

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

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

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

-- definition of privilege groups
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 is the administrator
GRANT SELECT ON groups TO public;

-- definition of users' privilege levels
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;

-- table holding the information to be protected
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;

-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's 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));

-- we rely only on RLS to protect the information table
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 от mallory». Тем не менее, здесь есть условия гонки. Если mallory одновременно делает, скажем,

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

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

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

Для получения дополнительной информации см. CREATE POLICY и ALTER TABLE.

Схемы

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

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

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

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

  • позволить многим пользователям использовать одну базу данных, не мешая друг другу;

  • организовать объекты базы данных в логические группы, чтобы сделать их более управляемыми;

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

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

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

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

CREATE SCHEMA myschema;

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

schema.table

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

На самом деле, даже более общий синтаксис

database.schema.table

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

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

CREATE TABLE myschema.mytable (
 ...
);

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

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

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

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

CREATE SCHEMA schema_name AUTHORIZATION user_name;

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

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

Схема Public

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

CREATE TABLE products (...);

и:

CREATE TABLE public.products (...);

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

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

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

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

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

SHOW search_path;

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

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

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

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

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

SET search_path TO myschema,public;

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

DROP TABLE mytable;

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

Мы могли бы также написать:

SET search_path TO myschema;

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

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

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

OPERATOR(schema.operator)

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

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

Схемы и привилегии

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

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

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

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

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

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

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

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

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

  • Ограничить обычных пользователей частными схемами. Для этого выполните REVOKE CREATE ON SCHEMA public FROM PUBLIC и создайте схему для каждого пользователя с тем же именем, что и у этого пользователя. Напомним, что путь поиска по умолчанию начинается с $user, который преобразуется в имя пользователя. Поэтому, если у каждого пользователя есть отдельная схема, он получает доступ к своим собственным схемам по умолчанию. После принятия этого шаблона в базе данных, в которую уже вошли ненадежные пользователи, рассмотрите возможность аудита схемы public для объектов с именами, подобными объектам в схеме pg_catalog. Этот шаблон является шаблоном использования защищенной схемы, если только недоверенный пользователь не является владельцем базы данных или не имеет привилегии CREATEROLE, в этом случае шаблон использования защищенной схемы не работает.

  • Удалите общедоступную схему из пути поиска по умолчанию, изменив qhb.conf или введя команду ALTER ROLE ALL SET search_path = "$user". Каждый пользователь сохраняет возможность создавать объекты в схеме public, но выбирать эти объекты возможно только используя квалифицированные имена. Несмотря на то, что ссылки на квалифицированные таблицы в порядке, вызовы функций в схеме public будут небезопасными или ненадежными. Если вы создаете функции или расширения в схеме public, используйте вместо этого первый шаблон. В противном случае, как и в первом шаблоне, это безопасно, если только ненадежный пользователь не является владельцем базы данных или не имеет привилегии CREATEROLE.

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

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

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

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

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

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

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

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

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

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- in feet
);

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

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

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

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

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

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, а не к таблицам ниже cities в иерархии наследования. Многие из команд, которые мы уже обсуждали - SELECT, UPDATE и DELETE - поддерживают ключевое слово ONLY.

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

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

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

В некоторых случаях вы можете узнать, из какой таблицы произошла конкретная строка. В каждой таблице есть системный столбец с именем tableoid который может указать 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) jбъединенив 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 всегда вставляет именно в указанную таблицу.

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

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

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

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

Родительская таблица не может быть удалена, пока остаются ее дочерние элементы. Столбцы или контрольные ограничения дочерних таблиц также не могут быть удалены или изменены, если они унаследованы от каких-либо родительских таблиц. Если вы хотите удалить таблицу и всех ее потомков, одним из простых способов является удаление родительской таблицы с опцией 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 ссылается на какую-то другую таблицу, это ограничение не будет автоматически распространяться на capitals. В этом случае вы можете обойти это, вручную добавив такое же ограничение REFERENCES в capitals.

  • Указание на то, что столбец из другой таблицы ссылается на cities(name) позволит другой таблице содержать названия городов, но не названия столиц. Для этого случая нет хорошего пути решения.

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

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

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

Обзор

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

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

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

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

  • Редко используемые данные могут быть перенесены на более дешевые и медленные носители данных.

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

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

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

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

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

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

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

В настоящее время поддерживаются следующие методы партиционирования: диапазон (range), список (list) и хэш (hash).

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

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

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

  • Ограничения CHECK и NOT NULL для партиционированной таблицы всегда наследуются всеми ее партициями. Контрольные ограничения, помеченные как NO INHERIT, не могут быть созданы для партиционированных таблиц.

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

  • Поскольку партиционированная таблица не имеет никаких данных непосредственно, попытки использовать TRUNCATE ONLY в партиционированной таблице всегда будут возвращать ошибку.

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

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

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

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

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

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

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

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

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

  1. Создайте таблицу measurement как партиционированную таблицу, указав условие PARTITION BY, которое включает метод партиционирования (в данном случае RANGE) и список столбцов, которые следует использовать в качестве ключа партиционирования.
    При желании вы можете использовать несколько столбцов в ключе партиционирования для разделения диапазона. Конечно, это часто приводит к большему количеству партиций, каждый из которых в отдельности меньше. С другой стороны, использование меньшего количества столбцов может привести к более грубым критериям партиционирования с меньшим числом партиций. Запрос, обращающийся к партиционированной таблице, должен будет сканировать меньшее количество партийий, если условия включают некоторые или все эти столбцы. Например, рассмотрите диапазон таблицы, разделенный с использованием столбцов lastname и firstname (в этом порядке) в качестве ключа партиционирования.
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
  1. Создать партиции. Определение каждой партиции должно указывать границы, которые соответствуют методу партиционирования и ключу партиционирования родителя. Обратите внимание, что указание границ таким образом, что значения новой партиции будут совпадать со значениями в одном или нескольких существующих партициях, приведет к ошибке. Вставка данных в родительскую таблицу, которая не сопоставлена ни с одной из существующих партиций, приведет к ошибке - соответствующие партиции должны быть добавлены вручную. Созданные таким образом партиции во всех смыслах являются обычными таблицами 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);

После создания партициq measurement_y2006m02 любые данные, вставленные в партицию которая сопоставлена с measurement_y2006m02 (или данные, которые непосредственно вставлены в measurement_y2006m02, при условии, что оно удовлетворяет ограничению на партиции), будут дополнительно перенаправлены в одну из партиций на основе столбца peaktemp. Указанный ключ партиции может перекрываться с ключом партиции родителя, хотя следует соблюдать осторожность при указании границ партиции, чтобы набор данных, которые она принимает, составлял подмножество того, что позволяют собственные границы партиции - система не пытается проверить, так ли это на самом деле.

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

    CREATE INDEX ON measurement (logdate);
    
  2. Убедитесь, что параметр конфигурации enable_partition_pruning не отключен в qhb.conf. Если это так, запросы не будут оптимизированы должным образом.

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

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

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

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

DROP TABLE measurement_y2006m02;

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

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

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

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

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

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

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

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'
-- possibly some other data preparation work

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

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

Как объяснено выше, можно создавать индексы для партиционированных таблиц, и они автоматически применяются ко всей иерархии. Это очень удобно, так как не только существующие партиции будут проиндексированы, но и любые партиции, которые будут созданы в будущем. Одно ограничение заключается в том, что при создании такого партиционированного индекса невозможно использовать CONCURRENTLY. Чтобы преодолеть длительное время блокировки, можно использовать CREATE INDEX ON ONLY для партиционированной таблицы - такой индекс помечается как недействительный, и партиции не получают индекс автоматически. Индексы на партициях можно создавать отдельно с помощью CONCURRENTLY, а затем прикреплять к индексу на родительском 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;
...

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

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

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

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

  • Триггеры BEFORE ROW, если необходимо, должны быть определены на отдельных партициях, а не на партиционированной таблице.

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

Реализация с использованием наследования

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

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

  • Наследование таблиц допускает множественное наследование.

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

  • Некоторые операции требуют более сильной блокировки при использовании декларативного партиционирования, чем при использовании наследования таблиц. Например, добавление или удаление партиции в или из партиционированной таблицы требует установки блокировки ACCESS EXCLUSIVE на родительскую таблицу, тогда как в случае обычного наследования достаточно блокировки SHARE UPDATE EXCLUSIVE.

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

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

  1. Создайте «главную» таблицу, от которой будут наследоваться все «дочерние» таблицы. Эта таблица не будет содержать данных. Не определяйте никакие контрольные ограничения для этой таблицы, если только вы не собираетесь применять их одинаково ко всем дочерним таблицам. Также нет смысла определять какие-либо индексы или ограничения уникальности для неё. В нашем примере главная таблица — это таблица measurement, как она была определена изначально.

  2. Создайте несколько «дочерних» таблиц, каждая из которых наследуется от основной таблицы. Обычно эти таблицы не добавляют столбцы в набор, унаследованный от главной. Как и при декларативном разбиении, эти таблицы во всех отношениях являются обычными таблицами 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);
  1. Добавьте неперекрывающиеся ограничения таблиц в дочерние таблицы, чтобы определить допустимые значения ключей в каждой.
    Типичные примеры:
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);
  1. Для каждой дочерней таблицы создайте индекс по ключевым столбцам, а также любые другие индексы, которые вам могут понадобиться.
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);
  1. Для того, чтобы приложение могло сказать 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 для вставки данных, вам нужно будет копировать в правильную дочернюю таблицу, а не прямо в мастер. COPY запускает триггеры, поэтому вы можете использовать их как обычно, если вы используете триггерный подход.

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

  1. Убедитесь, что параметр конфигурации constraint_exclusion не отключен в QHB.conf в противном случае к дочерним таблицам можно получить доступ без необходимости.

Как мы видим, сложная иерархия таблиц может потребовать значительного количества 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'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

Предостережения

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

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

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

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

  • Если вы используете команды VACUUM или ANALYZE вручную, не забывайте, что вам нужно запускать их для каждой дочерней таблицы отдельно. Например, следующая команда будет обрабатывать только основную таблицу:

    ANALYZE measurement;

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

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

Сокращение партиций

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

SET enable_partition_pruning = on;                 -- the default
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.

В настоящее время обрезка партиций во время выполнения выполняется только для типов узлов Append и MergeAppend. Он еще не реализован для типа узлов ModifyTable, но, вероятно, будет изменен в будущем выпуске QHB.

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

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

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

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

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

Следующие предостережения относятся к исключению ограничений:

  • Исключение ограничений применяется только при планировании запросов, в отличие от сокращения партиций, которое также может применяться при выполнении запросов.

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

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

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

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

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

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

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

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

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

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

Внешние данные

QHB реализует части спецификации SQL/MED, позволяя вам получать доступ к данным, находящимся вне QHB, используя обычные SQL-запросы. Такие данные называются «внешние данные». (Обратите внимание, что это использование не следует путать с внешними ключами, которые являются типом ограничения в базе данных).

Доступ к внешним данным осуществляется с помощью обёртки сторонних данных (foreign data wrapper). Обёртка сторонних данных — это библиотека, которая может взаимодействовать с внешним источником данных, скрывая детали подключения к источнику данных и получения данных из него. В качестве модулей contrib доступны некоторые сторонние обертки данных. Другие виды сторонних упаковщиков данных могут быть найдены как сторонние продукты. Если ни одна из существующих сторонних обёрток данных не соответствует вашим потребностям, вы можете написать свою собственную см. главу Написание обёртки сторонних данных.

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

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

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

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

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

  • Представления (views);

  • Функции, процедуры и операторы (functions, procedures, and operators);

  • Типы данных и домены (data types and domains);

  • Триггеры и правила перезаписи (triggers and rewrite rules).

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

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

Чтобы гарантировать целостность всей структуры базы данных, 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.

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

DROP TABLE products CASCADE;

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

Почти все команды DROP в QHB поддерживают указание 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, и поэтому не будет удалять функцию, если таблица будет удалена. Хотя у этого подхода есть недостатки, есть и преимущества. Функция все еще допустима в некотором смысле, если таблица отсутствует, хотя ее выполнение может вызвать ошибку - создание новой таблицы с тем же именем позволит функции работать снова.