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

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

Amit Langote <amitlangote09@gmail.com> writes:
> 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).

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

On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Langote <amitlangote09@gmail.com> writes:
>> 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).
>
> Try it without the explicit DEFAULT clause.
>

Thanks, that does the trick.

> 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)?

--
Amit

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.

            regards, tom lane

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

Agreed.

--
Amit

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