Clarify behavior of adding and altering a column in same ALTER command.

The behavior of something like

ALTER TABLE transactions
  ADD COLUMN status varchar(30) DEFAULT 'old',
  ALTER COLUMN status SET default 'current';

is to fill existing table rows with 'old', not 'current'.  That's
intentional and desirable for a couple of reasons:

* It makes the behavior the same whether you merge the sub-commands
into one ALTER command or give them separately;

* If we applied the new default while filling the table, there would
be no way to get the existing behavior in one SQL command.

The same reasoning applies in cases that add a column and then
manipulate its GENERATED/IDENTITY status in a second sub-command,
since the generation expression is really just a kind of default.
However, that wasn't very obvious (at least not to me; earlier in
the referenced discussion thread I'd thought it was a bug to be
fixed).  And it certainly wasn't documented.

Hence, add documentation, code comments, and a test case to clarify
that this behavior is all intentional.

In passing, adjust ATExecAddColumn's defaults-related relkind check
so that it matches up exactly with ATRewriteTables, instead of being
effectively (though not literally) the negated inverse condition.
The reasoning can be explained a lot more concisely that way, too
(not to mention that the comment now matches the code, which it
did not before).

Discussion: https://postgr.es/m/10365.1558909428@sss.pgh.pa.us

3 files changed, 24 insertions, 8 deletions

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 30b72b62971..faf0db99e2a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6126,14 +6126,18 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	 * returned by AddRelationNewConstraints, so that the right thing happens
 	 * when a datatype's default applies.
 	 *
-	 * We skip this step completely for views and foreign tables.  For a view,
-	 * we can only get here from CREATE OR REPLACE VIEW, which historically
-	 * doesn't set up defaults, not even for domain-typed columns.  And in any
-	 * case we mustn't invoke Phase 3 on a view or foreign table, since they
-	 * have no storage.
-	 */
-	if (relkind != RELKIND_VIEW && relkind != RELKIND_COMPOSITE_TYPE
-		&& relkind != RELKIND_FOREIGN_TABLE && attribute.attnum > 0)
+	 * Note: it might seem that this should happen at the end of Phase 2, so
+	 * that the effects of subsequent subcommands can be taken into account.
+	 * It's intentional that we do it now, though.  The new column should be
+	 * filled according to what is said in the ADD COLUMN subcommand, so that
+	 * the effects are the same as if this subcommand had been run by itself
+	 * and the later subcommands had been issued in new ALTER TABLE commands.
+	 *
+	 * We can skip this entirely for relations without storage, since Phase 3
+	 * is certainly not going to touch them.  System attributes don't have
+	 * interesting defaults, either.
+	 */
+	if (RELKIND_HAS_STORAGE(relkind) && attribute.attnum > 0)
 	{
 		/*
 		 * For an identity column, we can't use build_column_default(),
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 7cf4696ec95..1a614b85f99 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -409,6 +409,12 @@ ALTER TABLE itest8
   ALTER COLUMN f5 DROP NOT NULL,
   ALTER COLUMN f5 SET DATA TYPE bigint;
 INSERT INTO itest8 VALUES(0), (1);
+-- This does not work when the table isn't empty.  That's intentional,
+-- since ADD GENERATED should only affect later insertions:
+ALTER TABLE itest8
+  ADD COLUMN f22 int NOT NULL,
+  ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY;
+ERROR:  column "f22" contains null values
 TABLE itest8;
  f1 | f2 | f3 | f4 | f5 
 ----+----+----+----+----
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index 685607c90c1..b4cdd21bdd4 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -269,6 +269,12 @@ ALTER TABLE itest8
 
 INSERT INTO itest8 VALUES(0), (1);
 
+-- This does not work when the table isn't empty.  That's intentional,
+-- since ADD GENERATED should only affect later insertions:
+ALTER TABLE itest8
+  ADD COLUMN f22 int NOT NULL,
+  ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY;
+
 TABLE itest8;
 \d+ itest8
 \d itest8_f2_seq