Когда CHECK ограничение является корректным?
Документация предупреждает, что при использовании CHECK ограничений необходимо соблюдать определенные правила:В этом есть смысл. Иначе PostgreSQL пришлось бы проверять условие всякий раз, когда в базе данных что-либо изменяется (или по прошествии времени), что нежелательно. Однако PostgreSQL не требует, чтобы выражение ограничения было IMMUTABLE.
Почему IMMUTABLE не применяется в CHECK выражениях ограничений?
Обычно PostgreSQL не оставляет подобные требования на усмотрение пользователя. Например, попытка использовать функцию, которой нет IMMUTABLE в определении индекса, вызывает ошибку:ERROR: functions in index expression must be marked IMMUTABLE
Так почему же PostgreSQL не применяет это для CHECK ограничений? Причина в том, что существуют допустимые сценарии использования для CHECK ограничений, когда выражение не является IMMUTABLE в буквальном смысле (но ограничение является таковым). Этого требует даже стандарт SQL. Процитируем ISO/IEC 9075-2:2003, глава 11, параграф 9:
Ранее в стандарте объяснялось:
Я избавлю вас от «точного» определения понятия «ретроспективно детерминированный». Этого достаточно, чтобы вызвать слезы на глазах, и это яркий пример того, как попытка формализовать идею может пойти не так. Из него можно сделать вывод, что комитет по стандартизации имел в виду нечто подобное:
CHECK (colname < current_timestamp - INTERVAL '1' DAY)
Это не IMMUTABLE выражение (current_timestamp завтра может иметь другое значение), но, безусловно, разумное CHECK ограничение. Любая строка, удовлетворяющая этому условию сейчас, будет удовлетворять ему и в будущем.
Нарушение допустимых UPDATE с плохим CHECK ограничением
Плохое ограничение для проверки формата строки
Представьте, что у нас есть таблицаCREATE TABLE data (
id bigint PRIMARY KEY,
lirum text NOT NULL
);
и у нас есть таблица для запоминания удаленных строк
CREATE TABLE history (
id bigint NOT NULL,
archived_at timestamp with time zone NOT NULL,
row text NOT NULL,
view_count integer DEFAULT 0 NOT NULL
);
которая заполняется триггером
CREATE FUNCTION archive() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
INSERT INTO history
(id, archived_at, row)
VALUES (OLD.id, current_timestamp, OLD::text);
RETURN NULL;
END;$$;
CREATE TRIGGER archive AFTER DELETE ON data
FOR EACH ROW EXECUTE FUNCTION archive();
Мы хотим создать CHECK ограничение, обеспечивающее правильный формат строки. Поэтому мы определяем
/* не обращайте внимания на IS NOT NULL, приведение типа является проверкой */
ALTER TABLE history ADD CONSTRAINT row_correct
CHECK (row::data IS NOT NULL);
Демонстрация проблемы
Давайте добавим и удалим некоторые данные:INSERT INTO data (id, lirum)
VALUES (1, 'some data');
DELETE FROM data WHERE id = 1;
Конечно, теперь есть строка history:
TABLE history;
id │ archived_at │ row │ view_count
════╪═══════════════════════════════╪═════════════════╪════════════
1 │ 2023-01-26 07:26:07.460679+01 │ (1,"some data") │ 0
(1 row)
Позже нам нужно будет создать новый столбец для нашей таблицы:
ALTER TABLE data ADD larum text NOT NULL;
В следующий раз, когда мы посмотрим на таблицу history и захотим увеличить view_count, мы получим:
UPDATE history
SET view_count = view_count + 1
WHERE id = 1
RETURNING id, archived_at, row;
ERROR: malformed record literal: "(1,"some data")"
DETAIL: Too few columns.
Что пошло не так, и что мы должны были сделать вместо этого?
Что произошло?Добавив столбец в таблицу data, мы также изменили одноименный составной тип данных. Таким образом, мы нарушили «ретроспективный детерминизм», потому что данные больше не соответствуют измененному типу данных. Несмотря на то, что мы вообще не изменили проверяемое значение, новая строка больше не удовлетворяет CHECK ограничению.
Лучшим решением для этого требования был бы триггер. Вы можете настроить триггер так, чтобы он проверял данные только один раз, при вставке строки. Также можно настроить триггер таким образом, чтобы он проверял данные при каждом обновлении строки, но только если row изменен.
Нарушение восстановления при неправильном CHECK ограничении
Неправильное ограничение, проверяющее зависимости между таблицами
Для примера рассмотрим систему управления арендой автомобилей:CREATE TABLE vehicle (
id bigint PRIMARY KEY,
model text NOT NULL,
seats smallint NOT NULL
);
CREATE TABLE client (
id bigint PRIMARY KEY,
name text NOT NULL,
group_size smallint NOT NULL
);
/* for the exclusion constraint */
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE rented (
vehicle_id bigint REFERENCES vehicle NOT NULL,
client_id bigint REFERENCES client NOT NULL,
from_to daterange NOT NULL,
PRIMARY KEY (vehicle_id, client_id),
EXCLUDE USING gist (from_to WITH &&, vehicle_id WITH =)
);
Сейчас не очень хорошо сдавать в аренду маленькую машину большой группе, и мы знаем, что хорошей идеей является применение ограничений в базе данных. Поэтому мы хотим иметь ограничение, которое не позволит нам назначить автомобиль клиенту, если размер группы превышает количество мест в автомобиле:
ALTER TABLE rented ADD CHECK (
(SELECT vehicle.seats FROM vehicle
WHERE vehicle.id = rented.vehicle_id) >=
(SELECT client.group_size FROM client
WHERE client.id = rented.client_id)
);
ERROR: cannot use subquery in check constraint
Какое неприятное ограничение!
Оно указывает, что PostgreSQL действительно накладывает некоторые ограничения на то, что нам разрешено делать в CHECK ограничении.
К счастью, мы можем обойти это с помощью функций:
CREATE FUNCTION get_seats(bigint) RETURNS smallint
BEGIN ATOMIC
SELECT seats FROM vehicle WHERE id = $1;
END;
CREATE FUNCTION get_group_size(bigint) RETURNS smallint
BEGIN ATOMIC
SELECT group_size FROM client WHERE id = $1;
END;
ALTER TABLE rented ADD CHECK
(coalesce(get_seats(vehicle_id), 0) >=
coalesce(get_group_size(client_id), 0));
Отлично! В любом случае это гораздо более читабельно!
Проверка ограничения
Давайте проверим, что это работает так, как задумано:INSERT INTO vehicle (id, model, seats) VALUES
(1, 'Mercedes Vito', 9),
(2, 'Audi TT', 4);
INSERT INTO client (id, name, group_size) VALUES
(100, 'Albe', 5),
(101, 'McDuck', 1);
INSERT INTO rented (vehicle_id, client_id, from_to) VALUES
(2, 100, '[2022-07-01,2022-07-14]');
ERROR: new row for relation "rented" violates check constraint "rented_check"
DETAIL: Failing row contains (2, 100, [2022-07-01,2022-07-15)).
INSERT INTO rented (vehicle_id, client_id, from_to) VALUES
(1, 100, '[2022-07-01,2022-07-14]');
Выглядит неплохо!
Неработающая резервная копия
Мы выполняем резервное копирование базы данных с помощьюpg_dump -F c -f dumpfile cars
В черный день потери данных, когда мы попытаемся восстановить нашу резервную копию, мы получим неожиданное сообщение об ошибке:
pg_restore -d cars dumpfile
pg_restore: error: COPY failed for table "rented": ERROR: new row for relation "rented" violates check constraint "rented_check"
DETAIL: Failing row contains (1, 100, [2022-07-01,2022-07-15)).
CONTEXT: COPY rented, line 1: "1 100 [2022-07-01,2022-07-15)"
pg_restore: warning: errors ignored on restore: 1
Что пошло не так, и что мы должны были сделать вместо этого?
PostgreSQL выводит таблицы в алфавитном порядке, чтобы быть детерминированным. Хотя большинство ограничений выбрасывается в конце, чтобы ускорить загрузку данных и предотвратить проблемы с целостностью, ограничения CHECK считаются частью определения таблицы и выбрасываются в начале. Это нормально, поскольку выражения в CHECK ограничениях должны быть ретроспективно детерминированными и зависеть только от самой строки.К сожалению, наше симпатичное ограничение не удовлетворяет этому требованию. PostgreSQL создает таблицы, затем восстанавливает данные для таблиц client, rented и vehicle именно в таком порядке. Поэтому, когда данные для таблицы rented загружаются, таблица vehicle все еще пуста и get_seats возвращает 0, и ограничение не выполняется.
Опять же, правильным решением был бы триггер, который выбрасывает ошибку при нарушении условия. В то время как ограничения должны выполняться постоянно, триггеры срабатывают только при определенных событиях. Дамп содержит определения триггеров в конце, и при восстановлении дампа триггеры не срабатывают вообще.
Что бы быть уверенным, что условие всегда выполняется, можно пойти другим путем: мы можем добавить копию seats и group_size в rented и включить эти столбцы в ограничения FOREIGN KEY, чтобы убедиться, что они всегда идентичны.
Тогда CHECK ограничению придется сравнивать только столбцы rented, и проблема исчезнет.
Заключение
Мы видели два примера того, как плохо определенное CHECK ограничение может разрушить базу данных. Особенно неприятным был сбой восстановления. К сожалению, это не академические случаи, и в списках рассылки были сообщения о сбоях восстановления. Поэтому убедитесь, что ваши CHECK ограничения определены правильно, даже если PostgreSQL не может этого обеспечить.Кроме того, мы выучили претенциозную фразу «ретроспективно детерминированный», которая может вызывать восхищение или покачивание головой, в зависимости от того, насколько доверчива аудитория.
Разрушение PostgreSQL БД некорректными ограничениями целостности типа CHECK
© Лоренц Альбе, 2023 Я в ударе! После того, как я написал о разрушении вашей базы данных и обертывании идентификатора транзакции , здесь я пишу о повреждениях, вызванных CHECK...
habr.com