On Sat, Jan 22, 2022 at 10:28 AM David G. Johnston
<david.g.johns...@gmail.com> wrote:
>
>
>
> On Saturday, January 22, 2022, James Coleman <jtc...@gmail.com> wrote:
>>
>> On Sat, Jan 22, 2022 at 12:35 AM David G. Johnston
>> <david.g.johns...@gmail.com> wrote:
>> >
>> > On Fri, Jan 21, 2022 at 5:14 PM James Coleman <jtc...@gmail.com> wrote:
>> >>
>> >>
>> >> > Really? That's horrid, because that's directly useful advice.
>> >>
>> >> Remedied, but rewritten a bit to better fit with the new style/goal of
>> >> that tip).
>> >>
>> >> Version 3 is attached.
>> >>
>> >
>> > Coming back to this after a respite I think the tip needs to be moved just
>> > like everything else. For much the same reason (though this may only be a
>> > personal bias), I know what SQL Commands do the various things that DDL
>> > encompasses (especially the basics like adding a column) and so the DDL
>> > section is really just a tutorial-like chapter that I will generally
>> > forget about because I will go straight to the official source which is
>> > the SQL Command Reference. My future self would want the tip to show up
>> > there. If we put the tip after the existing paragraph that starts:
>> > "Adding a column with a volatile DEFAULT or changing the type of an
>> > existing column..." the need to specify an example function in the tip
>> > goes away - though maybe it should be moved to the notes paragraph
>> > instead: "with a volatile DEFAULT (e.g., clock_timestamp()) or changing
>> > the type of an existing column..."
>>
>> In my mind that actually might be a reason to keep it that way. I
>> expect someone who's somewhat experienced to know there are things
>> (like table rewrites and scans) you need to consider and therefore go
>> to the ALTER TABLE page and read the details. But for someone newer
>> the tutorial page needs to introduce them to the idea that those
>> gotchas exist.
>>
>
> Readers of the DDL page are given a hint of the issues and directed to
> additional, arguably mandatory, reading. They can not worry about the
> nuances during their learning phase but instead can defer that reading until
> they actually have need to alter a (large) table. But expecting them to read
> the command reference page is reasonable and is IMO the more probable place
> they will look when they start doing stuff in earnest. For the inexperienced
> reader breaking this up in this manner based upon depth of detail feels right
> to me.
Here's a version that looks like that. I'm not convinced it's an
improvement over the previous version: again, I expect more advanced
users to already understand this concept, and I think moving it to the
ALTER TABLE page could very well have the effect of burying i(amidst
the ton of detail on the ALTER TABLE page) concept that would be
useful to learn early on in a tutorial like the DDL page. But if
people really think this is an improvement, then I can acquiesce.
Thanks,
James Coleman
From ffca825ca27cffc70c7eb39385545a76fa0d9e2d Mon Sep 17 00:00:00 2001
From: James Coleman <jtc...@gmail.com>
Date: Fri, 24 Sep 2021 09:59:27 -0400
Subject: [PATCH v4 1/2] Document atthasmissing default avoids verification
table scan
When PG11 added the ability for ALTER TABLE ADD COLUMN to set a constant
default value without rewriting the table the doc changes did not note
how the new feature interplayed with ADD COLUMN DEFAULT NOT NULL.
Since adding a NOT NULL constraint requires a verification table scan to
ensure no values are null, users want to know that the combined
operation also avoids the table scan.
---
doc/src/sgml/ref/alter_table.sgml | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a76e2e7322..1dde16fa39 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1355,7 +1355,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
evaluated at the time of the statement and the result stored in the
table's metadata. That value will be used for the column for all existing
rows. If no <literal>DEFAULT</literal> is specified, NULL is used. In
- neither case is a rewrite of the table required.
+ neither case is a rewrite of the table required. A <literal>NOT NULL</literal>
+ constraint may be added to the new column in the same statement without
+ requiring scanning the table to verify the constraint.
</para>
<para>
--
2.17.1
From b1019154c749991a3e23cd8e5f82b31acbbdddc9 Mon Sep 17 00:00:00 2001
From: jcoleman <jtc...@gmail.com>
Date: Fri, 21 Jan 2022 18:50:39 +0000
Subject: [PATCH v4 2/2] Don't double document ADD COLUMN optimization details
Instead point people to the source of truth. This also avoids using
different language ("constant" versus "non-volatile").
---
doc/src/sgml/ddl.sgml | 22 ++++------------------
doc/src/sgml/ref/alter_table.sgml | 11 ++++++++++-
2 files changed, 14 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 22f6c5c7ab..0ec1b7cd39 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1406,24 +1406,10 @@ ALTER TABLE products ADD COLUMN description text;
<tip>
<para>
- From <productname>PostgreSQL</productname> 11, adding a column with
- a constant default value no longer means that each row of the table
- needs to be updated when the <command>ALTER TABLE</command> statement
- is executed. Instead, the default value will be returned the next time
- the row is accessed, and applied when the table is rewritten, making
- the <command>ALTER TABLE</command> very fast even on large tables.
- </para>
-
- <para>
- However, 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
- <command>ALTER TABLE</command> is executed. To avoid a potentially
- lengthy update operation, particularly if you intend to fill the column
- with mostly nondefault values anyway, it may be preferable to add the
- column with no default, insert the correct values using
- <command>UPDATE</command>, and then add any desired default as described
- below.
+ Adding a new column can require rewriting the whole table,
+ making it slow for large tables. However, the rewrite can be optimized
+ away in some cases, depending on what default value is given to the
+ column. See Notes on <xref linkend="sql-altertable"/> for details.
</para>
</tip>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1dde16fa39..9d1f6f1130 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1361,7 +1361,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Adding a column with a volatile <literal>DEFAULT</literal> or
+ Adding a column with a volatile <literal>DEFAULT</literal> (e.g.,
+ <function>clock_timestamp()</function>) 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</literal> clause does not change
@@ -1373,6 +1374,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
as much as double the disk space.
</para>
+ <para>
+ When adding a column with a default requires a rewrite it may be preferable
+ to add the column with no default, insert the correct values using
+ <command>UPDATE</command>, and then add any desired default as described
+ below. This is particularly true if you intend to fill the column
+ with mostly nondefault values anyway.
+ </para>
+
<para>
Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal> constraint requires
scanning the table to verify that existing rows meet the constraint,
--
2.17.1
