Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-docs-owner+M7955=pgsql+2Ddocs=arkaria.postgresql.org@postgresql.org>)
 id 1WVkmc-0005xQ-Ve
 for pgsql-docs@arkaria.postgresql.org; Thu, 03 Apr 2014 16:46:31 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-docs-owner+M7955=pgsql+2Ddocs=arkaria.postgresql.org@postgresql.org>)
 id 1WVkmc-0004Z0-Fb
 for pgsql-docs@arkaria.postgresql.org; Thu, 03 Apr 2014 16:46:30 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <tgl@sss.pgh.pa.us>) id 1WVkmb-0004Yu-VE
 for pgsql-docs@postgresql.org; Thu, 03 Apr 2014 16:46:30 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <tgl@sss.pgh.pa.us>) id 1WVkmZ-0000Mj-E8
 for pgsql-docs@postgresql.org; Thu, 03 Apr 2014 16:46:29 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
 by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id s33GkO2O029416;
 Thu, 3 Apr 2014 12:46:24 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Amit Langote <amitlangote09@gmail.com>
cc: pgsql-docs@postgresql.org
Subject: Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN
 ... DEFAULT NULL
In-reply-to: 
 <CA+HiwqHyY6fKuGjHTtF_HesnbJs=muu9chmNqzoa3MLaRv_KbQ@mail.gmail.com>
References: 
 <CA+HiwqE35_+z0_gFw9mp=_0MLgPfb=+QY910LG7C-sBNTvqdgg@mail.gmail.com>
 <3036.1396497272@sss.pgh.pa.us>
 <CA+HiwqGzakcVbKbfxv6ThNcAmp_ZgakagKTM1u80PAF2mnNVcQ@mail.gmail.com>
 <3704.1396498776@sss.pgh.pa.us>
 <CA+HiwqHvoM4RpYpGQdLXSQziGZhR1KmR9skTr03MMRJFdqXuJg@mail.gmail.com>
 <CA+HiwqHyY6fKuGjHTtF_HesnbJs=muu9chmNqzoa3MLaRv_KbQ@mail.gmail.com>
Comments: In-reply-to Amit Langote <amitlangote09@gmail.com>
 message dated "Thu, 03 Apr 2014 14:02:41 +0900"
Date: Thu, 03 Apr 2014 12:46:24 -0400
Message-ID: <29415.1396543584@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.5 (--)
List-Archive: <http://archives.postgresql.org/pgsql-docs>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-docs.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@postgresql.org>
List-Post: <mailto:pgsql-docs@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-docs>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
X-Mailing-List: pgsql-docs
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

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


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs