Documentation fix for adding a column with a default value
Hi,
the tip in the "Adding a column" section is not true anymore since PostgreSQL 11:
https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN
Attached a patch proposal for this.
Regards
Daniel
Attachments:
docfix.patchtext/x-patch; name=docfix.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9301f0227d..62058ec3b1 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1318,12 +1318,7 @@ ALTER TABLE products ADD COLUMN description text CHECK (description <> '')
<tip>
<para>
Adding a column with a default requires updating each row of the
- table (to store the new column value). However, if no default is
- specified, <productname>PostgreSQL</productname> is able to avoid
- the physical update. So if you intend to fill the column with
- mostly nondefault values, it's best to add the column with no default,
- insert the correct values using <command>UPDATE</command>, and then add any
- desired default as described below.
+ table (to store the new column value) if the default is volatile.
</para>
</tip>
</sect2>
______________________________________
From: Daniel Westermann (DWE)
Sent: Monday, July 15, 2019 13:01
To: pgsql-hackers@postgresql.org
Subject: Documentation fix for adding a column with a default valueHi,
the tip in the "Adding a column" section is not true anymore since PostgreSQL 11:
Attached a patch proposal for this.
Seems the first mail didn't make it ...
Regards
Daniel
Attachments:
docfix.patchtext/x-patch; name=docfix.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9301f0227d..62058ec3b1 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1318,12 +1318,7 @@ ALTER TABLE products ADD COLUMN description text CHECK (description <> '')
<tip>
<para>
Adding a column with a default requires updating each row of the
- table (to store the new column value). However, if no default is
- specified, <productname>PostgreSQL</productname> is able to avoid
- the physical update. So if you intend to fill the column with
- mostly nondefault values, it's best to add the column with no default,
- insert the correct values using <command>UPDATE</command>, and then add any
- desired default as described below.
+ table (to store the new column value) if the default is volatile.
</para>
</tip>
</sect2>
On Wed, 17 Jul 2019 at 15:42, Daniel Westermann (DWE) <
daniel.westermann@dbi-services.com> wrote:
______________________________________
From: Daniel Westermann (DWE)
Sent: Monday, July 15, 2019 13:01
To: pgsql-hackers@postgresql.org
Subject: Documentation fix for adding a column with a default valueHi,
the tip in the "Adding a column" section is not true anymore since
PostgreSQL 11:
https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN
<https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN>
Attached a patch proposal for this.
Seems the first mail didn't make it ...
Actually it did, I was about to reply to it :)
The suggested change pares down the "Tip" to more of a brief "Note", which
IMHO is a bit
terse for that section of the documentation (which has more of a tutorial
character),
and the contents of the original tip basically still apply for volatile
default values
anyway.
I've attached another suggestion for rewording this which should also make
the
mechanics of the operation a little clearer.
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
doc-alter-table-default-value-pg11.v1.patchapplication/octet-stream; name=doc-alter-table-default-value-pg11.v1.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
new file mode 100644
index 5ae3cac..95a326b
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*************** ALTER TABLE products ADD COLUMN descript
*** 1189,1194 ****
--- 1189,1214 ----
value is given (null if you don't specify a <literal>DEFAULT</literal> clause).
</para>
+ <tip>
+ <para>
+ From <productname>PostgreSQL 11</productname>, 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 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 calulated 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 UPDATE, and then add any desired default as
+ described below.
+ </para>
+ </tip>
+
<para>
You can also define constraints on the column at the same time,
using the usual syntax:
*************** ALTER TABLE products ADD COLUMN descript
*** 1203,1219 ****
correctly.
</para>
- <tip>
- <para>
- Adding a column with a default requires updating each row of the
- table (to store the new column value). However, if no default is
- specified, <productname>PostgreSQL</productname> is able to avoid
- the physical update. So if you intend to fill the column with
- mostly nondefault values, it's best 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>
- </tip>
</sect2>
<sect2 id="ddl-alter-removing-a-column">
--- 1223,1228 ----
Seems the first mail didn't make it ...
Actually it did, I was about to reply to it :)
The suggested change pares down the "Tip" to more of a brief "Note", which IMHO is a bit
terse for that section of the documentation (which has more of a tutorial character),
and the contents of the original tip basically still apply for volatile default values
anyway.I've attached another suggestion for rewording this which should also make the
mechanics of the operation a little clearer.
Thank you, that better explains it. Looks good to me.
Regards
Daniel
The suggested change pares down the "Tip" to more of a brief "Note", which IMHO is a bit
terse for that section of the documentation (which has more of a tutorial character),
and the contents of the original tip basically still apply for volatile default values
anyway.I've attached another suggestion for rewording this which should also make the
mechanics of the operation a little clearer.
Thank you, that better explains it. Looks good to me.
Shouldn't we add that to the current commit fest?
Regards
Daniel
On 18 Jul 2019, at 17:46, Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> wrote:
The suggested change pares down the "Tip" to more of a brief "Note", which IMHO is a bit
terse for that section of the documentation (which has more of a tutorial character),
and the contents of the original tip basically still apply for volatile default values
anyway.I've attached another suggestion for rewording this which should also make the
mechanics of the operation a little clearer.Thank you, that better explains it. Looks good to me.
Shouldn't we add that to the current commit fest?
The current commitfest is closed for new additions, but please add it to the
next one (2019-09) and it will be picked up then.
cheers ./daniel
On 7/19/19 12:51 AM, Daniel Gustafsson wrote:
On 18 Jul 2019, at 17:46, Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> wrote:
The suggested change pares down the "Tip" to more of a brief "Note", which IMHO is a bit
terse for that section of the documentation (which has more of a tutorial character),
and the contents of the original tip basically still apply for volatile default values
anyway.I've attached another suggestion for rewording this which should also make the
mechanics of the operation a little clearer.Thank you, that better explains it. Looks good to me.
Shouldn't we add that to the current commit fest?
The current commitfest is closed for new additions, but please add it to the
next one (2019-09) and it will be picked up then.
To me it looks like a minor documentation correction to fix an omission
from a patch already in PostgreSQL.
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Jul 19, 2019 at 09:04:03AM +0900, Ian Barwick wrote:
To me it looks like a minor documentation correction to fix an omission
from a patch already in PostgreSQL.
I think that it is better to register it in the commit fest anyway so
as we don't lose track of it. Things tend to get lost easily as this
list has a lot of traffic.
I have been looking at the original patch from Daniel and got
surprised by the simple removal of the paragraph as this applies to
16828d5c where using volatile defaults still require a table rewrite.
Well, this just comes back to the point raised by Ian upthread ;p
Except for a couple of misplaced and missing markups and one typo, the
new paragraph looked fine, so committed down to v11 after fixing the
whole.
--
Michael