06.07.2026 04:40
OlegIct 0 3900 Источник

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

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

Примеры использования

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

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

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. Загрузка данных или восстановление дампа

Если есть скрипт в виде набора команд 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;

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)

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

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

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)

Комментарии (0)