Структура данных для хранения поля сортировки для эффективного разрешения модификаций

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

A  10   to  B  10
B  25       C  25
C  26       E  26
E  34       A  34

Где может быть любое количество элементов между каждой строкой. Итак, сначала вы читаете записи и создаете список [['A', 10], ['B', 25], ['C', 26], ['E', 34]] . С помощью некоторой питонической магии вы перемещаете идентификаторы и вставляете их во временную таблицу:

create temporary table reorder (
    id varchar(20), -- whatever
    sort_order number,
    primary key (id));

Теперь для обновления:

update table XYZ
set sort_order = (select sort_order from reorder where xyz.id = reorder.id)
where id in (select id from reorder)

Я только предполагаю, что pgsql может обработать этот запрос. Если это возможно, оно будет атомарным.

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

EDIT: Есть некоторые проблемы с транзакцией. Возможно, вам понадобится реализовать обе мои идеи. Если два процесса хотят обновить элемент B (например), могут возникнуть проблемы. Итак, предположим, что все значения порядка четны:

1. Begin Transaction
2. Увеличивает все используемые заказы на 1. Это устанавливает блокировку записи на уровне строк для всех строк, которые вы собираетесь обновить.
3. Выберите данные, которые вы только что обновили. обновлено, если какие-либо поля sort_order - это даже какой-то другой процесс, добавивший запись, соответствующую вашим критериям. Вы можете либо прервать транзакцию и перезапустить ее, либо просто отбросить запись и завершить операцию, используя только записи, которые были обновлены на шаге 2. «Правильный» шаг зависит от того, что вам нужно для выполнения этого кода.
4. Заполните вашу временную таблицу переупорядочения, как указано выше, используя правильные четные sort_orders.
5. Обновите основную таблицу, как указано выше.
6. Отбросьте временную таблицу. рассматриваемый, пока транзакция не завершится:

   update XYZ set sort_order = sort_order + 1
   where -- whatever your select criteria are
   
   select * from XYZ
   where -- same select criteria
   order by sort_order
   

   В качестве альтернативы, вы можете добавить поле управления в таблицу, чтобы получить тот же эффект, и тогда вам не нужно играть с полем sort_order . Преимущество использования поля sort_order заключается в индексировании с помощью поля BIT или поля LOCK_BY_USERID , когда поле обычно имеет значение NULL, как правило, имеет низкую производительность, поскольку индекс в 99% случаев не имеет смысла . Механизмы SQL не любят индексы, которые большую часть времени проводят пустыми.

Вы можете решить проблему перенумерации, сделав столбец порядка целым числом, которое всегда является четным числом. Когда вы перемещаете данные, вы меняете поле порядка на новое значение сортировки + 1, а затем выполняете быстрое обновление, чтобы преобразовать все поля нечетного порядка в четные:

update table set sort_order = bitand(sort_order, '0xFFFFFFFE')
where sort_order <> bitand(sort_order, '0xFFFFFFFE')

Таким образом, вы можете сохранить уникальность sort_order как ограничение

РЕДАКТИРОВАТЬ: Хорошо, взглянув на вопрос еще раз, я начал новый ответ.

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

Итак, вопрос в том, можете ли вы сделать это «способом Django», а не с помощью прямого SQL. Поиск по «таблице блокировки django» обнаружил несколько интересных ссылок, в том числе этот фрагмент , есть много других, которые реализуют подобное поведение.

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

Мне очень любопытно услышать, как это получится и каково ваше окончательное решение, обязательно держите нас в курсе!

Почему бы не создать сначала простое символьное поле некоторой длины, например максимум 16 (или 255).

Начните сначала с маркировки вещей от aaa до zzz (это должно быть 17576 записей). (Вы также можете добавить 0–9, а также прописные буквы и символы для оптимизации.)

По мере добавления элементов они могут доходить до конца до максимума, который вы разрешаете для дополнительных «конечных времен» (zzza , zzzaa, zzzaaa, zzzaab, zzzaac, zzzaad и т. д.)

Это должно быть достаточно простым в программировании и очень похоже на десятичную систему Дьюи.

Да, вам нужно будет время от времени балансировать ее, но это должна быть простая операция. Самый простой подход - это два прохода, на первом этапе нужно установить для нового тега упорядочения значение «0» (или любой символ до первого символа), за которым следует новый тег соответствующей длины, и шаг 2 будет заключаться в удалении спереди нуля.

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