Hello, Sergey!
09.01.2018, 15:53, "Sergei Kornilov" <sk@zsrv.org>:
> 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
youcan 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:
What you wrote are exactly I'm doing. Moreover, I'm checking current metrics to avoid previously problems.
> 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
Hope your commit will be merged. It will be realy useful.
>
> [1] https://www.postgresql.org/message-id/flat/81911511895540@web58j.yandex.ru#81911511895540@web58j.yandex.ru
>
> Regards, Sergei
--
Timokhin 'maf' Maxim