public inbox for [email protected]help / color / mirror / Atom feed
Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL 9+ messages / 2 participants [nested] [flat]
* Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 03:31 Amit Langote <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Amit Langote @ 2014-04-03 03:31 UTC (permalink / raw) To: Postgres General <[email protected]> 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 protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 03:54 Tom Lane <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tom Lane @ 2014-04-03 03:54 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Postgres General <[email protected]> Amit Langote <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 04:12 Amit Langote <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Amit Langote @ 2014-04-03 04:12 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Postgres General <[email protected]> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <[email protected]> wrote: > Amit Langote <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 04:19 Tom Lane <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: Tom Lane @ 2014-04-03 04:19 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Postgres General <[email protected]> Amit Langote <[email protected]> writes: > On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 04:26 Amit Langote <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: Amit Langote @ 2014-04-03 04:26 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Postgres General <[email protected]> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <[email protected]> wrote: > Amit Langote <[email protected]> writes: >> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 04:53 Amit Langote <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: Amit Langote @ 2014-04-03 04:53 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Postgres General <[email protected]> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <[email protected]> wrote: > Amit Langote <[email protected]> writes: >> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <[email protected]> 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 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Attachments: [application/octet-stream] alter-table-doc-fix.patch (719B, 2-alter-table-doc-fix.patch) download | inline diff: diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 4847d66..de63e9e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -853,7 +853,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> <para> 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). + (NULL if no <literal>DEFAULT</> clause is specified). Users are advised + not to use explicit <literal>DEFAULT</> clause unless there is a non-null + default value to specify. </para> <para> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 05:02 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Amit Langote @ 2014-04-03 05:02 UTC (permalink / raw) To: pgsql-docs ---------- Forwarded message ---------- From: Amit Langote <[email protected]> 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 protected]> Cc: Postgres General <[email protected]> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <[email protected]> wrote: > Amit Langote <[email protected]> writes: >> On Thu, Apr 3, 2014 at 12:54 PM, Tom Lane <[email protected]> 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 -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs Attachments: [application/octet-stream] alter-table-doc-fix.patch (719B, 2-alter-table-doc-fix.patch) download | inline diff: diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 4847d66..de63e9e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -853,7 +853,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> <para> 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). + (NULL if no <literal>DEFAULT</> clause is specified). Users are advised + not to use explicit <literal>DEFAULT</> clause unless there is a non-null + default value to specify. </para> <para> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 16:46 Tom Lane <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tom Lane @ 2014-04-03 16:46 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: pgsql-docs Amit Langote <[email protected]> writes: > On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL @ 2014-04-03 19:34 Amit Langote <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Amit Langote @ 2014-04-03 19:34 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs On Fri, Apr 4, 2014 at 1:46 AM, Tom Lane <[email protected]> wrote: > Amit Langote <[email protected]> writes: >> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-04-03 19:34 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-04-03 03:31 Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL Amit Langote <[email protected]> 2014-04-03 03:54 ` Tom Lane <[email protected]> 2014-04-03 04:12 ` Amit Langote <[email protected]> 2014-04-03 04:19 ` Tom Lane <[email protected]> 2014-04-03 04:26 ` Amit Langote <[email protected]> 2014-04-03 04:53 ` Amit Langote <[email protected]> 2014-04-03 05:02 ` Amit Langote <[email protected]> 2014-04-03 16:46 ` Tom Lane <[email protected]> 2014-04-03 19:34 ` Amit Langote <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox