On Fri, Jan 21, 2022 at 5:38 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johns...@gmail.com> writes:
> > You've removed the "constraint verification scan" portion of this.
>
> Indeed, because that's got nothing to do with adding a new column
> (per se; adding a constraint along with the column is a different
> can of worms).
Yeah. Initially I'd thought I'd wanted it there, but by explicitly
linking people to the ALTER TABLE docs for more details (I've made
that a link now too) I'm now inclined to agree that tightly focusing
the tip is better form.
> > Re-reading this, the recommendation:
>
> > - 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.
>
> > has now been completely removed from the documentation.
>
> 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.
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 v3 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 3f119b3f67f6452ff7594d4f19d60ca17a09e19f Mon Sep 17 00:00:00 2001
From: jcoleman <jtc...@gmail.com>
Date: Fri, 21 Jan 2022 18:50:39 +0000
Subject: [PATCH v3 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 | 24 ++++++++----------------
1 file changed, 8 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 22f6c5c7ab..efd9542252 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1406,25 +1406,17 @@ 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>
+ 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 <xref linkend="sql-altertable"/> for details.
- <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
+ When a rewrite is required (e.g., a volatile default value like
+ <function>clock_timestamp()</function>) 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.
- </para>
+ below. This is particularly true if you intend to fill the column
+ with mostly nondefault values anyway.
</tip>
<para>
--
2.17.1
