По умолчанию, ограничения целостности в PostgreSQL проверяются немедленно, сразу после обновления каждой строки, что может быть неоднозначным при обновлении нескольких строк.
Рассмотрим пример с одной таблицей, имеющей один столбец с уникальным ограничением (и индексом):
CREATE TABLE numbers (number INT, UNIQUE (number));INSERT INTO numbers VALUES (1), (2);
Если понадобится обновить строки, увеличив значение на 1, возникнет проблема:
UPDATE numbers SET number = number + 1;ERROR: duplicate key value violates unique constraint "numbers_number_key"DETAIL: Key (number)=(2) already exists.
Немедленная или отложенная проверка
По умолчанию, ограничения создаются как NOT DEFERRABLE INITIALLY IMMEDIATE.
-
INITIALLY IMMEDIATE означает, что по умолчанию, проверка ограничения выполняется после обновления каждой строки.
-
NOT DEFERRABLE означает, что мы не можем изменить этот параметр в транзакции.
Список ограничений целостности:
SELECT connamespace::regnamespace schema, conrelid::regclass table, conname, contype type, condeferrable deferrable, condeferred deferred FROM pg_constraint WHERE contype IN ('p', 'u') AND connamespace::regnamespace::text != 'pg_catalog' AND conname='numbers_number_key' ORDER BY 1, 2, 3, 4;schema | table | conname | type | deferrable | deferred-------+---------+--------------------+------+------------+----------public | numbers | numbers_number_key | u | f | f(1 row)
ограничение действительно NOT DEFERRABLE (deferrable = false) и INITIALLY IMMEDIATE (deferrable = false).
Если ограничение DEFERRABLE, то проверка может откладываться до фиксации транзакции. Откладываемое ограничение может быть:
-
INITIALLY DEFERRED — по умолчанию проверяться при фиксации транзакции, свойство DEFERRABLE устанавливается автоматически
-
DEFERRABLE INITIALLY IMMEDIATE — по умолчанию проверяться немедленно, но может проверяться при фиксации транзакции при использовании одной из команд:
SET CONSTRAINTS numbers_number_key DEFERRED;SET CONSTRAINTS ALL DEFERRED;
Откладывать проверку могут ограничения целостности:
-
PRIMARY KEY
-
UNIQUE
-
REFERENCES (FOREIGN KEY)
-
EXCLUDE
На DEFERRABLE ограничения нельзя создать FOREIGN KEY:
CREATE TABLE authors (id INT UNIQUE DEFERRABLE);CREATE TABLE books (id INT PRIMARY KEY, author_id INT REFERENCES authors (id) DEFERRABLE INITIALLY DEFERRED);ERROR: cannot use a deferrable unique constraint for referenced table "authors"
В PostgreSQL не могут быть откладываемыми:
-
NOT NULL
-
CHECK
хотя по стандарту SQL они могут быть с отложенной проверкой.
Изменение ограничения
В PostgreSQL версии 9.4 (2014 год) добавлена возможность поменять откладываемость ограничения командой ALTER TABLE имя ALTER CONSTRAINT свойства;
Попробуем изменить существующее ограничение:
ALTER TABLE numbers ALTER CONSTRAINT numbers_number_key DEFERRABLE;ERROR: constraint "numbers_number_key" of relation "numbers" is not a foreign key constraint
В документации к команде ALTER TABLE написано: «В настоящее время только ограничения внешнего ключа могут быть изменены таким образом».
Это означает, что нам придётся полностью удалить ограничение и создать его заново. Это можно сделать одной командой:
ALTER TABLE numbers DROP CONSTRAINT numbers_number_key, ADD CONSTRAINT numbers_number_key UNIQUE (number) DEFERRABLE INITIALLY DEFERRED;
Если выполним запрос к pg_constraint, то увидим изменения в двух последних столбцах:
SELECT connamespace::regnamespace schema, conrelid::regclass table, conname, contype type, condeferrable deferrable, condeferred deferred FROM pg_constraint WHERE contype IN ('p', 'u') AND connamespace::regnamespace::text != 'pg_catalog' AND conname='numbers_number_key' ORDER BY 1, 2, 3, 4; schema | table | conname | type | deferrable | deferred --------+---------+--------------------+------+------------+---------- public | numbers | numbers_number_key | u | t | t
Повторим команду UPDATE, теперь она успешно выполнится:
UPDATE numbers SET number = number + 1;SELECT * FROM numbers; number-------- 2 3
Три варианта настройки ограничений целостности
В документации к команде ALTER TABLE видно, что допустимый синтаксис выглядит так:
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Это даёт три комбинации, которые можно указывать при создании ограничения:
-
NOT DEFERRABLE [INITIALLY IMMEDIATE](по умолчанию)
Ограничение проверяется немедленно, и этот параметр нельзя изменить внутри транзакции. Является значением по умолчанию, первичных и уникальных ключей.
-
DEFERRABLE [INITIALLY IMMEDIATE]
CREATE TABLE numbers (number INT, UNIQUE (number) DEFERRABLE INITIALLY IMMEDIATE);
Ограничения проверяются немедленно, но этот параметр может меняться внутри транзакции. Если не хочется, чтобы на таблицу ссылались внешние ключи, можно использовать это значение.
-
[DEFERRABLE] INITIALLY DEFERRED
CREATE TABLE numbers (number INT, UNIQUE (number) DEFERRABLE INITIALLY DEFERRED);
Проверка ограничений выполняется во время фиксации транзакции, но это можно поменять внутри транзакции и ограничение сможет проверяться немедленно.
Примеры использования
Есть несколько причин, по которым могут понадобиться отложенные ограничения.
Одна из причин наличия уникального ограничения для одного числового столбца в том, чтобы значения столбца хранили порядок для сортировки. Представьте себе приложение для составления списков задач, где вы можете перетаскивать задачи, расставляя их по сроку выполнения или важности.
1. Столбцы «Позиция / Вес / Приоритет»
Изменение порядка задач возможно реализовать отложенными ограничениями:
CREATE TABLE todo_items (id SERIAL PRIMARY KEY, task TEXT NOT NULL, priority INTEGER NOT NULL, UNIQUE (priority) DEFERRABLE INITIALLY DEFERRED);-- Вставка двух задачINSERT INTO todo_items (task, priority) VALUES ('Clean the bathroom', 1) , ('Go grocery shopping', 2);-- Поменять две задачи местамиBEGIN;UPDATE todo_items SET priority = 2 WHERE task = 'Clean the bathroom';UPDATE todo_items SET priority = 1 WHERE task = 'Go grocery shopping';COMMIT;
2. Циклическая зависимость между таблицами
Проектировать такие схемы не стоит, но схема хранения может быть унаследованной. Пример вставки строк, которые можно выполнить только в одной транзакции и только, если внешний ключ с отложенной проверкой:
CREATE TABLE manufacturers (name TEXT PRIMARY KEY, flagship_phone_name VARCHAR NOT NULL);CREATE TABLE phones (name TEXT PRIMARY KEY, manufacturer_name TEXT NOT NULL REFERENCES manufacturers (name) DEFERRABLE INITIALLY DEFERRED);-- Добавить foreign key, замыкающую кругALTER TABLE manufacturers ADD CONSTRAINT manufacturers_latest_phone_id_fkey FOREIGN KEY (flagship_phone_name) REFERENCES phones (name) DEFERRABLE INITIALLY DEFERRED;-- Вставить несколько связанных по кругу строкBEGIN;INSERT INTO manufacturers (name, flagship_phone_name)VALUES ('Google', 'Pixel 6 Pro') , ('Apple', 'iPhone 13 Pro Max') , ('Samsung', 'Galaxy S22 Ultra');INSERT INTO phones (manufacturer_name, name)VALUES ('Google', 'Pixel 6') , ('Google', 'Pixel 6 Pro') , ('Apple', 'iPhone 13 Pro') , ('Apple', 'iPhone 13 Pro Max') , ('Samsung', 'Galaxy S22') , ('Samsung', 'Galaxy S22 Ultra');COMMIT;
3. Загрузка данных или восстановление дампа
Если у вас есть файл данных с командами вставки, которые расположены не в порядке, удовлетворяющим ограничениям, возможно, имеет смысл отложить выполнение всех ограничений до конца транзакции.
В этом случае, может быть, достаточно использовать DEFERRABLE INITIALLY IMMEDIATE ограничения, но они не имеют преимуществ по скорости вставки данных, по сравнению с DEFERRABLE INITIALLY DEFERRED.
Вот пример, который работает только потому, что ограничение внешнего ключа отложено:
CREATE TABLE authors (id SERIAL PRIMARY KEY, name VARCHAR NOT NULL);CREATE TABLE books (id SERIAL PRIMARY KEY, author_id INTEGER NOT NULL REFERENCES authors (id) DEFERRABLE INITIALLY DEFERRED, title VARCHAR NOT NULL);-- Вставить строки из файла,временно игнорируя связи между таблицамиBEGIN;INSERT INTO booksVALUES (1, 1, 'All Summer in a Day') , (2, 1, 'The Martian Chronicles') , (3, 2, 'Starship Troopers') , (4, 2, 'Podkayne of Mars');INSERT INTO authorsVALUES (1, 'Ray Bradbury') , (2, 'Robert A. Heinlein');COMMIT;
4. Удаление строк в произвольном порядке
Вместо потенциально опасного подхода REFERENCES ... ON DELETE CASCADE можно использовать отложенные ограничения, позволяющие удалять строки из таблиц в произвольном порядке. Это может быть полезно при удалении тестовых данных — заглушек, созданных для тестирования интеграций.
CREATE TABLE countries (iso2 CHAR(2) PRIMARY KEY, name VARCHAR NOT NULL);CREATE TABLE cities (id SERIAL PRIMARY KEY, country_iso2 CHAR(2) NOT NULL REFERENCES countries (iso2) DEFERRABLE INITIALLY DEFERRED, name VARCHAR NOT NULL);-- Вставка тестовых строкINSERT INTO countries (iso2, name)VALUES ('IS', 'Iceland'), ('NZ', 'New Zealand');INSERT INTO cities (country_iso2, name)VALUES ('IS', 'Reykjavík') , ('IS', 'Akureyri') , ('NZ', 'Christchurch') , ('NZ', 'Queenstown');-- Удаление строк в произвольном порядке, нарушающим внешний ключBEGIN;DELETE FROM countries WHERE iso2 = 'NZ';DELETE FROM cities WHERE country_iso2 = 'NZ';COMMIT;
Вопросы производительности
Отложенные ограничения кажутся отличной идеей, почему же они не используются по умолчанию? Или почему бы мне не создавать все свои ограничения как отложенные INITIALLY DEFERRED? Основная причина:
Уникальные индексы с откладываемой проверкой (pg_index.indimmediate=true) допускают наличие повторяющихся значений в один момент времени, что снижает возможности планировщика запросов по оптимизации запроса.
Планировщик запросов не может быть уверен, что уникальность соблюдается абсолютно в каждый момент времени в транзакции. Это может негативно сказаться при соединении по уникальным столбцам или при выполнении запроса с условием WHERE <unique_column> IN (...). Джо Нельсон дал более подробное объяснение в своей статье.
Создадим таблицу с данными и ограничением с немедленной проверкой:
DROP TABLE IF EXISTS numbers;CREATE TABLE numbers (number INT, UNIQUE (number));insert into numbers select * from generate_series(1, 1000000);vacuum analyze numbers;Выполним тестовый запрос:EXPLAIN (analyze, buffers, timing off) SELECT * FROM numbers WHERE number IN (SELECT number FROM numbers); QUERY PLAN------------------------------------------------ Seq Scan on numbers (cost=0.00..14425.00 rows=1000000 width=4) (actual rows=1000000 loops=1) Filter: (number IS NOT NULL) Buffers: shared hit=4425 Planning: Buffers: shared hit=51 Planning Time: 0.194 ms Execution Time: 209.181 ms
Планировщик может определить, что набор условий для таблицы обеспечивает уникальность результата. Если существует совместимый уникальный, не откладываемый индекс, планировщик может пропустить необходимый, в противном случае, шаг сортировки или проверки на уникальность или HashAggregate. Планировщик не может сделать это для откладываемых ограничений, потому что физически могут присутствовать повторяющиеся значения.
Поменяем ограничение на откладываемое и повторим тестовый запрос:
ALTER TABLE numbers DROP CONSTRAINT numbers_number_key, ADD CONSTRAINT numbers_number_key UNIQUE (number) DEFERRABLE INITIALLY IMMEDIATE;vacuum analyze numbers;EXPLAIN (analyze, buffers, timing off) SELECT * FROM numbers WHERE number IN (SELECT number FROM numbers); QUERY PLAN------------------------------------------------ Merge Semi Join (cost=0.90..66960.90 rows=1000000 width=4) (actual rows=1000000 loops=1) Merge Cond: (numbers.number = numbers_1.number) Buffers: shared hit=2741 read=2731 -> Index Only Scan using numbers_number_key on numbers (cost=0.42..25980.42 rows=1000000 width=4) (actual rows=1000000 loops=1) Heap Fetches: 0 Buffers: shared hit=5 read=2731 -> Index Only Scan using numbers_number_key on numbers numbers_1 (cost=0.42..25980.42 rows=1000000 width=4) (actual rows=1000000 loops=1) Heap Fetches: 0 Buffers: shared hit=2736 Planning: Buffers: shared hit=32 read=5 Planning Time: 0.289 ms Execution Time: 694.485 ms
Запрос стал выполняться в 3,3 раза дольше. В PostgreSQL 10 появилась оптимизация, которая преобразует полусоединение (Semi Join) в подзапросе IN в соединение, если гарантируется уникальность столбца в подзапросе. С откладываемым ограничением оптимизация не работает и в последнем плане использовался Merge Semi Join.
Если ограничение PRIMARY KEY или UNIQUE откладываемое, то автоматически создаётся триггер со свойствами ограничения (свойства в примере DEFERRABLE INITIALLY IMMEDIATE):
select tgrelid::regclass tab, tgconstrindid::regclass index, tgfoid::regproc as function_name, tgenabled e, tgisinternal i, pg_get_triggerdef(oid) from pg_trigger where tgrelid::regclass::text='numbers'; tab | index | function_name | e | i | pg_get_triggerdef--------+--------------------+--------------------+---+---+-----------------numbers | numbers_number_key | unique_key_recheck | O | t | CREATE CONSTRAINT TRIGGER "Unique_ConstraintTrigger_16885" AFTER INSERT OR UPDATE ON public.numbers DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION unique_key_recheck()(1 row)
Если ограничение не откладываемое, то триггер не создаётся, так как проверка уникальности проверяется немедленно.
Если отключить триггеры, то ограничение целостности не будет срабатывать и можно будет вставить строки, нарушающие ограничение::
set session_replication_role = replica;insert into numbers values (1);INSERT 0 1insert into numbers values (1);INSERT 0 1
ссылка на оригинал статьи https://habr.com/ru/articles/1055860/