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

---------- Forwarded message ----------
From: Amit Langote <amitlangote09@gmail.com>
Date: Thu, Apr 3, 2014 at 1:53 PM
Subject: Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN
... DEFAULT NULL
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Postgres General <pgsql-general@postgresql.org>


On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Langote <amitlangote09@gmail.com> writes:
>> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Some experimentation suggests that we are smart about "DEFAULT NULL"
>>> unless the column type requires a length-coercion cast, in which
>>> case the default expression involves a function call, and that doesn't
>>> get elided.
>
>> Is there a warning about such behavior in the manual?
>> Is it useful to include it somewhere (not sure where though)?
>
> 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?

--
Amit

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

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