From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-docs(at)postgresql(dot)org |
Subject: | Re: Fwd: [GENERAL] Table Rewrite During ALTER TABLE ... ADD COLUMN ... DEFAULT NULL |
Date: | 2014-04-03 19:34:49 |
Message-ID: | CA+HiwqE6-MorgpOews-9z7TLDxT1McgQC67ZtQVGQatjSQmciw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs pgsql-general |
On Fri, Apr 4, 2014 at 1:46 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Amit Langote <amitlangote09(at)gmail(dot)com> writes:
>> On Thu, Apr 3, 2014 at 1:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolay Samokhvalov | 2014-04-06 09:17:37 | Installation tutorial should be much more simple for newbies |
Previous Message | Antony | 2014-04-03 18:32:21 | suggestion about SEO on www.postgresql.org/docs |
From | Date | Subject | |
---|---|---|---|
Next Message | Brent Wood | 2014-04-03 19:44:27 | Re: SSD Drives |
Previous Message | Rob Sargent | 2014-04-03 19:32:20 | Re: COPY v. java performance comparison |