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
-- 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;
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)
From 94b79dc0f6412b1e73549c091d6561e0815d07d3 Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <ikeda...@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
