What we would do when having to update a large table is do it in multiple transactions. Basically:
1. create sequence s1;
2. alter table t1 add column c1 integer default nextval('s1'); -- Ensure new values don't need changing anymore
3. In batches of a significant but not huge amount of rows: update t1 set c1=nextval('s1');
4. Create index
5. Convert the index into pk index
1. create sequence s1;
2. alter table t1 add column c1 integer default nextval('s1'); -- Ensure new values don't need changing anymore
3. In batches of a significant but not huge amount of rows: update t1 set c1=nextval('s1');
4. Create index
5. Convert the index into pk index
We use niceupdate (home brew tool) for this, it may suit your needs:
Regards,
Feike
