Подреждане на Postgres, актуализация и заключване

да Мисля, че основният проблем тук е, че IN проверява за членство в посочения набор, но не придава никакъв вид подреждане на UPDATE, което от своя страна означава, че не се придава конкретно подреждане при подреждането на заключването.

Клаузата WHERE в оператор UPDATE по същество се държи по същия начин, както в SELECT. Например, често ще симулирам UPDATE с помощта на SELECT, за да проверя какво ще се актуализира, за да видя дали е това, което очаквах.

Имайки предвид това, следващият пример с използване на SELECT демонстрира, че IN само по себе си не предоставя подреждане:

Като се има предвид тази схема/данни:

create table foo
(
  id serial,
  val text
);

insert into foo (val)
values ('one'), ('two'), ('three'), ('four');

Следните запитвания:

select *
from foo
where id in (1,2,3,4);


select *
from foo
where id in (4,3,2,1);

дават точно същите резултати -- редовете в ред от id 1-4.

Дори това не е гарантирано, тъй като не използвах ORDER BY в селекцията. По-скоро без него Postgres използва реда, който сървърът реши, че е най-бърз (вижте точка 8 относно ORDER BY в Postgres ИЗБЕРЕТЕ документ). Като се има предвид доста статична таблица, често е същият ред, в който е била вмъкната (както беше случаят тук). Въпреки това, няма нищо, което да гарантира това и ако има много отлив на масата (много мъртви кортежи, премахнати редове и т.н.), е по-малко вероятно това да е така.

Подозирам, че това се случва тук с вашия UPDATE. Понякога -- ако не дори през повечето време -- може да се окаже в цифров ред, ако това е същият начин, по който са били вмъкнати редовете, но няма нищо, което да гарантира това, а случаите, в които виждате блокиранията, са вероятни сценарии, при които данните се промени така, че една актуализация е подредена по различен начин от другата.

sqlfiddle с горния код.

Възможни корекции/заобиколни решения:

По отношение на това какво можете да направите по въпроса, има различни опции в зависимост от вашите изисквания. Можете изрично да премахнете заключване на таблица на масата, въпреки че това, разбира се, ще има ефект на сериализиране на актуализациите там, което може да се окаже твърде голямо затруднение.

Друг вариант, който все пак би позволил паралелност -- е изрично итериране на елементите с помощта на динамичен SQL в, да речем, Python. По този начин бихте имали набор от едноредови актуализации, които се случват винаги в един и същи ред, и тъй като можете да гарантирате този последователен ред, нормалното заключване на Postgres трябва да може да се справи с паралелността без задънена улица.

Това няма да работи толкова добре, колкото пакетното актуализиране в чист SQL, но трябва да реши проблема със заключването. Едно предложение за повишаване на производителността е само COMMIT толкова често, а не след всеки ред -- това спестява много режийни разходи.

Друг вариант би бил да направите цикъла във функция Postgres, написана на PL/pgSQL. След това тази функция може да бъде извикана външно, да речем, в Python, но цикълът ще се извърши (също изрично) от страна на сървъра, което може да спести някои допълнителни разходи, тъй като цикълът и UPDATEs се извършват изцяло от страна на сървъра, без да се налага да преминавате през кабела при всяка итерация на цикъл.

Няма ORDER BY в командата UPDATE.
Но има за SELECT. Използвайте заключване на ниво ред< /a> с FOR UPDATE клауза в подзаявка:

UPDATE foo f
SET    a = 1
FROM (
   SELECT b FROM foo
   WHERE  b IN (1,2,3,4)
   ORDER BY b
   FOR   UPDATE
   ) upd
WHERE f.b = upd.b;

Разбира се, b трябва да бъде UNIQUE или трябва да добавите повече изрази към клаузата ORDER BY, за да я направите недвусмислена.

И трябва да наложите същия ред за всички UPDATE, DELETE и SELECT .. FOR UPDATE изрази в таблицата.

Свързани, с повече подробности:

• Postgres АКТУАЛИЗАЦИЯ … ЛИМИТ 1
• Избягване на блокирания на PostgreSQL при извършване на групова актуализация и изтриване на операции
• Оптимизиране на едновременните актуализации в Postgres