Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL - Mailing list pgsql-docs
From | Amit Langote |
---|---|
Subject | Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL |
Date | |
Msg-id | CA+HiwqE6-MorgpOews-9z7TLDxT1McgQC67ZtQVGQatjSQmciw@mail.gmail.com Whole thread Raw |
In response to | Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-docs |
On Fri, Apr 4, 2014 at 1:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Amit Langote <amitlangote09@gmail.com> writes: >> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> We could just rephrase the ALTER TABLE docs to say that the table >>> rewrite is avoided if you omit the DEFAULT clause, rather than >>> saying that a null default works. > >> How does the attached sound? >> Wonder if a rewrite-warning is necessary? > > I had in mind more like the attached. > > This is still not the full truth, as for example this case must do > a rewrite: > > regression=# create domain dnn as int check(value is not null); > CREATE DOMAIN > regression=# create table foo1 (f1 int); > CREATE TABLE > regression=# insert into foo1 values(42); > INSERT 0 1 > regression=# alter table foo1 add column ff dnn; > ERROR: value for domain dnn violates check constraint "dnn_check" > > But I think we can avoid getting into such complexities here. > > regards, tom lane > > > diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml > index 4847d66..f0a8b86 100644 > *** a/doc/src/sgml/ref/alter_table.sgml > --- b/doc/src/sgml/ref/alter_table.sgml > *************** ALTER TABLE [ IF EXISTS ] <replaceable c > *** 854,867 **** > When a column is added with <literal>ADD COLUMN</literal>, all existing > rows in the table are initialized with the column's default value > (NULL if no <literal>DEFAULT</> clause is specified). > </para> > > <para> > ! Adding a column with a non-null default or changing the type of an > ! existing column will require the entire table and indexes to be rewritten. > ! As an exception, if the <literal>USING</> clause does not change the column > contents and the old type is either binary coercible to the new type or > ! an unconstrained domain over the new type, a table rewrite is not needed, > but any indexes on the affected columns must still be rebuilt. Adding or > removing a system <literal>oid</> column also requires rewriting the entire > table. Table and/or index rebuilds may take a significant amount of time > --- 854,871 ---- > When a column is added with <literal>ADD COLUMN</literal>, all existing > rows in the table are initialized with the column's default value > (NULL if no <literal>DEFAULT</> clause is specified). > + If there is no <literal>DEFAULT</> clause, this is merely a metadata > + change and does not require any immediate update of the table's data; > + the added NULL values are supplied on readout, instead. > </para> > > <para> > ! Adding a column with a <literal>DEFAULT</> clause or changing the type of > ! an existing column will require the entire table and its indexes to be > ! rewritten. As an exception when changing the type of an existing column, > ! if the <literal>USING</> clause does not change the column > contents and the old type is either binary coercible to the new type or > ! an unconstrained domain over the new type, a table rewrite is not needed; > but any indexes on the affected columns must still be rebuilt. Adding or > removing a system <literal>oid</> column also requires rewriting the entire > table. Table and/or index rebuilds may take a significant amount of time Thanks for the fix. -- Amit
pgsql-docs by date: