Ограничения CHECK в PostgreSQL: правила уровня строки и почему межстрочные сравнения ломают дампы
Ограничение CHECK в PostgreSQL может ссылаться только на вставляемую или обновляемую строку. CHECK, сравнивающий данные с другими строками, может пройти простые тесты, но дать сбой при восстановлении из pg_dump, поскольку строки загружаются в порядке, который может не удовлетворять условию.
В этом материале
Основная мысль
В PostgreSQL ограничение CHECK это логическое выражение, вычисляемое только для новой или обновлённой строки. Оно может ссылаться на столбцы этой строки, константы, неизменяемые функции и операторы, но не должно ссылаться на другие строки или другие таблицы. PostgreSQL не проверяет это ограничение на этапе определения, поэтому межстрочный CHECK можно создать, и в небольших тестах он может казаться работающим. Однако он не может гарантировать инвариант, поскольку последующие изменения в упоминаемой строке могут нарушить условие без повторной проверки исходной строки. Документированное следствие состоит в том, что дамп и восстановление базы данных могут завершиться ошибкой: строки перезагружаются в порядке, который может не удовлетворять ограничению, даже если итоговое состояние базы данных согласовано. Для межстрочных или межтабличных правил используйте ограничения UNIQUE, EXCLUDE или FOREIGN KEY, либо реализуйте правило в логике приложения или триггере. Помните также, что CHECK проходит, когда выражение вычисляется в NULL, поэтому сочетайте его с NOT NULL, когда значения NULL должны быть исключены.
Допустимые ограничения CHECK
Ограничение CHECK в PostgreSQL это наиболее общий тип ограничения. Оно привязывает логическое выражение к столбцу или к таблице, и выражение вычисляется всякий раз при вставке или обновлении строки. Выражение должно затрагивать ограничиваемый столбец, иначе оно почти бесполезно. Ограничения столбца и ограничения таблицы во многих случаях взаимозаменяемы, и ограничение таблицы может ссылаться на несколько столбцов одной и той же строки.
Выражение может использовать столбцы проверяемой строки, литеральные константы, операторы и функции. Оно не должно ссылаться на данные таблицы, отличные от новой или обновлённой строки. Это документированное ограничение, а не стилистическое предпочтение. Ограничение проверяется для строки-кандидата изолированно, поэтому во время вычисления у него нет доступа к другим строкам.
Тонкость, удивляющая многих практиков, это обработка NULL. Ограничение CHECK считается выполненным, когда выражение вычисляется в true или в значение NULL. Поскольку большинство выражений дают NULL, если любой операнд равен NULL, CHECK сам по себе не предотвращает значения NULL в ограничиваемых столбцах. Чтобы запретить NULL, добавьте ограничение NOT NULL, которое функционально эквивалентно CHECK (column IS NOT NULL), но в PostgreSQL более эффективно.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (price > discounted_price)
);Риск межстрочных ссылок
PostgreSQL не поддерживает ограничения CHECK, которые ссылаются на данные таблицы, отличные от проверяемой новой или обновлённой строки. Документация прямо указывает, что CHECK, нарушающий это правило, может казаться работающим в простых тестах, но не может гарантировать, что база данных не достигнет состояния, в котором условие ограничения ложно. Причина в том, что условие зависит от других строк, а эти строки могут измениться после того, как исходная строка была проверена. Ничто не перепроверяет исходную строку при изменении упоминаемой строки.
Это проблема корректности, а не только производительности. Ограничение, которое выполняется лишь в момент вставки, создаёт ложное ощущение целостности. База данных может дрейфовать в состояние, где инвариант нарушен, и в момент дрейфа ошибка не возникает. Сбой проявляется позже, часто в самый неподходящий момент.
Те же рассуждения применимы к функциям, используемым внутри CHECK. Функция, читающая другие таблицы, вносит ту же межстрочную зависимость, даже если синтаксис выглядит локальным. Ограничение касается того, что выражение может наблюдать, а не того, как оно написано.
Сбои дампа и восстановления
Документированное следствие межстрочного CHECK состоит в том, что дамп и восстановление базы данных могут завершиться ошибкой. Во время восстановления строки загружаются в порядке, определяемом дампом, и этот порядок может не удовлетворять ограничению на каждом промежуточном шаге. Восстановление может завершиться ошибкой, даже когда полное состояние базы данных согласовано с ограничением, потому что ограничение вычисляется построчно по мере вставки данных.
Это делает проблему операционно серьёзной. Резервная копия, которая восстанавливается без ошибок в среде разработки, может дать сбой в продакшене, если порядок строк отличается, если объём данных меняет порядок загрузки или если дамп был снят на другом этапе жизненного цикла данных. Сбой не детерминирован относительно итогового состояния; он зависит от пути, пройденного для достижения этого состояния.
Практический вывод таков: ограничение, которое нельзя вычислить по одной строке, нельзя полагать декларативной гарантией. Оно может проходить тесты и даже однажды пройти восстановление, но оно не обеспечивает свойство целостности, которое, как кажется, обеспечивает.
Рекомендуемые альтернативы
Когда правило действительно охватывает несколько строк или таблиц, PostgreSQL предлагает декларативные ограничения, предназначенные для этой цели. Используйте UNIQUE для уникальности по строкам, EXCLUDE для правил диапазонов и перекрытий и FOREIGN KEY для ссылочной целостности. Эти ограничения обеспечиваются базой данных для соответствующих строк и корректно поддерживаются при изменении данных.
Для правил, которые ни одно из них не выражает, используйте триггер или проверку на уровне приложения и чётко документируйте, что правило не является декларативным ограничением. Триггер может наблюдать другие строки и может быть написан так, чтобы повторно проверять при соответствующих изменениях, но он несёт собственную сложность и должен тщательно поддерживаться.
Полезная мысленная модель: спросите, можно ли решить правило по одной строке-кандидату. Если да, CHECK уместен и дёшев. Если нет, правило принадлежит типу ограничения, который понимает связь, или процедурному коду, который вы принимаете как точку обеспечения.
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);Условия применения
- Ссылается ли выражение CHECK только на столбцы вставляемой или обновляемой строки?
- Затрагивает ли правило другие строки или другие таблицы, что потребовало бы UNIQUE, EXCLUDE или FOREIGN KEY?
- Сопоставлены ли столбцы, допускающие NULL, с NOT NULL там, где значения NULL должны быть исключены?
- Проверяли ли вы дамп и восстановление таблицы, чтобы убедиться, что ограничение выдерживает перезагрузку?
- Названо ли ограничение так, чтобы его можно было идентифицировать и изменить позже?
Границы применения
Эта статья описывает поведение PostgreSQL в соответствии с документацией для поддерживаемых версий (14-18 на момент написания). Точные формулировки сообщений об ошибках и поведение дампа и восстановления могут различаться в зависимости от версии и используемых инструментов (pg_dump, pg_restore или логическая репликация). Примеры иллюстративны и предполагают установку по умолчанию без пользовательских триггеров ограничений. Статья не рассматривает подробно отложенные ограничения, операторы ограничений исключения или обеспечение через триггеры; они требуют отдельного рассмотрения. Она также не утверждает, что какое-либо конкретное восстановление завершится ошибкой, а лишь то, что межстрочный CHECK не может гарантировать целостность и может привести к сбою восстановления в зависимости от порядка загрузки строк.