Ограничение определено ОПАСНОЕ ИНИЦИАЛИЗИРОВАННОЕ ПОСЛЕДУЮЩЕЕ ПОСЛЕДОВАТЕЛЬНО?

В связи с этот ответ я наткнулся на феномен, который я не могу объяснить.

Версия:
PostgreSQL 9.1.2 на x86_64-unknown-linux-gnu, скомпилированный gcc-4.4.real(Debian 4.4.5-8) 4.4.5, 64-разрядный

Рассмотрим следующую демонстрационную версию. Testbed:

CREATE TEMP TABLE t (
  id  integer
 ,txt text
 ,CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO t VALUES
 (1, 'one')
,(2, 'two');

1) Оператор UPDATE, изменяющий несколько строк:

UPDATE t
SET    id = t_old.id
FROM   t t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));

Кажется, в текущей реализации есть ошибка? Вышеупомянутое UPDATE работает, хотя не должно. Ограничение определено INITIALLY IMMEDIATE, и я не использовал SET CONSTRAINTS.

Я что-то упустил или это (довольно безобидная) ошибка?

2) Изменение данных CTE

Следовательно, данные, модифицирующие CTE, тоже работают, хотя с ошибкой NOT DEFERRED pk:

WITH x AS (
    UPDATE t SET id = 1 WHERE id = 2
    )
UPDATE t SET id = 2 WHERE id = 1;

Я цитирую руководство по CTE:

Подзапросы в WITH выполняются одновременно друг с другом и с основным запросом. Поэтому при использовании модификации данных в WITH, порядок, в котором указанные обновления фактически произойдет непредсказуемо. Все утверждения выполняются с тем же моментальный снимок (см. главу 13), поэтому они не могут "видеть" эффекты друг друга на целевых таблицах.

3) Несколько операторов UPDATE в одной транзакции

Без SET CONSTRAINTS это не выполняется с УНИКАЛЬНЫМ нарушением - как и ожидалось:

BEGIN;
-- SET CONSTRAINTS t_pkey DEFERRED;
UPDATE t SET id = 2 WHERE txt = 'one';
UPDATE t SET id = 1 WHERE txt = 'two';
COMMIT;

Ответы

Ответ 1

Я помню, что поднял почти идентичную точку, когда PG9 находился в альфа-состоянии. Здесь был ответ от Tom Lane (высококлассный разработчик PG): http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php

Вкратце: не будет исправлено.

Нельзя сказать, что я согласен с вашим предложением о том, что текущее поведение является ошибкой. Посмотрите на него с противоположного угла: это неправильное поведение NOT DEFERRABLE.

На самом деле, нарушение ограничения в этом UPDATE никогда не должно происходить в любом случае, так как в конце UPDATE ограничение выполняется. Состояние, стоящее в конце команды, имеет значение. Промежуточные состояния во время выполнения одного оператора не должны быть доступны пользователю.

Похоже, что PostgreSQL реализует не откладываемое ограничение, проверяя дубликаты после каждой обновленной строки и немедленно сбиваясь с первого дубликата, что по существу является недостатком. Но это известная проблема, вероятно, такая же старая, как PostgreSQL. В настоящее время обходным путем для этого является использование ограничения DEFERRABLE. И есть какая-то ирония в том, что вы смотрите на нее как на недостающую, потому что она не сработает, а как-то она должна быть решением проблемы в первую очередь!

Сводка статус-кво в PostgreSQL 9.1

  • Ограничения

  • NOT DEFERRABLE UNIQUE или PRIMARY KEY проверяются после каждой строки.

  • DEFERRABLE ограничения, установленные на IMMEDIATE (INITIALLY IMMEDIATE или через SET CONSTRAINTS), проверяются после каждого утверждения.

  • DEFERRABLE ограничения, установленные на DEFERRED (INITIALLY DEFERRED или через SET CONSTRAINTS), проверяются после каждой транзакции.

Обратите внимание на специальную обработку ограничений UNIQUE/PRIMARY KEY. Цитирование страницы руководства для CREATE TABLE:

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

Пока он находится в разделе совместимости в разделе Non-deferred uniqueness constraints:

Когда ограничение UNIQUE или PRIMARY KEY не откладывается, PostgreSQL проверяет уникальность сразу всякий раз, когда строка вставлена ​​или модифицирована. В стандарте SQL говорится, что уникальность должна быть соблюдена только в конце инструкции; это имеет значение, когда для Например, одна команда обновляет несколько значений ключа. Чтобы получить стандартное поведение, объявите ограничение как DEFERRABLE, но не откладывается (т.е. INITIALLY IMMEDIATE). Имейте в виду, что это может быть значительно медленнее, чем немедленная проверка уникальности.

Смелый акцент мой.

Если вам нужны какие-либо ограничения FOREIGN KEY для ссылки на столбец (столбцы), DEFERRABLE не является опцией, потому что (для документации)

Указанные столбцы должны быть столбцами неотделяемой уникальной или ограничение первичного ключа в ссылочной таблице.

Ответ 2

Здесь может быть небольшая ошибка документации, но не для случая, который вы показываете. Если вы НАЧИЛИТЕ транзакцию и каждый раз проверяете обновления, они терпят неудачу, но если одно выражение оставляет вещи в хорошем состоянии, оно не жалуется. Документы говорят:

Если ограничение откладывается, это предложение указывает время по умолчанию для проверки ограничения. Если ограничение НЕИМАЛЬНО НЕМЕДЛЕННО, это проверяется после каждого утверждения. Это значение по умолчанию. Если ограничение INITIALLY DEFERRED, оно проверяется только в конце сделка.

Это именно то, что, похоже, происходит. Что для меня неожиданно, учитывая документацию DEFERRABLE, в которой говорится, в частности:

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

Без параметров DEFERRABLE INITIALLY IMMEDIATE обновление примера не выполняется, хотя оператор UPDATE (предположительно составляющий "команда" ) оставляет вещи в хорошем состоянии. Возможно, документы должны быть изменены, чтобы сказать, что ограничение NOT DEFERRABLE принудительно, поскольку каждая строка изменяется с помощью инструкции?