Teach ALTER TABLE .. SET DATA TYPE to avoid some table rewrites.

When the old type is binary coercible to the new type and the using
clause does not change the column contents, we can avoid a full table
rewrite, though any indexes on the affected columns will still need
to be rebuilt.  This applies, for example, when changing a varchar
column to be of type text.

The prior coding assumed that the set of operations that force a
rewrite is identical to the set of operations that must be propagated
to tables making use of the affected table's rowtype.  This is
no longer true: even though the tuples in those tables wouldn't
need to be modified, the data type change invalidate indexes built
using those composite type columns.  Indexes on the table we're
actually modifying can be invalidated too, of course, but the
existing machinery is sufficient to handle that case.

Along the way, add some debugging messages that make it possible
to understand what operations ALTER TABLE is actually performing
in these cases.

Noah Misch and Robert Haas

1 files changed, 10 insertions, 6 deletions

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9f02674f44f..7e6e72f008e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -766,9 +766,13 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
    <para>
     Adding a column with a non-null default or changing the type of an
     existing column will require the entire table and indexes to be rewritten.
-    This might take a significant amount of time for a large table; and it will
-    temporarily require double the disk space.  Adding or removing a system
-    <literal>oid</> column likewise requires rewriting the entire table.
+    As an exception, if the old type type is binary coercible to the new
+    type and the <literal>USING</> clause does not change the column contents,
+    a table rewrite is not needed, but any indexes on the affected columns 
+    must still be rebuilt.  Adding or removing a system <literal>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.
    </para>
 
    <para>
@@ -797,9 +801,9 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
    <para>
     To force an immediate rewrite of the table, you can use
     <link linkend="SQL-VACUUM">VACUUM FULL</>, <xref linkend="SQL-CLUSTER">
-    or one of the forms of ALTER TABLE that forces a rewrite, such as
-    SET DATA TYPE.  This results in no semantically-visible change in the
-    table, but gets rid of no-longer-useful data.
+    or one of the forms of ALTER TABLE that forces a rewrite.  This results in
+    no semantically-visible change in the table, but gets rid of
+    no-longer-useful data.
    </para>
 
    <para>