Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit11bd831

Browse files
committed
doc: Explain more thoroughly when a table rewrite is needed
Author: Masahiro Ikeda <ikedamsh@oss.nttdata.com>Reviewed-by: Robert Treat <rob@xzilla.net>Discussion:https://postgr.es/m/00e6eb5f5c793b8ef722252c7a519c9a@oss.nttdata.com
1 parent1c9242b commit11bd831

File tree

2 files changed

+29
-25
lines changed

2 files changed

+29
-25
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1650,17 +1650,15 @@ ALTER TABLE products ADD COLUMN description text;
16501650

16511651
<tip>
16521652
<para>
1653-
From <productname>PostgreSQL</productname> 11, adding a column with
1654-
a constant default value no longer means that each row of the table
1655-
needs to be updated when the <command>ALTER TABLE</command> statement
1653+
Adding a column with a constant default value does not require each row of
1654+
the table to be updated when the <command>ALTER TABLE</command> statement
16561655
is executed. Instead, the default value will be returned the next time
16571656
the row is accessed, and applied when the table is rewritten, making
16581657
the <command>ALTER TABLE</command> very fast even on large tables.
16591658
</para>
16601659

16611660
<para>
1662-
However, if the default value is volatile (e.g.,
1663-
<function>clock_timestamp()</function>)
1661+
If the default value is volatile (e.g., <function>clock_timestamp()</function>)
16641662
each row will need to be updated with the value calculated at the time
16651663
<command>ALTER TABLE</command> is executed. To avoid a potentially
16661664
lengthy update operation, particularly if you intend to fill the column

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 26 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1421,30 +1421,36 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14211421

14221422
<para>
14231423
When a column is added with <literal>ADD COLUMN</literal> and a
1424-
non-volatile <literal>DEFAULT</literal> is specified, the default is
1424+
non-volatile <literal>DEFAULT</literal> is specified, the defaultvalueis
14251425
evaluated at the time of the statement and the result stored in the
1426-
table's metadata. That value will be used for the column for all existing
1427-
rows. If no <literal>DEFAULT</literal> is specified, NULL is used. In
1428-
neither case is a rewrite of the table required.
1426+
table's metadata, where it will be returned when any existing rows are
1427+
accessed. The value will be only applied when the table is rewritten,
1428+
making the <command>ALTER TABLE</command> very fast even on large tables.
1429+
If no column constraints are specified, NULL is used as the
1430+
<literal>DEFAULT</literal>. In neither case is a rewrite of the table
1431+
required.
14291432
</para>
14301433

14311434
<para>
1432-
Adding a column with a volatile <literal>DEFAULT</literal> or
1433-
changing the type of an existing column will require the entire table and
1434-
its indexes to be rewritten. As an exception, when changing the type of an
1435-
existing column, if the <literal>USING</literal> clause does not change
1436-
the column contents and the old type is either binary coercible to the new
1437-
type or an unconstrained domain over the new type, a table rewrite is not
1438-
needed. However, indexes must always be rebuilt unless the system can
1439-
verify that the new index would be logically equivalent to the existing
1440-
one. For example, if the collation for a column has been changed, an index
1441-
rebuild is always required because the new sort order might be different.
1442-
However, in the absence of a collation change, a column can be changed
1443-
from <type>text</type> to <type>varchar</type> (or vice versa) without
1444-
rebuilding the indexes because these data types sort identically.
1445-
Table and/or index rebuilds may take a
1446-
significant amount of time for a large table; and will temporarily require
1447-
as much as double the disk space.
1435+
Adding a column with a volatile <literal>DEFAULT</literal>
1436+
(e.g., <function>clock_timestamp()</function>), a generated column
1437+
(e.g., <literal>GENERATED BY DEFAULT AS IDENTITY</literal>), a domain
1438+
data type with constraints will require the entire table and its
1439+
indexes to be rewritten, as will changing the type of an existing
1440+
column. As an exception, when changing the type of an existing column,
1441+
if the <literal>USING</literal> clause does not change the column
1442+
contents and the old type is either binary coercible to the new type
1443+
or an unconstrained domain over the new type, a table rewrite is not
1444+
needed. However, indexes must always be rebuilt unless the system
1445+
can verify that the new index would be logically equivalent to the
1446+
existing one. For example, if the collation for a column has been
1447+
changed, an index rebuild is required because the new sort
1448+
order might be different. However, in the absence of a collation
1449+
change, a column can be changed from <type>text</type> to
1450+
<type>varchar</type> (or vice versa) without rebuilding the indexes
1451+
because these data types sort identically. Table and/or index
1452+
rebuilds may take a significant amount of time for a large table,
1453+
and will temporarily require as much as double the disk space.
14481454
</para>
14491455

14501456
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp