On 28.04.25 18:56, Álvaro Herrera wrote:
On 2025-Apr-23, Nathan Bossart wrote:
On Mon, Mar 24, 2025 at 11:37:20AM +0100, Álvaro Herrera wrote:
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.
Are we still waiting on something for this, or should we proceed with the
documentation changes? It doesn't seem tremendously urgent, but I noticed
it's been about a month since the last message on this thread.
I've edited the Open Items page to disclaim my responsibility from this
item, since this comes from virtual generated columns which is not my
turf. I think we should just document the current state of affairs; we
can come back with further code improvements during the next cycle.
Here is a proposed patch that includes some text about virtual generated
columns and also fixes up a small mistake in the previous patch
(confused identity and generated columns) and improves the wording and
formatting a bit more.
From 33fb59c94ae3dbf6367e36c79f71dc9e291423d8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 30 Apr 2025 11:11:15 +0200
Subject: [PATCH] doc: Improve explanations when a table rewrite is needed
Further improvement for commit 11bd8318602. That commit confused
identity and generated columns; fix that. Also, virtual generated
columns have since been added; add more details about that. Also some
small rewordings and reformattings to further improve clarity.
Discussion: https://postgr.es/m/00e6eb5f5c793b8ef722252c7a519...@oss.nttdata.com
---
doc/src/sgml/ref/alter_table.sgml | 23 ++++++++++++++++-------
1 file changed, 16 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index a75e75d800d..9bf7ca1462e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1436,22 +1436,31 @@ <title>Notes</title>
<para>
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,
+ (e.g., <function>clock_timestamp()</function>), a stored generated column,
+ an identity column, or a column with a domain data type that has
+ constraints will cause the entire table and its indexes to be rewritten.
+ Adding a virtual generated column never requires a rewrite.
+ </para>
+
+ <para>
+ Changing the type of an existing column will also cause 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 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. However, indexes must always be rebuilt unless the system
+ needed. However, indexes are always rebuilt unless the system
can verify that the new index would be logically equivalent to the
existing one. For example, if the collation for a column has been
changed, an index rebuild is required because the new sort
order might be different. However, in the absence of a collation
change, a column can be changed from <type>text</type> to
<type>varchar</type> (or vice versa) without rebuilding the indexes
- because these data types sort identically. Table and/or index
+ because these data types sort identically.
+ </para>
+
+ <para>
+ Table and/or index
rebuilds may take a significant amount of time for a large table,
and will temporarily require as much as double the disk space.
</para>
--
2.49.0
