On 1/6/20 10:36 AM, Israel Brewster wrote:
Thanks to a change in historical data, I have a need to update a large
number of records (around 50 million). The update itself is straight
forward, as I can just issue an "UPDATE table_name SET
changed_field=new_value();" (yes, new_value is the result of a stored
procedure, if that makes a difference) command via psql, and it should
work. However, due to the large number of records this command will
obviously take a while, and if anything goes wrong during the update
(one bad value in row 45 million, lost connection, etc), all the work
that has been done already will be lost due to the transactional nature
of such commands (unless I am missing something).
Given that each row update is completely independent of any other row, I
have the following questions:
1) Is there any way to set the command such that each row change is
committed as it is calculated?
Pretty sure:
UPDATE table_name SET changed_field=new_value();
is seen as a single statement and is all or none.
If you want to go row by row you will need to have the statement run on
a row by row basis or maybe in batches.
2) Is there some way to run this command in parallel in order to better
utilize multiple processor cores, other than manually breaking the data
into chunks and running a separate psql/update process for each chunk?
Honestly, manual parallelizing wouldn’t be too bad (there are a number
of logical segregations I can apply), I’m just wondering if there is a
more automatic option.
This is good time to ask what Postgres version?
I am still working out the recent parallel query system additions. Not
sure if it applies to UPDATE or not.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
