Атомное многострочное обновление с уникальным ограничением

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

create table label (
  id_label serial not null,
  rank integer not null,
  title text not null,
  constraint pri primary key (id_label),
  constraint unq unique (rank)
)

Не имеет значения, являются ли они PostgreSQL или MySQL, они демонстрируют одинаковое поведение. Запрос может выглядеть как select title from label order by rank. Предположим, что таблица содержит:

id_label rank title
1        10   Cow
2        20   Apple
3        45   Horse
4        60   Beer

Теперь предположим, что я хочу изменить порядок двух меток, например. Apple занимает до коров. Самый простой способ - изменить их ранговые значения:

update label
set rank = case when rank = 20 then 10 else 20 end
where id_label in (1,2)

Неа. Также:

update label
set rank = case when rank = 20 then rank - 10 else rank + 10 end
where id_label in (1,2)

И даже:

update label
set rank = 30 - rank
where id_label in (1,2)

Каждый раз уникальное ограничение срабатывает при обновлении первой строки и прерывает операцию. Если бы я мог отложить чек до конца заявления, я был бы в порядке. Это происходит как на PostgreSQL, так и на MySQL.

Обходной путь, безопасный для использования с помощью ACID, заключается в следующем:

• начать транзакцию
• выберите ранги первой, второй записи и наивысшего (max) ранга в таблице (что, скорее всего, потребует объединение)
• обновить первую запись до ранга = max + 1
• обновить вторую запись до ранжирования первого
• обновить первую запись до ранга секунды
• совершить

Это просто невыразимо уродливое. Хуже того, чтобы отказаться от ограничения, обновить, а затем воссоздать ограничение. Предоставление таких привилегий оперативной роли требует неприятностей. Поэтому мой вопрос заключается в следующем: есть ли простая техника, которую я упустил, которая решает эту проблему, или я SOL?

Ответы

create table label (
  id_label serial not null,
  rank integer not null,
  title text not null,
  constraint pri primary key (id_label)
);
alter table label add constraint unique_rank unique (rank) 
      deferrable initially immediate;

begin;
set constraints unique_rank DEFERRED;
update rank
   set rank = case when rank = 20 then 10 else 20 end
   where id_label in (1,2);
commit;

update label set rank = 5 where id_label=2

create table label (
  id_label serial not null,
  rank numeric not null,
  title text not null,
  constraint pri primary key (id_label),
  constraint unq unique (rank)
)