pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedDoc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
14+ messages / 6 participants
[nested] [flat]
Commits from this thread:
2025-05-01 06c4f3ae80 doc: Improve explanations when a table rewrite is needed
2025-03-14 11bd831860 doc: Explain more thoroughly when a table rewrite is needed
* Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2024-12-03 08:13 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 14+ messages in thread
From: Masahiro Ikeda @ 2024-12-03 08:13 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
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?
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachments:
test.sql (text/plain, 1.6K) download
-- psql -f test.sql -e --no-psqlrc
DROP TABLE IF EXISTS test;
CREATE TABLE test (id1 int);
INSERT INTO test (SELECT generate_series(1,100));
DELETE FROM test WHERE id1 < 10;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will not rewrite'
ALTER TABLE test ADD COLUMN id2 int;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will not rewrite'
ALTER TABLE test ADD COLUMN id3 int default 100;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will not rewrite: stable proc'
ALTER TABLE test ADD COLUMN id4 timestamp DEFAULT now();
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will rewrite: volatile proc'
ALTER TABLE test ADD COLUMN id5 timestamp DEFAULT clock_timestamp();
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will rewrite: identity'
ALTER TABLE test ADD COLUMN id6 int GENERATED ALWAYS AS (id1 * 2) STORED;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will rewrite: identity'
ALTER TABLE test ADD COLUMN id7 int GENERATED BY DEFAULT AS IDENTITY;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will rewrite: domain with constraint'
DROP DOMAIN IF EXISTS test_domain;
CREATE DOMAIN test_domain int CONSTRAINT check_test CHECK (VALUE IN (1, 2, 3));
ALTER TABLE test ADD COLUMN id8 test_domain;
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
\echo 'will not rewrite: only constraint'
ALTER TABLE test ADD COLUMN id9 int CONSTRAINT check_test CHECK (id9 IN (1, 2, 3));
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
SELECT * FROM test LIMIT 10;
test.out (text/plain, 3.5K) download
DROP TABLE IF EXISTS test;
DROP TABLE
CREATE TABLE test (id1 int);
CREATE TABLE
INSERT INTO test (SELECT generate_series(1,100));
INSERT 0 100
DELETE FROM test WHERE id1 < 10;
DELETE 9
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17038
(1 row)
will not rewrite
ALTER TABLE test ADD COLUMN id2 int;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17038
(1 row)
will not rewrite
ALTER TABLE test ADD COLUMN id3 int default 100;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17038
(1 row)
will not rewrite: stable proc
ALTER TABLE test ADD COLUMN id4 timestamp DEFAULT now();
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17038
(1 row)
will rewrite: volatile proc
ALTER TABLE test ADD COLUMN id5 timestamp DEFAULT clock_timestamp();
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17044
(1 row)
will rewrite: identity
ALTER TABLE test ADD COLUMN id6 int GENERATED ALWAYS AS (id1 * 2) STORED;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17048
(1 row)
will rewrite: identity
ALTER TABLE test ADD COLUMN id7 int GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17053
(1 row)
will rewrite: domain with constraint
DROP DOMAIN IF EXISTS test_domain;
DROP DOMAIN
CREATE DOMAIN test_domain int CONSTRAINT check_test CHECK (VALUE IN (1, 2, 3));
CREATE DOMAIN
ALTER TABLE test ADD COLUMN id8 test_domain;
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17059
(1 row)
will not rewrite: only constraint
ALTER TABLE test ADD COLUMN id9 int CONSTRAINT check_test CHECK (id9 IN (1, 2, 3));
ALTER TABLE
SELECT oid, relfilenode FROM pg_class WHERE relname = 'test';
oid | relfilenode
-------+-------------
17038 | 17059
(1 row)
SELECT * FROM test LIMIT 10;
id1 | id2 | id3 | id4 | id5 | id6 | id7 | id8 | id9
-----+-----+-----+----------------------------+----------------------------+-----+-----+-----+-----
10 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261365 | 20 | 1 | |
11 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261427 | 22 | 2 | |
12 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261432 | 24 | 3 | |
13 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261434 | 26 | 4 | |
14 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261437 | 28 | 5 | |
15 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.26144 | 30 | 6 | |
16 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261443 | 32 | 7 | |
17 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261445 | 34 | 8 | |
18 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.261448 | 36 | 9 | |
19 | | 100 | 2024-12-03 15:08:24.251233 | 2024-12-03 15:08:24.26145 | 38 | 10 | |
(10 rows)
v1-0001-Doc-fix-the-rewrite-condition-when-executing-ALTE.patch (text/x-diff, 3.1K) download
From 94b79dc0f6412b1e73549c091d6561e0815d07d3 Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
Date: Tue, 3 Dec 2024 16:01:10 +0900
Subject: [PATCH v1] Doc: fix the rewrite condition when executing ALTER TABLE
ADD COLUMN
Reported-by: Tatsuhito Kasahara
Author: Masahiro Ikeda
---
doc/src/sgml/ddl.sgml | 12 +-----------
doc/src/sgml/ref/alter_table.sgml | 11 ++++++++---
2 files changed, 9 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index dea04d64db..489e4d8e71 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1623,17 +1623,7 @@ 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>)
+ 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
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c8f7ab7d95..ade4b6d3c8 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1399,12 +1399,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
non-volatile <literal>DEFAULT</literal> is specified, the default is
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.
+ rows, ensuring it will be returned the next time the row is accessed.
+ The value will be only applied when the table is rewritten, making the
+ <command>ALTER TABLE</command> very fast even on large tables. If no column
+ constraints are specified, NULL is used as the <literal>DEFAULT</literal>.
+ In neither case is a rewrite of the table required.
</para>
<para>
- Adding a column with a volatile <literal>DEFAULT</literal> or
+ Adding a column with a volatile (e.g., <function>clock_timestamp()</function>)
+ <literal>DEFAULT</literal>, a generated column (e.g., <literal>GENERATED BY DEFAULT
+ AS IDENTITY</literal>), a domain data type with constraints, 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
--
2.34.1
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-01-02 16:25 Robert Treat <rob@xzilla.net>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Treat @ 2025-01-02 16:25 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; +Cc: pgsql-hackers@lists.postgresql.org
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.
Robert Treat
https://xzilla.net
Attachments:
v2-0001-Doc-fix-the-rewrite-condition-when-executing-ALTE.patch (application/octet-stream, 4.9K) download
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index dea04d64db..e2f5a683f2 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1623,17 +1623,15 @@ 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
+ Adding a column with a constant default value does not require each row of
+ the table 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>)
+ 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
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c8f7ab7d95..1ececc3aab 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1396,30 +1396,35 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
When a column is added with <literal>ADD COLUMN</literal> and a
- non-volatile <literal>DEFAULT</literal> is specified, the default is
+ non-volatile <literal>DEFAULT</literal> is specified, the default value is
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.
+ table's metadata, where it will be returned when any existing rows are
+ accessed. The value will be only applied when the table is rewritten,
+ making the <command>ALTER TABLE</command> very fast even on large tables.
+ If no column constraints are specified, NULL is used as the
+ <literal>DEFAULT</literal>. In neither case is a rewrite of the table
+ required.
</para>
<para>
- Adding a column with a volatile <literal>DEFAULT</literal> 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
- 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 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 always 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 rebuilds may take a
- significant amount of time for a large table; and will temporarily require
- as much as double the disk space.
+ 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, 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 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 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 always 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 rebuilds may take a significant amount
+ of time for a large table; and will temporarily require as much as double
+ the disk space.
</para>
<para>
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-01-06 08:18 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 14+ messages in thread
From: Masahiro Ikeda @ 2025-01-06 08:18 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-01-06 21:27 Robert Treat <rob@xzilla.net>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Treat @ 2025-01-06 21:27 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; +Cc: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-01-07 02:29 Masahiro Ikeda <ikedamsh@oss.nttdata.com>
parent: Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 14+ messages in thread
From: Masahiro Ikeda @ 2025-01-07 02:29 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-03-14 19:49 Álvaro Herrera <alvherre@alvh.no-ip.org>
parent: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2025-03-14 19:49 UTC (permalink / raw)
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; +Cc: Robert Treat <rob@xzilla.net>; pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-03-24 01:43 jian he <jian.universality@gmail.com>
parent: Álvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 14+ messages in thread
From: jian he @ 2025-03-24 01:43 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; Robert Treat <rob@xzilla.net>; pgsql-hackers@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-03-24 10:37 Álvaro Herrera <alvherre@alvh.no-ip.org>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2025-03-24 10:37 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Masahiro Ikeda <ikedamsh@oss.nttdata.com>; Robert Treat <rob@xzilla.net>; pgsql-hackers@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-04-23 16:28 Nathan Bossart <nathandbossart@gmail.com>
parent: Álvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 14+ messages in thread
From: Nathan Bossart @ 2025-04-23 16:28 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: jian he <jian.universality@gmail.com>; Masahiro Ikeda <ikedamsh@oss.nttdata.com>; Robert Treat <rob@xzilla.net>; pgsql-hackers@lists.postgresql.org
This one was briefly discussed in an RMT meeting.
On Mon, Mar 24, 2025 at 11:37:20AM +0100, Álvaro Herrera wrote:
> 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.
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.
--
nathan
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-04-28 16:56 Álvaro Herrera <alvherre@alvh.no-ip.org>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2025-04-28 16:56 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Masahiro Ikeda <ikedamsh@oss.nttdata.com>; Robert Treat <rob@xzilla.net>; pgsql-hackers@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
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.
Thanks,
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"This is a foot just waiting to be shot" (Andrew Dunstan)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-04-30 09:15 Peter Eisentraut <peter@eisentraut.org>
parent: Álvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2025-04-30 09:15 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@alvh.no-ip.org>; Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Masahiro Ikeda <ikedamsh@oss.nttdata.com>; Robert Treat <rob@xzilla.net>; pgsql-hackers@lists.postgresql.org
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.
Attachments:
0001-doc-Improve-explanations-when-a-table-rewrite-is-nee.patch (text/plain, 2.9K) download
From 33fb59c94ae3dbf6367e36c79f71dc9e291423d8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@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/00e6eb5f5c793b8ef722252c7a519c9a@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
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-04-30 15:17 Robert Treat <rob@xzilla.net>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Treat @ 2025-04-30 15:17 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Nathan Bossart <nathandbossart@gmail.com>; jian he <jian.universality@gmail.com>; Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-hackers@lists.postgresql.org
On Wed, Apr 30, 2025 at 5:15 AM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> 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.
If I were going to quibble, I'd probably rewrite the second paragraph as
+ Changing the type of an existing column will normally 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 will still need to be 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.
But otherwise this LGTM.
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-05-01 07:22 Peter Eisentraut <peter@eisentraut.org>
parent: Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2025-05-01 07:22 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Nathan Bossart <nathandbossart@gmail.com>; jian he <jian.universality@gmail.com>; Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-hackers@lists.postgresql.org
On 30.04.25 17:17, Robert Treat wrote:
> On Wed, Apr 30, 2025 at 5:15 AM Peter Eisentraut <peter@eisentraut.org> wrote:
>>
>> 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.
>
> If I were going to quibble, I'd probably rewrite the second paragraph as
>
> + Changing the type of an existing column will normally 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 will still need to be 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.
>
> But otherwise this LGTM.
Thanks, I have worked this into the commit. I wanted to get away from
phrases like "needs to be rebuilt" or "must be rebuilt" because that
possibly sounds like you have to do it manually. So I tweaked it a bit
further, but I think I captured the essence.
I'm closing the open item.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
@ 2025-05-01 15:04 Robert Treat <rob@xzilla.net>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 0 replies; 14+ messages in thread
From: Robert Treat @ 2025-05-01 15:04 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Nathan Bossart <nathandbossart@gmail.com>; jian he <jian.universality@gmail.com>; Masahiro Ikeda <ikedamsh@oss.nttdata.com>; pgsql-hackers@lists.postgresql.org
On Thu, May 1, 2025 at 3:22 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> On 30.04.25 17:17, Robert Treat wrote:
> > But otherwise this LGTM.
>
> Thanks, I have worked this into the commit. I wanted to get away from
> phrases like "needs to be rebuilt" or "must be rebuilt" because that
> possibly sounds like you have to do it manually. So I tweaked it a bit
> further, but I think I captured the essence.
>
Yeah, that was my thinking as well; commit looks good.
> I'm closing the open item.
>
Thanks Peter.
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-05-01 15:04 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-03 08:13 Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2025-01-02 16:25 ` Robert Treat <rob@xzilla.net>
2025-01-06 08:18 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2025-01-06 21:27 ` Robert Treat <rob@xzilla.net>
2025-01-07 02:29 ` Masahiro Ikeda <ikedamsh@oss.nttdata.com>
2025-03-14 19:49 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-03-24 01:43 ` jian he <jian.universality@gmail.com>
2025-03-24 10:37 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-04-23 16:28 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-28 16:56 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-04-30 09:15 ` Peter Eisentraut <peter@eisentraut.org>
2025-04-30 15:17 ` Robert Treat <rob@xzilla.net>
2025-05-01 07:22 ` Peter Eisentraut <peter@eisentraut.org>
2025-05-01 15:04 ` Robert Treat <rob@xzilla.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox