From 16828d5c0273b4fe5f10f42588005f16b415b2d8 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Wed, 28 Mar 2018 10:43:52 +1030 Subject: Fast ALTER TABLE ADD COLUMN with a non-NULL default Currently adding a column to a table with a non-NULL default results in a rewrite of the table. For large tables this can be both expensive and disruptive. This patch removes the need for the rewrite as long as the default value is not volatile. The default expression is evaluated at the time of the ALTER TABLE and the result stored in a new column (attmissingval) in pg_attribute, and a new column (atthasmissing) is set to true. Any existing row when fetched will be supplied with the attmissingval. New rows will have the supplied value or the default and so will never need the attmissingval. Any time the table is rewritten all the atthasmissing and attmissingval settings for the attributes are cleared, as they are no longer needed. The most visible code change from this is in heap_attisnull, which acquires a third TupleDesc argument, allowing it to detect a missing value if there is one. In many cases where it is known that there will not be any (e.g. catalog relations) NULL can be passed for this argument. Andrew Dunstan, heavily modified from an original patch from Serge Rielau. Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley. Discussion: https://postgr.es/m/31e2e921-7002-4c27-59f5-51f08404c858@2ndQuadrant.com --- doc/src/sgml/catalogs.sgml | 27 +++++++++++++++++++++++++++ doc/src/sgml/ref/alter_table.sgml | 34 +++++++++++++++++----------------- 2 files changed, 44 insertions(+), 17 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 95a5b113b98..d6a9d8c5808 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1149,6 +1149,19 @@ + + atthasmissing + bool + + + This column has a value which is used where the column is entirely + missing from the row, as happens when a column is added with a + non-volatile DEFAULT value after the row is created. + The actual value used is stored in the + attmissingval column. + + + attidentity char @@ -1229,6 +1242,20 @@ + + attmissingval + anyarray + + + This column has a one element array containing the value used when the + column is entirely missing from the row, as happens when the column is + added with a non-volatile DEFAULT value after the + row is created. The value is only used when + atthasmissing is true. If there is no value + the column is null. + + + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index afe213910c7..69f3355eded 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1184,26 +1184,26 @@ WITH ( MODULUS numeric_literal, REM - When a column is added with ADD COLUMN, all existing - rows in the table are initialized with the column's default value - (NULL if no DEFAULT clause is specified). - If there is no DEFAULT clause, this is merely a metadata - change and does not require any immediate update of the table's data; - the added NULL values are supplied on readout, instead. + When a column is added with ADD COLUMN and a + non-volatile DEFAULT 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 DEFAULT is specified, NULL is used. In + neither case is a rewrite of the table required. - Adding a column with a DEFAULT clause 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 USING 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; - but any indexes on the affected columns must still be rebuilt. Adding or - removing a system oid column also requires rewriting the entire - table. 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 DEFAULT 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 USING 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; but any indexes on the affected columns must still be rebuilt. + Adding or removing a system oid column also requires + rewriting the entire table. 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. -- cgit v1.2.3