Целостность данных легко нарушить. Бывает так, что в поле price попадает значение 0 из-за ошибки в коде приложения (периодически всплывают новости, как в том или ином инет-магазине продавали товары по 0 долларов). Или бывает, что удалили юзера из таблицы, но какие-то данные о нем остались в других таблицах, и эти данные вылезли в каком-то интерфейсе.PostgreSQL, как и любая другая СУБД, умеет делать некоторые проверки при вставке/изменении данных, и этим обязательно нужно уметь пользоваться. Давайте посмотрим, что мы можем проверять:
1. Кастомный подтип через ключевое слово DOMAIN
В PostgreSQL вы можете создать свой тип, основанный на каком-нибудь int или text с дополнительной проверкой каких-то вещей:
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
Мы создаем тип us_postal_code, в котором регулярками проверяем различные варианты его написания. Теперь никто не сможет туда по ошибке написать “улица Бармалеева”, там будет только индекс:
CREATE TABLE users (
id integer,
name text,
email text,
postal_code us_postal_code
) ;
Кроме того, это улучшает читабельность кода, так как тип сам поясняет, что в нем лежит, в отличие от безликих integer или text.
2. Check (особенно актуально для проверки jsonb и hstore)
Выше мы использовали us_postal_code использовали оператор CHECK. Точно такой же можно написать и в конструкции CREATE TABLE.
CREATE TABLE users (
id integer,
name text,
email text,
postal_code us_postal_code,
CHECK (length(name) >= 1 AND length(name) <= 300)
) ;
Или в таблице с товарами можно поставить check (price > 0), тогда вы не будете продавать ноуты по 0 рублей. Или можно написать хранимку и использовать check(superCheckFunction(price)), а в этой хранимке кучу логики проверять.
Кстати, тип varchar(100) — это тоже самое, что и тип text с дополнительным check по длине.
Надо понимать, что check происходит при каждом insert или update, поэтому, если в вашу таблицу идет 100500 записей в секунду, то check возможно делать не стоит.
Бывает важно обвешать чеками универсальные типы данных, такие как jsonb или hstore, потому что туда можно напихать что угодно. Можно проверять существование каких-то ключей в json или что их значение соответствует тому, что там должно быть.
3. Проверка на уникальность, как простая, так и частичная.
Простая проверка, что email у разных пользователей должен быть разный:
CREATE TABLE users (
id integer,
name text,
email text,
postal_code us_postal_code,
deleted boolean,
UNIQUE(email)
) ;
Однако иногда нужно проверять уникальность не по всей таблице, а только, например, у пользователей с определенным статусом.
Вместо простого UNIQUE вы можете добавить такой уникальный индекс:
CREATE UNIQUE INDEX users_unique_idx ON users(email) WHERE deleted = false;
Тогда уникальность email будет проверяться только у неудаленных юзеров. В where можно вставлять любые условия.
Надо еще отметить, что можно делать уникальные индексы по двум и более полям сразу, т.е. проверять уникальную комбинацию.
4. EXCLUDE
С помощью оператора EXCLUDE можно сделать еще один вид уникальности. Дело в том, что в посгресе множество типов данных, как встроенных, так и добавляемых через расширения. Например, есть тип данных ip4r, с его помощью можно хранить диапазон ip-адресов в одном поле.
И, допустим, надо в таблице хранить непересекающиеся диапазоны. Вообще, проверить, пересекаются ли два диапазона можно с помощью оператора &&, например SELECT ‘127.0.0.0/24’ && ‘127.0.0.1/32’ вернет true.
В итоге делаем просто:
CREATE TABLE ip_ranges (
ip_range ip4r,
EXCLUDE USING gist (ip_range WITH &&)
);
И тогда при вставке/апдейте postgres будет смотреть каждую строку, не пересекается ли она со вставляемой (т.е. не возвращает ли использование оператора && истину). Благодаря gist индексу эта проверка очень быстрая.
5. NOT NULL
Тут всё понятно, колонка не может принимать значение NULL. Зачастую (но необязательно) идет в связке с DEFAULT.
Например:
CREATE TABLE users (
id integer,
name text NOT NULL,
email text NOT NULL,
postal_code us_postal_code,
is_married BOOLEAN NOT NULL DEFAULT true,
UNIQUE(email)
) ;
При добавлении новой колонки с not null в существующую таблицу надо быть осторожным. Дело в том, что обычную колонку, где допустимо null, PostgreSQL добавляет мгновенно, даже если таблица очень большая, к примеру, десятки миллионов строк. Потому что ему не надо физически менять данные, лежащие на диске, null в postgres не занимают места. Однако если вы добавите колонку name text not null default ‘Вася’, то посгрес по факту полезет делать update каждой строки, и это может занять много времени, что может быть недопустимо в некоторых ситуациях.
Поэтому часто в огромные таблицы такие колонки добавляются в два этапа, т.е. сначала пачками заполняют данные новой колонки, и только потом ставят ей not null.
6. Primary key, т.е. первичный ключ
Раз это первичный ключ, то оно должен быть уникальным и не может быть пустым. В общем, в PostgreSQL PRIMARY KEY работает как комбинация UNIQUE и NOT NULL.
В других базах данных PRIMARY KEY делает еще и другие вещи, к примеру, если не ошибаюсь, в MySQL (Innodb), данные еще и автоматически кластеризуются вокруг PK для ускорения доступа по этому полю. (В посгресе, кстати, тоже так можно сделать, но вручную, командой CLUSTER. Но обычно в этом нет необходимости)
7. FOREIGN KEY
Например, у вас есть таблица
CREATE TABLE items (
id bigint PRIMARY KEY,
name varhar(1000),
status_id int
);
и таблица со статусами
CREATE TABLE status_dictionary (
id int PRIMARY KEY,
status_name varchar(100)
);
Вы можете указать базе, что колонка status_id соответствует Id из таблице status_dictionary. Например, так:
CREATE TABLE items (
id bigint PRIMARY KEY,
name varhar(1000),
status_id int REFERENCES status_dictionary(id)
);
Теперь вы сможете в status_id записать только null или Id из таблицы status_dictionaries, и больше ничего.
Также можно это делать по двум полям:
FOREIGN KEY (a,b) REFERENCES other_table(x,y);
При вставке опять же есть некоторый оверхед, потому что при каждой вставке СУБД вынуждена лочить довольно много вещей. Поэтому при (очень) интенсивной вставке возможно не стоит злоупотреблять использовнием Foreign key
8. DEFERRABLE
Если для производительности надо отложить проверку констрейнтов, констрейнты можно пометить ключевым словом DEFERRABLE.
Они бывают разных видов, например если вы сделаете UNIQUE(email) DEFERRABLE INITIALLY DEFERRED, то внутри транзакции можно написать
SET CONSTRAINTS ALL DEFERRED
И тогда все проверки будут Отложены и по факту произойдут только перед словом commit
Это сработает для UNIQUE, PRIMARY KEY и REFERENCES, но не сработает для NOT NULL и CHECK.
Комментарии (14)
varanio
04.10.2016 00:02+7Я согласен, что слово «констрейнт» звучит диковато, но оно очень часто фигурирует в разговорах разработчиков. Наверно потому, что слово «ограничение» — слишком общее. Например: «Ограничения postgresql». Приходится уточнять, что имеются в виду constraints, а не то, что база какая-то ограниченная, убогая. Так что я даже не знаю. Давайте считать, что статья написана в разговорном жанре :)
Хотя ваши аргументы мне совершенно понятны
vlivyur
04.10.2016 11:13В посгресе, кстати, тоже так можно сделать, но вручную, командой CLUSTER. Но обычно в этом нет необходимости
Можно пояснить этот момент — в кластерном ключе нет необходимости?varanio
04.10.2016 11:36Врать не буду, не знаю точно.
Но мы работаем в тандеме с несколькими профессиональными DBA на высоконагруженных базах. Ни разу они еще не советовали кластерные индексы. Видимо они нужны в каких-то особых случаях, или с ними возни больше, чем пользы.varanio
06.10.2016 10:37Говорят, что в основном это нужно на таблицах, которые редко меняются.
Например, можно использовать для партиционированных таблиц, либо таблиц с геоданными
vadv
06.10.2016 11:17+1Нарисую пример при котором важен кластерный индекс: допустим у вас таблица Users, в нем есть поле LastLoginDate. Пользователей у которых LastLoginDate меньше месяца назад — всего 10%, поэтому они разбросаны по таблице. но ДБ не загружает конкретные строки, она загружает данные блоками, тоесть вам понадобилась одна строчка из блока — а загрузиться весь блок, и в нем 90% не нужных вам пользователей. При помощи кластера вы можете отстортировать таких пользователей по LastLoginDate и тогда ДБ будет работать эффективнее.
vlivyur
06.10.2016 15:20Пример хорош, но только как сильно искусственный: кластерный индекс по полю, которое постоянно меняется — к потере производительности.
vadv
06.10.2016 15:27+2В реальной жизни — да, из-за особенности версионника. Но вообще есть костылики в виде pg_repack и регламентное временя. Второй пример — это поиск по временному диапазону в append-only таблице.
ivan_petroff
04.10.2016 11:15-1Антон, статья неплохая и судя по теме предназначена для начинающих программистов. Однако русский язык, используемый в описании примеров, только путает.
Английское слово «constraint» имеет аналог в русском языке «ограничитель» и уместно в заголовке написать «Ограничители(constraints) PostgreSQL», а затем после двоеточия их перечислить и поставить точку.
Кастомные логично поменять на «свои подтипы», тем более, что «свои» используете в объяснении.
2. Check (особенно актуально для проверки jsonb и hstore)
«Проверка оператором Сheck(актуально для jsonb и hstore)» звучит лучше.
Бывает важно обвешать чеками универсальные типы данных,…
«Не забывайте проверять универсальные типы данных, как jsonb и hstore,… » понятнее.
При вставке опять же есть некоторый оверхед, потому что при каждой вставке СУБД вынуждена лочить довольно много вещей.
«При каждой вставке СУБД вынуждена делать много избыточных операций» — проще и яснее.
Или в таблице с товарами можно поставить check (price > 0), тогда вы не будете продавать ноуты по 0 рублей. логично заменить на
«Или в таблице с товарами поставьте check (price > 0), чтобы не продать дорогой товар по 0 рублей.»
В статье нет смысла постоянно повторять название СУБД по-английски, а затем в русской фонетической транскрипции. Ясно по названию статьи о какой СУБД идет речь.
Успехов.varanio
04.10.2016 11:21Спасибо. Я подумаю над этим (правда, чуть позже, сейчас нет времени совсем). Меня самого немного раздражает смесь русских и английских слов. Я бы использовал только русский язык, но боюсь, что с нетоторой вероятностью меня поймут хуже. Взять то же слово «ограничитель». Оно действиетльно отражает суть. Но спросите какого-нибудь разработчика: «ты тут поставил ограничитель»? Он будет дольше соображать, чем после вопроса «ты поставил constraint»?
Впрочем, постараюсь хотя бы немного улучшить ситуацию, если не в этой статье, то в следующей. По крайней мере, наверно не стоит писать constraint русскими буквами. Тогда это сойдет за оператор, к которому не придерешься
the_unbridled_goose
04.10.2016 12:12+2О. это извечная проблема. Особенно при переводе статей. Назовешь по-русски — теряется смысл, по-английски — выбивается из текста. Получается замкнутый круг из мучений и компромиссов с самим собой.
ivan_petroff
04.10.2016 12:27+1Взять то же слово «ограничитель». Оно действиетльно отражает суть. Но спросите какого-нибудь разработчика: «ты тут поставил ограничитель»? Он будет дольше соображать, чем после вопроса «ты поставил constraint»?
Вы правильно делаете, написав термин на русском и дав в круглых скобках его название на английском языке. После этого вправе употреблять термин на русском без ограничений в статье. Это хороший стиль.
Постоянно используете в деятельности профессиональный сленг и переход на простой язык закономерно вызывает торможение у специалиста. Однако профессионал всегда должен уметь объяснить простым и доступным языком сложные вещи.
Для меня был примером преподаватель по теоретической механике, который приводил аналогии, что вбивал основы на всю жизнь простыми примерами. Например, даже иностранцы запоминали надолго, что такое момент кручения, когда он сказал: «Была бы пара — момент найдется» :-)
Успехов.
darthunix
04.10.2016 15:41Посмотрел на теги внизу и нашёл родственную душу — я всегда делаю такую же ошибку в написании слова deferrable ;) и только написав вспоминаю, что где-то был капкан.
McAaron
А зачем Вы в качестве перевода слова «constraint» используете новояз «констрейнт», если всегда со времен IBM 360/370 оно переводилось как «ограничение»?