From: | Sergei Kornilov <sk(at)zsrv(dot)org> |
---|---|
To: | Timokhin Maxim <ncx2(at)yandex(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Updating a large table |
Date: | 2018-01-09 12:53:44 |
Message-ID: | 1037971515502424@web26o.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello
> 1. ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT FALSE;
this is wrong. To avoid large table lock you need DEFAULT NULL:
ALTER TABLE clusters ADD COLUMN "is_paid" BOOLEAN DEFAULT NULL;
Default null changes only system catalog, default with any non-null value will rewrite all rows. After adding column you can set default value - it applied only for future inserts:
ALTER TABLE clusters ALTER COLUMN "is_paid" SET DEFAULT FALSE;
And then you can update all old rows in table by small chunks. Finally, when here is no NULL values you can set not null:
ALTER TABLE clusters ALTER COLUMN "is_paid" SET NOT NULL;
But unfortunately this locks table for some time - smaller what rewrite time, but time of full seqscan. I hope my patch [1] will be merged and not null can be set in future by temporary adding check constraint (not valid, then validate) - which not require large table lock
Regards, Sergei
From | Date | Subject | |
---|---|---|---|
Next Message | Timokhin Maxim | 2018-01-09 16:31:17 | Re: Updating a large table |
Previous Message | Timokhin Maxim | 2018-01-09 12:18:48 | Re: Updating a large table |