Comments 17
Спасибо за отличную статью! Хотел прояснить одну вещь.
В этот момент может возникнуть вопрос – зачем вообще создавать ограничение, если индекс выполняет ровно то, что требуется – гарантирует уникальность значений?
Единственное отличие, которое удалось найти, состоит в том, что ограничения могут быть отложенными (deferrable), а индексы – нет.
Ответ очень простой: на ограничения уникальности можно ссылаться из внешних ключей. Просто индекса в этом случае недостаточно. Читаем документацию:
A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint).
Попутно у некоторых людей возникает вопрос: а в чём тогда отличие первичных ключей от уникальных? Ответ: первичный ключ не допускает значений NULL, и он лишь один на таблицу.
Ответ очень простой: на ограничения уникальности можно ссылаться из внешних ключей. Просто индекса в этом случае недостаточно. Читаем документацию:
…
Эксперименты показывают, что уникального индекса все же достаточно. В ссылке на SO выше есть пример для воспроизведения:
create table A(
first_a varchar,
second_a varchar,
third_a varchar,
primary key(first_a, second_a)
);
create unique index on A (second_a, third_a);
create table B(
first_b varchar,
second_b varchar,
foreign key (first_b, second_b) references A(second_a, third_a), --references the unique index
foreign key (first_b, second_b) references A(first_a, second_a) -- references the primary key
);
Причем, если не создавать индекс, то получим ошибку:
ERROR: there is no unique constraint matching given keys for referenced table «a»
В общем, похоже на неточность в документации.
Да, похоже вы правы, действительно, работает! Возможно, это послабление было сделано специально, по каким-то неизвестным нам причинам, и открывать тикет навряд ли стоит, потому что обратная совместимость важнее и всё такое. Я точно знаю по опыту работы с другими СУБД что этот constraint необходим, и иногда даже задаю этот вопрос на собеседованиях.
В теории реляционных баз данных для внешнего ключа обязателен потенциальный ключ в родительском отношении. Одно из свойств потенциального ключа — это его уникальность.
Согласитесь, что ключи и индексы — это совершенно разные вещи. Ключи обеспечивают целостность и непротиворечивость данных с точки зрения бизнес правил, в то время как индексы — это чисто техническая вещь для ускорения доступа к данным. Как говорится, мухи отдельно, котлеты отдельно. Ключ должен ссылаться на ключ, мы связываем ограничения целостности друг с другом.
В общем случае индекс не обязан присутствовать для поддержки уникального ограничения, но крайне желателен для больших таблиц во избежание full/seq-scan. Для маленьких табличек он точно не нужен, и в других СУБД индекс не создаётся автоматически при создании уникального ключа.
Так что с документацией всё нормально, она просто следует общеизвестным базовым правилам. А вот реализация немножко кривая.
Согласитесь, что ключи и индексы — это совершенно разные вещи. Ключи обеспечивают целостность и непротиворечивость данных с точки зрения бизнес правил, в то время как индексы — это чисто техническая вещь для ускорения доступа к данным.
Полностью согласен с вами и сам лично также предпочитаю выполнять этот последний «опциональный» пункт по созданию ограничения на основе индекса.
Более того, как я отметил в статье, раньше даже сама документация советовала использовать ограничение, а вот почему с 9.5 эту сноску удалили – интересный вопрос.
Можете подсказать в каких именно СУБД? Знаю точно, что Oracle и MySQL ведёт себя точно также как PostgreSQL в этом вопросе.
Важно заметить, что команда REINDEX, которая как раз предназначена для пересоздания индекса, до 12й версии работает только в блокирующем режиме, что не дает возможности ее использовать. В 12й версии PostgreSQL появилась поддержка CONCURRENTLY, и теперь и ей можно пользоваться.
Тут хотел бы отметить, что reindex'ов несколько и они немного различаются:
reindex index concurrently — будет. Но в случае очередной ошибки (например уникальности) каждый такой reindex index будет оставлять по дополнительному дублирующему invalid индексу.
reindex более крупных объектов (reindex table concurrently и т.д.) не будет перестраивать invalid индексы. Сделано специально, чтобы не плодить много invalid индексов.
К слову, reindex concurrently на уровне кода базы — это create index concurrently + swap + drop index concurrently
Создание ограничения NOT NULL
На версии postgresql 12 и новее после VALIDATE CONSTRAINT уже можно сделать
ALTER TABLE my_table ALTER COLUMN name SET NOT NULLИ затем можно удалить добавленный check constraint. Мой патч принят в pg12 и set not null теперь имеет возможность не читать данные, если по имеющимся check можно увидеть, что в поле null нет.
Что упростит и махинации с primary key.
PS: отдельно спасибо за выделение опасности взятия эксклюзивной блокировки. Довольно часто пишут, что «такой alter table — это быстрая операция», но без уточнения «быстрая только после взятия блокировки». Что на практике довольно регулярно приводит к сюрпризам для разработчиков.
Небольшой вопрос – в какую из минорных версий pg12 попал ваш патч (беглый поиск по release notes не помог мне)?
Allow ALTER TABLE… SET NOT NULL to avoid unnecessary table scans
до PG12 если есть права на записть в pg_attribute можно сделать немного чёрной магии:
UPDATE pg_catalog.pg_attribute SET attnotnull = TRUE
WHERE attrelid = 'table_name'::regclass::oid AND attname = 'col_name'ещё можно добавить:
- FK делает блокировку на две таблицы (особенно может вылазить при удалении столбца или таблицы)
- есть ещё
statement_timeoutв добавок кlock_timeout— на случай, когда, что-то упустили - EXCLUDE констрэйнт, который вроде не обойти
- есть другие операции как
SET SCHEMA,SET TABLESPACE,SET DATA TYPE
также интересно:
- как писать логику и делать миграции когда стэйтов приложения и базы много
- сколько стэйтов не больно поддерживать одновременно
- как ведут себя блокировки в репликах и шардах
- что делать если операция нужна, но серьёзно блокирующая
С EXCLUDE мне не особо приходилось работать, поэтому ничего не написал :-) Если есть, чем поделиться, то так же буду рад дополнить статью.
Насчет остального – спасибо за идеи, подумаю.
Отличная статья, спасибо!
А как в Postgres работает ограничение уникальности по столбцу партиционированной таблицы, если индексы создаются для каждой партиции отдельно?
Или есть вариант глобального индекса?
This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually.
SQL миграции в Postgres. Часть 1