Thread: Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
On 2025-01-03 01:25, Robert Treat wrote:
> On Tue, Dec 3, 2024 at 3:13 AM Masahiro Ikeda
> <ikedamsh@oss.nttdata.com> wrote:
>>
>> Hi,
>>
>> The documentation seems to overlook the rewrite condition
>> when executing ALTER TABLE ADD COLUMN.
>>
>> The current document states that a volatile DEFAULT will
>> trigger a rewrite of the table and its indexes. However, the
>> table and its indexes will also be rewritten when an IDENTITY
>> column is added, or when a column with a domain data type that
>> has constraints is added.
>>
>> What do you think?
>>
>
> We still see a number of people asking (or confused) about table
> rewrites when adding columns, so I think the initial tip should
> remain, though I think it can be cleaned up a little.
>
> In the second section (alter_table.sgml) I liked the idea of adding
> these additional examples, though I tweaked the wording a bit to
> (hopefully) make it a little easier to read.
>
> Modified patch attached.
Thanks! It looks good to me with one minor comment.
Is the following intended to remove "However"? It seems that we don't
need to modify the lines if the initial tip remains.
<para>
- However, if the default value is volatile (e.g.,
- <function>clock_timestamp()</function>)
+ If the default value is volatile (e.g.,
<function>clock_timestamp()</function>)
each row will need to be updated with the value calculated at the
time
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
On Mon, Jan 6, 2025 at 3:18 AM Masahiro Ikeda <ikedamsh@oss.nttdata.com> wrote: > > On 2025-01-03 01:25, Robert Treat wrote: > > On Tue, Dec 3, 2024 at 3:13 AM Masahiro Ikeda > > <ikedamsh@oss.nttdata.com> wrote: > >> > >> Hi, > >> > >> The documentation seems to overlook the rewrite condition > >> when executing ALTER TABLE ADD COLUMN. > >> > >> The current document states that a volatile DEFAULT will > >> trigger a rewrite of the table and its indexes. However, the > >> table and its indexes will also be rewritten when an IDENTITY > >> column is added, or when a column with a domain data type that > >> has constraints is added. > >> > >> What do you think? > >> > > > > We still see a number of people asking (or confused) about table > > rewrites when adding columns, so I think the initial tip should > > remain, though I think it can be cleaned up a little. > > > > In the second section (alter_table.sgml) I liked the idea of adding > > these additional examples, though I tweaked the wording a bit to > > (hopefully) make it a little easier to read. > > > > Modified patch attached. > > Thanks! It looks good to me with one minor comment. > > Is the following intended to remove "However"? It seems that we don't > need to modify the lines if the initial tip remains. > > <para> > - However, if the default value is volatile (e.g., > - <function>clock_timestamp()</function>) > + If the default value is volatile (e.g., > <function>clock_timestamp()</function>) > each row will need to be updated with the value calculated at the > time > Technically speaking, because we split the tip into two distinct paragraphs, use of the word however would be considered poor grammar, though I'll admit I only removed it because it felt superfluous. Robert Treat https://xzilla.net
On 2025-01-07 06:27, Robert Treat wrote: > On Mon, Jan 6, 2025 at 3:18 AM Masahiro Ikeda > <ikedamsh@oss.nttdata.com> wrote: >> >> On 2025-01-03 01:25, Robert Treat wrote: >> > On Tue, Dec 3, 2024 at 3:13 AM Masahiro Ikeda >> > <ikedamsh@oss.nttdata.com> wrote: >> >> >> >> Hi, >> >> >> >> The documentation seems to overlook the rewrite condition >> >> when executing ALTER TABLE ADD COLUMN. >> >> >> >> The current document states that a volatile DEFAULT will >> >> trigger a rewrite of the table and its indexes. However, the >> >> table and its indexes will also be rewritten when an IDENTITY >> >> column is added, or when a column with a domain data type that >> >> has constraints is added. >> >> >> >> What do you think? >> >> >> > >> > We still see a number of people asking (or confused) about table >> > rewrites when adding columns, so I think the initial tip should >> > remain, though I think it can be cleaned up a little. >> > >> > In the second section (alter_table.sgml) I liked the idea of adding >> > these additional examples, though I tweaked the wording a bit to >> > (hopefully) make it a little easier to read. >> > >> > Modified patch attached. >> >> Thanks! It looks good to me with one minor comment. >> >> Is the following intended to remove "However"? It seems that we don't >> need to modify the lines if the initial tip remains. >> >> <para> >> - However, if the default value is volatile (e.g., >> - <function>clock_timestamp()</function>) >> + If the default value is volatile (e.g., >> <function>clock_timestamp()</function>) >> each row will need to be updated with the value calculated at >> the >> time >> > > Technically speaking, because we split the tip into two distinct > paragraphs, use of the word however would be considered poor grammar, > though I'll admit I only removed it because it felt superfluous. OK, thanks for your comments. Regards, -- Masahiro Ikeda NTT DATA CORPORATION
Hello,
I have pushed this patch now, with some tiny changes. (I am not a
believer of the semicolon added by commit d31e2a495b6f before the word
"and").
Also, I didn't think that changing a column type was sufficiently close
to the restrictions of column addition to belong in the same
enumeration, so the first phrase is now (note the "as will" bit at the
end):
Adding a column with a volatile <literal>DEFAULT</literal>
(e.g., <function>clock_timestamp()</function>), a generated column
(e.g., <literal>GENERATED BY DEFAULT AS IDENTITY</literal>), a domain
data type with constraints will require the entire table and its
indexes to be rewritten, as will changing the type of an existing
column.
Thank you!
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"It takes less than 2 seconds to get to 78% complete; that's a good sign.
A few seconds later it's at 90%, but it seems to have stuck there. Did
somebody make percentages logarithmic while I wasn't looking?"
http://smylers.hates-software.com/2005/09/08/1995c749.html
hi. https://git.postgresql.org/cgit/postgresql.git/commit/?id=11bd8318602fc2282a6201f714c15461dc2009c6 + Adding a column with a volatile <literal>DEFAULT</literal> + (e.g., <function>clock_timestamp()</function>), a generated column + (e.g., <literal>GENERATED BY DEFAULT AS IDENTITY</literal>), a domain + data type with constraints will require the entire table and its + indexes to be rewritten, as will changing the type of an existing + column. As an exception, when changing the type of an existing column, + if the <literal>USING</literal> 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. In the current development branch, virtual generated columns still do not support the domain. you can not change the generation expression if it contains a check constraint on it. so virtual generated columns don't need rewriting. IMHO, the committed doc description didn't mention this exception. we need some text to cover this exception?
On 2025-Mar-24, jian he wrote: > In the current development branch, > virtual generated columns still do not support the domain. > you can not change the generation expression if it contains a check > constraint on it. > so virtual generated columns don't need rewriting. > > IMHO, the committed doc description didn't mention this exception. > we need some text to cover this exception? I'd add a note about these two things to the open items page, and wait to see if we get some of these limitations fixed, so that if we don't, we remember to note this limitation in the documentation. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "All rings of power are equal, But some rings of power are more equal than others." (George Orwell's The Lord of the Rings)