Есть ли штатный способ разбить большой UPDATE на транзакционные части?

Question

[beduin01]

Нужно выполнить апдейт столбца в таблице на 10 млн позиций.
т.к. update транзакционный то пока все строки не заапдейтятся.
Вопрос. Можно ли штатными средствами сделать так, чтобы UPDATE шел или построчно или какими-то кусками?
Да можно написать кучу:
UPDATE .... WHERE ID > 0 AND ID < 1000
UPDATE .... WHERE ID > 1000 AND ID < 2000

Но может можно как-то попроще?

Comments

[Армянское Радио]

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

[Алексей Черемисин]

да, так обычно и делается. но обычно это делается в цикле на каком нибудь языке программирования (например python), чтобы не писать кучу одинаковых строк.
Про питон и работу с базой модно почитать здесь например - https://khashtamov.com/ru/postgresql-python-psycopg2/

[beduin01]

Армянское Радио, да просто сделать апдейт по частям без дробления запроса руками

[pfg21]

beduin01, можно найти какойлибо внешний скриптик, который будет неидеально тебе подходить доплить его руками.

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

хотя бы тем же копипастом с изменением циферок
или напиши скрипт с циклом.

[calculator212]

Если я правильно понял, то все дело в долгом update это может помочь

Answer 1

[d-stream]

Долго ковырял подобное, в итоге пришел примерно к такой реализации:
- таблица pk для обновления
- прикладная часть запрашивает диапазоны из этой таблицы и дёргает процедуру обновления с указанием диапазона
- старт идёт с короткого диапазона, если время выполнения не превышает психологический порог - на следующей итерации - берётся порция большего размера и так до порога времени выполнения

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

Answer 2

[mayton2019]

Можно так попробовать

CREATE EXTENSION pgcrypto;

BEGIN;
 // Partition 1 of 16
 UPDATE .... WHERE left(digest(ID, 'sha256'),1) = '0' 
COMMIT;

BEGIN;
 // Partition 2 of 16
 UPDATE .... WHERE left(digest(ID, 'sha256'),1) = '1' 
COMMIT;

У Oracle была более простая функция hash(...) которая разделяла сет ключей сразу на известное число партишенов. Там второй аргумент указывался. Но в PG я не знаю аналога. Может знающие подскажут.

Comments

[mayton2019]

И там насчет SHA256 я был не прав. Можно более простой хешик взять. Нам только 1-я HEX цифра нужна для распределения.

[Melkij]

Только это худший из вариантов с точки зрения ресурсов. Гарантированный seqscan, полный промах по локальности данных, кэшей и всему что можно.

[mayton2019]

Melkij, если у автора есть partitioning можно по другому сыграть. Но конечно главный вопрос - какая решается проблема. Вот его беспокоит что 10 млн обновляются в одной транзакции.

[Melkij]

а, мне лишний нолик-другой привиделся. На 10млн не важно каким способом на части пилить. Можно и хэшом или остатком от деления обойтись, если лениво.

[mayton2019]

Melkij, я когда экономил undo-segment в Оракле такое делал. Но это вобщем все технические кейсы. Реально - транзакция должна ограничиваться только business rules.

Я так понимаю что если у Постгреса нету undo-segment (у него собсно таблица и есть этот сегмент) то и проблемы такой нету.

Answer 3

[Melkij]

Как делить одну большую операцию на части - необходимо базе объяснять.

PG14 - нагенерировать запросов

update ... where ctid between '(N,1)' and '(N+1000,1)'

где N от 0 до relpages из pg_class и запустить.

Если хочется - можно обернуть в анонимку DO, каждую очередную итерацию делая commit.

На более старых версиях - выбрать какой-нибудь индекс скоррелированный с физическим расположением данных и идти вдоль него, нагенерировав запросов
update ... where somecolumn between ? and ?
кусками некоторого размера. Не имеет решающего значения соблюдение размера одного обновления, лишь бы выполнялся за разумное время.

Во время миграции поглядывайте, успевает ли autovacuum за вами, успевают ли реплики и archiver (если есть).