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

Commit16828d5

Browse files
committed
Fast ALTER TABLE ADD COLUMN with a non-NULL default
Currently adding a column to a table with a non-NULL default results ina rewrite of the table. For large tables this can be both expensive anddisruptive. This patch removes the need for the rewrite as long as thedefault value is not volatile. The default expression is evaluated atthe time of the ALTER TABLE and the result stored in a new column(attmissingval) in pg_attribute, and a new column (atthasmissing) is setto true. Any existing row when fetched will be supplied with theattmissingval. New rows will have the supplied value or the default andso will never need the attmissingval.Any time the table is rewritten all the atthasmissing and attmissingvalsettings for the attributes are cleared, as they are no longer needed.The most visible code change from this is in heap_attisnull, whichacquires a third TupleDesc argument, allowing it to detect a missingvalue if there is one. In many cases where it is known that there willnot be any (e.g. catalog relations) NULL can be passed for thisargument.Andrew Dunstan, heavily modified from an original patch from SergeRielau.Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley.Discussion:https://postgr.es/m/31e2e921-7002-4c27-59f5-51f08404c858@2ndQuadrant.com
1 parentef1978d commit16828d5

36 files changed

+1898
-244
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1149,6 +1149,19 @@
11491149
</entry>
11501150
</row>
11511151

1152+
<row>
1153+
<entry><structfield>atthasmissing</structfield></entry>
1154+
<entry><type>bool</type></entry>
1155+
<entry></entry>
1156+
<entry>
1157+
This column has a value which is used where the column is entirely
1158+
missing from the row, as happens when a column is added with a
1159+
non-volatile <literal>DEFAULT</literal> value after the row is created.
1160+
The actual value used is stored in the
1161+
<structfield>attmissingval</structfield> column.
1162+
</entry>
1163+
</row>
1164+
11521165
<row>
11531166
<entry><structfield>attidentity</structfield></entry>
11541167
<entry><type>char</type></entry>
@@ -1229,6 +1242,20 @@
12291242
</entry>
12301243
</row>
12311244

1245+
<row>
1246+
<entry><structfield>attmissingval</structfield></entry>
1247+
<entry><type>anyarray</type></entry>
1248+
<entry></entry>
1249+
<entry>
1250+
This column has a one element array containing the value used when the
1251+
column is entirely missing from the row, as happens when the column is
1252+
added with a non-volatile <literal>DEFAULT</literal> value after the
1253+
row is created. The value is only used when
1254+
<structfield>atthasmissing</structfield> is true. If there is no value
1255+
the column is null.
1256+
</entry>
1257+
</row>
1258+
12321259
</tbody>
12331260
</tgroup>
12341261
</table>

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

Lines changed: 17 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1184,26 +1184,26 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11841184
</para>
11851185

11861186
<para>
1187-
When a column is added with <literal>ADD COLUMN</literal>, all existing
1188-
rows in the table are initialized withthecolumn'sdefaultvalue
1189-
(NULL if no <literal>DEFAULT</literal> clause is specified).
1190-
If there is no <literal>DEFAULT</literal> clause, this is merely a metadata
1191-
change and does not require any immediate update of the table's data;
1192-
the added NULL values are supplied on readout, instead.
1187+
When a column is added with <literal>ADD COLUMN</literal> and a
1188+
non-volatile <literal>DEFAULT</literal> is specified,the defaultis
1189+
evaluated at the time of the statement and the result stored in the
1190+
table's metadata. That value will be used for the column for all existing
1191+
rows. If no <literal>DEFAULT</literal> is specified, NULL is used. In
1192+
neither case is a rewrite of the table required.
11931193
</para>
11941194

11951195
<para>
1196-
Adding a column with a <literal>DEFAULT</literal>clause or changing the type of
1197-
an existing column will require the entire table and its indexes to be
1198-
rewritten.As an exception when changing the type of an existing column,
1199-
if the <literal>USING</literal> clause does not change the column
1200-
contents and the old type is either binary coercible to the new type or
1201-
an unconstrained domain over the new type, a table rewrite is not needed;
1202-
but any indexes on the affected columns must still be rebuilt. Adding or
1203-
removing a system <literal>oid</literal> column also requires rewriting the entire
1204-
table. Table and/or index rebuilds may take a significant amount of time
1205-
for a large table; and will temporarily require as much as double the disk
1206-
space.
1196+
Adding a column with avolatile<literal>DEFAULT</literal>or
1197+
changing the type ofan existing column will require the entire table and
1198+
its indexes to berewritten. As an exception, when changing the type of an
1199+
existing column,if the <literal>USING</literal> clause does not change
1200+
the columncontents and the old type is either binary coercible to the new
1201+
type oran unconstrained domain over the new type, a table rewrite is not
1202+
needed;but any indexes on the affected columns must still be rebuilt.
1203+
Adding orremoving a system <literal>oid</literal> column also requires
1204+
rewriting the entiretable. Table and/or index rebuilds may take a
1205+
significant amount of timefor a large table; and will temporarily require
1206+
as much as double the diskspace.
12071207
</para>
12081208

12091209
<para>

0 commit comments

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