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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
Attachments:
alter-table-doc-fix.patchapplication/octet-stream; name=alter-table-doc-fix.patchDownload+3-1
---------- 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
Attachments:
alter-table-doc-fix.patchapplication/octet-stream; name=alter-table-doc-fix.patchDownload+3-1
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
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs