В статье рассматриваются особенности использования ограничений целостности с проверкой, которую можно отложить до фиксации транзакции, а также использование системных триггеров для проверки ограничений целостности. Триггеры создаются для любых внешних ключей - и с немедленной и отложенной проверкой, а для уникальных ключей - только для ограничений с отложенной проверкой. Для уникальных откладываемых ключей создаются уникальные индексы, которые допускают неуникальные значения. В таблице могут находиться строки, нарушающие ограничение уникальности, при том, что статус ограничения целостности в системном каталоге "проверено" (validated).

По умолчанию, ограничения целостности в 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] (по умолчанию)

CREATE TABLE numbers (number INT, 
 UNIQUE (number));

Ограничение проверяется немедленно, и этот параметр нельзя изменить внутри транзакции. Является значением по умолчанию, первичных и уникальных ключей.

• 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);

Проверка ограничений выполняется во время фиксации транзакции, но это можно поменять внутри транзакции и ограничение сможет проверяться немедленно.

Есть несколько причин, по которым могут понадобиться отложенные ограничения. Одна из причин наличия уникального ограничения для одного числового столбца - в том, чтобы значения столбца хранили порядок для сортировки. Например, приложение для составления списков задач, где можно менять порядок задач, расставляя их по сроку выполнения или приоритету.

Изменение порядка задач возможно реализовать отложенными ограничениями:

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;

Проектировать такие схемы не стоит, но схема хранения может быть унаследованной. Пример вставки строк, которые можно выполнить только в одной транзакции, и только если внешний ключ - с отложенной проверкой:

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;

Если есть скрипт в виде набора команд INSERT, которые расположены не в порядке, удовлетворяющим ограничениям, то возможно, имеет смысл отложить выполнение всех ограничений до конца транзакции. В этом случае достаточно использовать 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 books
VALUES (1, 1, 'All Summer in a Day')
 , (2, 1, 'The Martian Chronicles')
 , (3, 2, 'Starship Troopers')
 , (4, 2, 'Podkayne of Mars');
INSERT INTO authors
VALUES (1, 'Ray Bradbury')
 , (2, 'Robert A. Heinlein');
COMMIT;

Вместо потенциально опасного подхода 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=false) допускают наличие повторяющихся значений, что снижает возможности планировщика запросов по оптимизации запроса.

Планировщик запросов не может быть уверен, что соблюдается уникальность. Это может негативно сказаться при соединении по уникальным столбцам или при выполнении запроса с условием 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

Планировщик может определить, что набор условий для таблицы обеспечивает уникальность результата. Если существует совместимый уникальный, не откладываемый индекс, планировщик может пропустить проверку на уникальность.

Поменяем ограничение на откладываемое и повторим запрос:

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 в соединение, если гарантируется уникальность столбца в подзапросе. С откладываемым ограничением оптимизация не работает, и во втором плане используется 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)

Если ограничение не откладываемое, то триггер не создаётся, поскольку проверка уникальности проверяется немедленно.

Если отключить триггеры, то ограничение целостности срабатывать не будет, и можно будет вставить строки, нарушающие ограничение:

truncate numbers;
set session_replication_role = replica;
insert into numbers values (1);
insert into numbers values (1);
select * from numbers;
 number 
--------
      1
      1
(2 rows)
SELECT conrelid::regclass table, conname, contype, condeferrable, convalidated 
FROM pg_constraint WHERE conname='numbers_number_key';
  table  |      conname       | contype | condeferrable | convalidated 
---------+--------------------+---------+---------------+--------------
 numbers | numbers_number_key | u       | t             | t
(1 row)

При этом, ограничение целостности будет иметь свойство VALIDATED. Не стоит полагаться на свойство VALIDATED у DERERRABLE ограничений целостности, оно не гарантирует отсутствия нарушений ограничения целостности существующими строками.

В PostgreSQL триггерами реализуются ограничения FOREIGN KEY, а также ограничения с откладываемой проверкой типов PRIMARY KEY, UNIQUE, EXCLUDE. Пример для EXCLUDE:

CREATE TABLE circles (c circle, EXCLUDE USING gist (c WITH &&) DEFERRABLE);

select tgrelid::regclass tab, tgconstrindid::regclass index, tgname, tgfoid::regproc as function_name, tgtype t, 
 CASE tgtype & 66 WHEN 2 THEN 'BEFORE ' WHEN 64 THEN 'INSTEAD OF ' ELSE 'AFTER ' END || 
 CASE tgtype & 60
  WHEN 4  THEN 'INSERT'
  WHEN 8  THEN 'DELETE'
  WHEN 16 THEN 'UPDATE'
  WHEN 20 THEN 'INSERT UPDATE'
  WHEN 32 THEN 'TRUNCATE'
  ELSE '?' END || 
  ' FOR EACH ' || CASE WHEN tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
AS firing_conditions, tgenabled e, tgisinternal i, tgdeferrable d, tginitdeferred id from pg_trigger;
   tab   |       index        |             tgname             |   function_name    | t  |        firing_conditions         | e | i | d | id 
---------+--------------------+--------------------------------+--------------------+----+----------------------------------+---+---+---+----
 authors | authors_id_key     | Unique_ConstraintTrigger_16800 | unique_key_recheck | 21 | AFTER INSERT UPDATE FOR EACH ROW | O | t | t | f
 numbers | numbers_number_key | Unique_ConstraintTrigger_16885 | unique_key_recheck | 21 | AFTER INSERT UPDATE FOR EACH ROW | O | t | t | f
 circles | circles_c_excl     | Unique_ConstraintTrigger_16905 | unique_key_recheck | 21 | AFTER INSERT UPDATE FOR EACH ROW | O | t | t | f
(3 rows)