From: Sergei Kornilov <sk@zsrv.org>
To: Timokhin Maxim <ncx2@yandex.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Updating a large table
Date: Tue, 09 Jan 2018 15:53:44 +0300
Message-ID: <1037971515502424@web26o.yandex.ru> (raw)
In-Reply-To: <4917721515500328@web47j.yandex.ru>
References: <145941513961163@web46o.yandex.ru>
<59776cbc-bd4a-7ce9-133c-be5665075429@2ndquadrant.com>
<4917721515500328@web47j.yandex.ru>
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
[1] https://www.postgresql.org/message-id/flat/81911511895540@web58j.yandex.ru#81911511895540@web58j.yan...
Regards, Sergei
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sk@zsrv.org, ncx2@yandex.com, tomas.vondra@2ndquadrant.com, pgsql-performance@lists.postgresql.org
Subject: Re: Updating a large table
In-Reply-To: <1037971515502424@web26o.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox