Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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