Dear Members!
I've read this article, but I need your experience in this theme.
alter table tk
add colum field1 default 'MUCH';
The table tk have 200 million rows. The autovacuum is no problem, only the long update.
But as I read the alter makes table lock, so this update locks the table for long time.
The article said we need to do this:
1. add column without default - fast.
2. set default on column.
3. update it where is null.
What we can save with this method?
As I suppose the table lock substituted with long update (row locks on whole table).
The article suggested to replace long update to shorter sequences (10000-100000 records by cycle).
We used to execute these SQL-s (alter, default, update) in one transaction.
So I can't make commit there.
What is the difference between "full update" and "updates by 10000 records" when I'm in a single transaction?
Is it possible that this pseudo code makes less locks WITHOUT INNER COMMIT?
Pseudo:
----------------------
begin
while True loop
update tk set field1 = ' MUCH' when field1 is NULL and id in
(select id from tk where field1 is NULL limit 100000);
if not FOUND then
break;
end while;
end
----------------------
Is there any advance when I split updates? I'm in one transaction.
TR. START
----
1. alter add col
2. set default
3. updates
---
TR. COMMIT
Or it isn't help me?
Because the whole transaction locks the other users also, just like "alter add colum wit hdefault statement"?
Thank you for your and help!
Best regards
dd