ReSQL.ru
     
powered by simpleCommunicator - 2.0.40     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Update chunks
21 сообщений из 21, страница 1 из 1
Update chunks
    #40134112
pyDev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pyDev
Гость
Здравствуйте! Подскажите как реализовать апдейт "пачками". В таблице много записей и нужно проапдейтить один столбец по значению (WHERE column='some string')
Подскажите, пожалуйста, конструкцию. База Postgres.
...
Рейтинг: 0 / 0
15.02.2022, 00:05
| Ответить | Цитировать | Написать  
Update chunks
    #40134115
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
pyDev
Здравствуйте! Подскажите как реализовать апдейт "пачками". В таблице много записей и нужно проапдейтить один столбец по значению (WHERE column='some string')
Подскажите, пожалуйста, конструкцию. База Postgres.


Варианты -
1)если есть цифровой primary key - то по его диапазонам обновлять where id>=0 and id<1000 и так далее

Есть более быстрые но более геморойные в реализации методы на основе ctid если интересно расскажу отдельно завтра.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
15.02.2022, 00:32
| Ответить | Цитировать | Написать  
Update chunks
    #40134119
pyDev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pyDev
Гость
Maxim Boguk
pyDev
Здравствуйте! Подскажите как реализовать апдейт "пачками". В таблице много записей и нужно проапдейтить один столбец по значению (WHERE column='some string')
Подскажите, пожалуйста, конструкцию. База Postgres.


если интересно

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru


Интересно, буду благодарен
...
Рейтинг: 0 / 0
15.02.2022, 00:49
| Ответить | Цитировать | Написать  
Update chunks
    #40134145
Guzya
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guzya
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
-- скрипт для обновления

with batch(id) as (
    select id from <table_for_update> t 
    WHERE column='some string'
    order by id
    limit 2000000 
    for update skip locked
), upd as (
    update from  <table_for_update> 
    where id in (select id from batch)
    returning id
)
select now(),count(*) from batch;
...
Рейтинг: 0 / 0
15.02.2022, 10:01
| Ответить | Цитировать | Написать  
Update chunks
    #40134170
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Guzya
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
-- скрипт для обновления

with batch(id) as (
    select id from <table_for_update> t 
    WHERE column='some string'
    order by id
    limit 2000000 
    for update skip locked
), upd as (
    update from  <table_for_update> 
    where id in (select id from batch)
    returning id
)
select now(),count(*) from batch;



Это будет полный конец света так что то большое по обьёму обновлять там на миллиард строк.. да и на 100M тоже.
Не делайте так... (а именно в части WHERE column='some string'
order by id
limit 2000000 - оно будет вечно работать на крупных таблицах особенно по мере того как таких записей будет всё меньше оставаться).
Только диапазоны ID никаких order by/limit если вы с большими таблицами работаете.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
15.02.2022, 11:21
| Ответить | Цитировать | Написать  
Update chunks
    #40134183
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Maxim Boguk
Есть более быстрые но более геморойные в реализации методы на основе ctid если интересно расскажу отдельно завтра

Или уже не геморойные после обновления на pg14
Код: sql
1.
update ... where ctid >= '(N,0)' and ctid < '(N+K,0)'


где N итерировать от 0 до relpages из pg_class записи этой таблицы с интервалом K, например в 1000.
...
Рейтинг: 0 / 0
15.02.2022, 11:49
| Ответить | Цитировать | Написать  
Update chunks
    #40134188
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Melkij
Maxim Boguk
Есть более быстрые но более геморойные в реализации методы на основе ctid если интересно расскажу отдельно завтра

Или уже не геморойные после обновления на pg14
Код: sql
1.
update ... where ctid >= '(N,0)' and ctid < '(N+K,0)'


где N итерировать от 0 до relpages из pg_class записи этой таблицы с интервалом K, например в 1000.


Ну а на старых аналогично но через
Код: sql
1.
update ... where ctid = ANY (ARRAY(SELECT format('(%s,%s)', i, j)::tid  FROM generate_series(N,N+K-1) AS gs(i), generate_series(1,255) AS gs2(j)))


где N и К - аналогично выше...

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
15.02.2022, 12:04
| Ответить | Цитировать | Написать  
Update chunks
    #40134259
Misha111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Misha111
Гость
Maxim Boguk,

а не будет ли апдейт по предикату

where ctid >= '(N,0)' and ctid < '(N+K,0)'

"топтать" одни и те-же записи?

т.е. в самом грубом приближении в таблице 3 блока, апдейтим все строки в 1-ом блоке:
1-й апдейт обновил все строки в 1-м блоке, они "переехали" частично в 3-й блок, частично в 4-й.
2-й апдейт обновил строки во втором блоке и они перешли в 4-й блок.
3-й апдейт опять апдейтит строки в 3-м блоке, которые уже были обновлены на 1 шаге (к стати - если строка при апдейте не меняется - ее ctid изменится?)
и тд.

я так понимаю если N не ограничить значением из relpages эта "музыка будет вечной"?
те перед запуском таких обновлений таблицу лучше вакуумировать?
...
Рейтинг: 0 / 0
15.02.2022, 14:41
| Ответить | Цитировать | Написать  
Update chunks
    #40134451
Guzya
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guzya
Гость
А разве relpages не может быть не актуальным, т.е. не может быть сценария, когда в relpages значение меньше, чем есть на самом деле?
...
Рейтинг: 0 / 0
16.02.2022, 10:39
| Ответить | Цитировать | Написать  
Update chunks
    #40134454
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Misha111
Maxim Boguk,

а не будет ли апдейт по предикату

where ctid >= '(N,0)' and ctid < '(N+K,0)'

"топтать" одни и те-же записи?

т.е. в самом грубом приближении в таблице 3 блока, апдейтим все строки в 1-ом блоке:
1-й апдейт обновил все строки в 1-м блоке, они "переехали" частично в 3-й блок, частично в 4-й.
2-й апдейт обновил строки во втором блоке и они перешли в 4-й блок.
3-й апдейт опять апдейтит строки в 3-м блоке, которые уже были обновлены на 1 шаге (к стати - если строка при апдейте не меняется - ее ctid изменится?)
и тд.

я так понимаю если N не ограничить значением из relpages эта "музыка будет вечной"?
те перед запуском таких обновлений таблицу лучше вакуумировать?


1)Так а добавлять WHERE column IS DISTINCT FROM 'required value' то кто будет? Это в таких миграциях как бы самооочевидно.
2)если таблица большая то пока там процесс идёт autovacuum успеет 5 раз пройти минимум и почистить свободное место в начале блоко
3)ВАЖНО - после такой миграции надо подсчитать сколько мы случайно пропустили строк и обычным update их обновить (опять же по WHERE column IS DISTINCT FROM 'required value' )... обычно там несколько строк попадают мимо обработки на нагруженных по записи таблицах при такой обработке
4)ctid при update меняется безусловно даже если ничего в строке не поменяли и даже если HOT update сработал

так что музыка вечной не будет но ограничить реальным размером таблицы (pg_relation_size) в любом случае надо.
Обычно просто набор миграционных update генерируют запросом и через \gexec запускают на выполнение.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
16.02.2022, 10:49
| Ответить | Цитировать | Написать  
Update chunks
    #40134456
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Guzya
А разве relpages не может быть не актуальным, т.е. не может быть сценария, когда в relpages значение меньше, чем есть на самом деле?


Может поэтому лучше pg_relation_size смотреть на самом деле.
Но он тоже может по ходу миграции измениться.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
16.02.2022, 10:50
| Ответить | Цитировать | Написать  
Update chunks
    #40134463
Guzya
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guzya
Гость
Еще такой вопрос

Код: sql
1.
SELECT format('(%s,%s)', i, j)::tid  FROM generate_series(N,N+K-1) AS gs(i), generate_series(1,255) AS gs2(j)



j - это номер записи(смещение), но ведь на странице может быть разное количество записей (зависит от длины записи), в том числе больше 255.

Или этот вопрос, как и предыдущий должен "закрываться" контрольным(простым) update в конце?
...
Рейтинг: 0 / 0
16.02.2022, 11:04
| Ответить | Цитировать | Написать  
Update chunks
    #40134468
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Guzya
Еще такой вопрос

Код: sql
1.
SELECT format('(%s,%s)', i, j)::tid  FROM generate_series(N,N+K-1) AS gs(i), generate_series(1,255) AS gs2(j)



j - это номер записи(смещение), но ведь на странице может быть разное количество записей (зависит от длины записи), в том числе больше 255.

Или этот вопрос, как и предыдущий должен "закрываться" контрольным(простым) update в конце?


"в том числе больше 255." - это каким образом? (если база конечно руками не пересоздана с другим blocksize).
блок 8kb + минимальный размер записи меньше 32байт не бывает + заголовок страницы.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
16.02.2022, 11:33
| Ответить | Цитировать | Написать  
Update chunks
    #40134475
Guzya
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guzya
Гость
Maxim Boguk
минимальный размер записи меньше 32байт не бывает


Понял, спасибо!
...
Рейтинг: 0 / 0
16.02.2022, 11:47
| Ответить | Цитировать | Написать  
Update chunks
    #40134483
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim Boguk
Guzya
А разве relpages не может быть не актуальным, т.е. не может быть сценария, когда в relpages значение меньше, чем есть на самом деле?

Может поэтому лучше pg_relation_size смотреть на самом деле.
Но он тоже может по ходу миграции измениться.

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

если relpages окажется меньше, чем надо — сделайте частичный индекс и добейте “в лоб” одним запросом то, что осталось.
если relpages получится больше — будут ошибки при обращении к страницам вне диапазона. можно вырубить vacuum_truncate для таблицы на время миграции.
...
Рейтинг: 0 / 0
16.02.2022, 12:13
| Ответить | Цитировать | Написать  
Update chunks
    #40134645
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
Для Oracle я делал обновление порциями через хеш от PK. Для толстых табличек где нет partitions.

Что-то типа

Код: sql
1.
2.
3.
4.
5.
UPDATE tab SET column='some string' WHERE ora_hash(id, 8) = 0;
commit;
UPDATE tab SET column='some string' WHERE ora_hash(id, 8) = 1;
commit;
...



Правда не знаю как такая практика пригодна для PG. Уплотнять надо после каждого прохода.

Можно попробовать заменить ora_hash на md5 с нужными параметрами и я думаю эффект - такойже будет.
...
Рейтинг: 0 / 0
16.02.2022, 20:37
| Ответить | Цитировать | Написать  
Update chunks
    #40134658
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
mayton
Для Oracle я делал обновление порциями через хеш от PK. Для толстых табличек где нет partitions.

Что-то типа

Код: sql
1.
2.
3.
4.
5.
UPDATE tab SET column='some string' WHERE ora_hash(id, 8) = 0;
commit;
UPDATE tab SET column='some string' WHERE ora_hash(id, 8) = 1;
commit;
...



Правда не знаю как такая практика пригодна для PG. Уплотнять надо после каждого прохода.

Можно попробовать заменить ora_hash на md5 с нужными параметрами и я думаю эффект - такойже будет.


Не эффективно (но возможно), так как индекс по id не будет использоваться... и на какой то таблице в пару терабайт размером - можно будет повесится (тем более что там надо будет по 1/1000000 делать лучше всего а это seq scan ами вечность займёт).
Поэтому таки или по диапазонам id или по ctid (cамое быстрое так как на физическую адресацию в блоках базы завязано и очень по IO эффективно).

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
16.02.2022, 21:43
| Ответить | Цитировать | Написать  
Update chunks
    #40134665
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
Да. В моем варианте будет 8 fulltable scan.

На это и был расчет. Можно 4 или 2.
...
Рейтинг: 0 / 0
16.02.2022, 22:03
| Ответить | Цитировать | Написать  
Update chunks
    #40134754
gav21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gav21
Участник
когда то обновлял такой процедурой - размер пачки передаете параметром, после каждой итерации будет коммит, чтобы не растягивать транзакцию, и не потерять прогресс в случае отмены

create or replace procedure update_chunks(cnt int) as $$

DECLARE
totalcnt bigint:=0;
updated bigint:=0;
BEGIN
select into totalcnt count(*) from test where col <> 'new';
while updated < totalcnt
loop
update test set col = 'new' where id in (select id from test where col <> 'new' limit cnt);
updated:=updated+cnt;
raise info 'updated: % / % ' ,updated,totalcnt;
commit;
end loop;

END;
$$ LANGUAGE plpgsql
...
Рейтинг: 0 / 0
17.02.2022, 12:13
| Ответить | Цитировать | Написать  
Update chunks
    #40134759
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
В постгресах жеж нет UNDO-сегмента. Короче мой метод не подходит. Короче вот ораклячий опыт вообще
не применим в PG. Ничего не сэкономите.
...
Рейтинг: 0 / 0
17.02.2022, 12:30
| Ответить | Цитировать | Написать  
Update chunks
    #40135253
Ринат Н.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ринат Н.
Гость
Посмотрите ещё на loop_execute() .
...
Рейтинг: 0 / 0
19.02.2022, 08:10
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Update chunks
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=40134119&tid=1993651]:
 0ms
get settings:
 11ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 23ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 255ms
total:  382ms
созд. / загр.: 382ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]