Меняем места элементов: отложенная уникальность PostgreSQL

У списка есть уникальная позиция внутри board_id. Нужно обменять местами элементы на позициях 1 и 2. Если сначала присвоить первому позицию 2, немедленная проверка обнаружит занятую позицию, хотя после второго изменения список снова станет правильным.

Здесь требуется сохранить уникальность конечного состояния транзакции, допустив временный конфликт между двумя UPDATE. Ограничение нужно заранее объявить DEFERRABLE. Обычный уникальный индекс нельзя сделать отложенным одной командой SET CONSTRAINTS.

Как изменить решение

CREATE TABLE cards (
  id bigint PRIMARY KEY,
  board_id bigint NOT NULL,
  position integer NOT NULL,
  CONSTRAINT cards_position_uq
    UNIQUE (board_id, position) DEFERRABLE INITIALLY IMMEDIATE
);
BEGIN;
SET CONSTRAINTS cards_position_uq DEFERRED;
UPDATE cards SET position = 2 WHERE id = 101;
UPDATE cards SET position = 1 WHERE id = 102;
COMMIT;

При условии, что карточки 101 и 102 относятся к одному списку и начинали на позициях 1 и 2, конечное состояние снова уникально. Ошибка возникнет при проверке ограничения, если вторая команда не выполнена или оставила конфликт.

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

Что проверить на своих данных

Проверьте успешный обмен и намеренно пропустите второй UPDATE. Во втором случае COMMIT должен отказать. Приложение не должно отвечать об успехе до фиксации: именно на этом этапе отложенная проверка способна вернуть ошибку.

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

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: Меняем места элементов — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.