Subject: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL

From: Amit Langote <[email-masked]>
Date: Thu, 3 Apr 2014 12:31:51 +0900

Hi,

When I do the following:

ALTER TABLE table ADD COLUMN numeric(x) DEFAULT NULL;

The table is rewritten whereas notes section on the manual page for
ALTER TABLE says otherwise (which holds true for most of the cases
though).

http://www.postgresql.org/docs/devel/static/sql-altertable.html

As an example,

postgres=# create table test as select generate_series(1,1000000) as a;
SELECT 1000000

postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16709
(1 row)

postgres=# alter table test add column b numeric(2) DEFAULT NULL;
ALTER TABLE

-- rewritten
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16713
(1 row)

postgres=# alter table test add column c int DEFAULT NULL;
ALTER TABLE

-- not rewritten
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16713
(1 row)

postgres=# alter table test add column d char(5) DEFAULT NULL;
ALTER TABLE

-- rewritten, again
postgres=# select oid, relname, relfilenode from pg_class where
relname = 'test';
  oid  | relname | relfilenode
-------+---------+-------------
 16709 | test    |       16717

So, when the type of the new column has type modifier like numeric(x),
char(x) etc. do,  this happens.

Is this intentional and/or documented somewhere else? If not, should
it be documented?

--
Amit

-- 
Sent via pgsql-general mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

---

From: Tom Lane <[email-masked]>
Date: Wed, 02 Apr 2014 23:54:32 -0400

> ALTER TABLE says otherwise (which holds true for most of the cases

Try it without the explicit DEFAULT clause.

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.

			regards, tom lane

---

From: Amit Langote <[email-masked]>
Date: Thu, 3 Apr 2014 13:12:18 +0900

> Try it without the explicit DEFAULT clause.

Thanks, that does the trick.

> case the default expression involves a function call, and that doesn't

Is there a warning about such behavior in the manual?
Is it useful to include it somewhere (not sure where though)?

--
Amit

---

From: Tom Lane <[email-masked]>
Date: Thu, 03 Apr 2014 00:19:36 -0400

> 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.

			regards, tom lane

---

From: Amit Langote <[email-masked]>
Date: Thu, 3 Apr 2014 13:26:45 +0900

> saying that a null default works.

Agreed.

--
Amit

---

From: Amit Langote <[email-masked]>
Date: Thu, 3 Apr 2014 13:53:43 +0900

> saying that a null default works.

How does the attached sound?
Wonder if a rewrite-warning is necessary?

--
Amit

---

From: Amit Langote <[email-masked]>
Date: Thu, 3 Apr 2014 14:02:41 +0900

---------- Forwarded message ----------
From: Amit Langote <[email-masked]>
Date: Thu, Apr 3, 2014 at 1:53 PM
Subject: Re: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN
... DEFAULT NULL
To: Tom Lane <[email-masked]>
Cc: Postgres General <[email-masked]>

--
Amit

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 03 Apr 2014 12:46:24 -0400

> 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

---

From: Amit Langote <[email-masked]>
Date: Fri, 4 Apr 2014 04:34:49 +0900

> *************** ALTER TABLE [ IF EXISTS ] <replaceable c

Thanks for the fix.

--
Amit