#python #sql #python-3.x #postgresql #psql
Вопрос:
Мне нужно обновить один столбец в таблице с огромным набором данных в базе данных PostgreSQL.
Поскольку задание может выполняться непрерывно в течение 1 или 2 дней из-за большого набора данных, мне нужно выполнить это пакетно и зафиксировать транзакции пакетно, чтобы я мог отслеживать ход выполнения и распечатывать журналы любых неудачных пакетов и запускать их вручную позже, указав смещение и ограничение сбоя.
Один из методов, который я попытался сделать, заключается в следующем в блоке postgres, который не удался, так как я не могу использовать row_number() в предложении where.
DO LANGUAGE plpgsql $ DECLARE row_count_ integer; offset_ integer := 0; batch_size_ integer := 100000; limit_ integer := offset_ batch_size_; total_rows_ integer; BEGIN WHILE offset_ lt; total_rows_ LOOP limit_ := offset_ batch_size_; UPDATE table1 SET column1 = 'Value' WHERE row_number() over() gt;= offset_ AND row_number() over() lt; limit_; GET DIAGNOSTICS row_count_ = row_count; RAISE INFO '% rows updated from % to %', row_count_, offset_, limit_; offset_ := offset_ batch_size_; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Transaction is rolling back, % : %', SQLSTATE, SQLERRM; ROLLBACK; END $; Я даже могу сделать это с помощью скрипта python, но мне нужно сделать это как можно быстрее. Я просмотрел много статей, в которых используется подзапрос select, который, на мой взгляд, слишком дорог из-за объединения.
Не мог бы кто-нибудь, пожалуйста, помочь мне с лучшим способом достижения этой цели?
Комментарии:
1. Если каждая строка обновляется с одинаковым значением, вы можете просто добавить столбец с этим значением по умолчанию. Это будет почти мгновенно в версии 11 или выше. (И до 11-обновите как минимум до 11.)
Ответ №1:
Если действие длится несколько дней, UPDATE имеет смысл выполнять его пакетами. Возможно, вам захочется выполнить явный VACUUM анализ таблицы между пакетами, чтобы избежать раздувания таблицы.
Что касается вашей основной проблемы, я бы сказал, что самым простым решением было бы выполнить пакетирование по значениям первичного ключа, то есть выполнить такие инструкции, как:
UPDATE tab SET col = newval WHERE id lt;= 100000 AND /* additional criteria*/; VACUUM tab; UPDATE tab SET col = newval WHERE id gt; 100000 AND id lt;= 200000 AND /* additional criteria*/; ... Продолжайте повторять это, пока не достигнете максимума id .