Hi Kristjan,
Thank you for this information.
"postgres the memory is slowly eaten away when doing updates within plsql loop" for this memory issue, I want to check if it exists in our current postgresql version. And let developer change to use python for loop also need to show them the proof, how to reproduce and check the memory slowly eaten away please? OS level cmd or psql cmd to verify? thanks
Kristjan Mustkivi <sonicmonkey@xxxxxxxxx> 于2021年3月2日周二 下午5:26写道：
Hi Yi,
I found that in postgres the memory is slowly eaten away when doing
updates within plsql loop. It only gets released once the whole block
completes. While it is ok for small tables you will eventually run out
of memory for really big ones. The working approach was to do the loop
in e.g a python script that called the DML statements and also called
commit. Several million rows is fortunately relatively small number to
update but once you get to billions this approach would not likely
work. Note that after each batch you also should call VACUUM before
starting a new one to avoid significant table bloat.
BR,
Kristjan
On Wed, Feb 24, 2021 at 3:01 PM Yi Sun <yinan81@xxxxxxxxx> wrote:
>
> Hello,
>
> Now need to update several million records in a table in prd DB, if can use batch update 1000 records and commit each time, if it will affect prd application like below sample script please?
>
> Sample script:
>
> DO $MAIN$
> DECLARE
> affect_count integer;
> chunk_size CONSTANT integer :=1000;
> sleep_sec CONSTANT numeric :=0.1;
> BEGIN
>
> loop
>
> exit when affect_count=0;
>
> UPDATE tbl a
> SET name = ''
> WHERE a.id IN (SELECT id
> FROM tbl b
> WHERE name IS NULL
> LIMIT chunk_size);
>
> GET DIAGNOSTICS affect_count = ROW_COUNT;
>
> commit;
>
> PERFORM pg_sleep(sleep_sec);
>
> end loop;
> END;
> $MAIN$;
>
> Thanks and best regards
--
Kristjan Mustkivi
Email: kristjan.mustkivi@xxxxxxxxx
