At Tue, 26 Nov 2019 10:49:11 -0500, Tom Lane <t...@sss.pgh.pa.us> wrote in
> Andreas Joseph Krogh <andr...@visena.com> writes:
> > Run the attached script and you'll get:
>
> > psql -f error.sql -d test
> > psql:error.sql:37: ERROR: attribute number 6 exceeds number of columns 5
>
> Hmm, interesting. IMO, that *should* have thrown an error, but of
> course not that one. The ADD COLUMN operations are all processed
> in parallel, so it's not okay for one of them to have a GENERATED
> expression that refers to another one of the new columns. But you
> should have gotten a "no such column" type of error, not a run-time
> cross-check failure.
Something like this works?
ALTER TABLE gtest25 ADD COLUMN x int, ADD COLUMN y int GENERATED ALWAYS AS (x *
4) STORED;
ERROR: column "x" does not exist
DETAIL: An expression cannot reference columns added in the same command.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
>From 92787bcef1b190696f71238879bcd3a9cf51d42b Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyota....@gmail.com>
Date: Wed, 27 Nov 2019 11:16:07 +0900
Subject: [PATCH] Emit meaningful message for reference to concurrently added
column.
In ALTER TABLE ADD COLUMN, expressions cannot refer to columns
concurrently added by other subcommands. Currently we get an improper
message like "attribute number 6 exceeds number of columns 5" for the
case. Fix it.
---
src/backend/commands/tablecmds.c | 28 ++++++++++++++++++++++++-
src/test/regress/expected/generated.out | 3 +++
src/test/regress/sql/generated.sql | 1 +
3 files changed, 31 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5440eb9015..f75ca74c6b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5891,10 +5891,36 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
if (defval)
{
NewColumnValue *newval;
+ List *varlist;
+ ListCell *lc;
+ Expr *newexpr;
+
+ newexpr = expression_planner(defval);
+
+ /*
+ * The expression may reference columns added by concurrent
+ * subcommands, which leads to internal error on table
+ * rewrite. Check them out here to emit meaningful error message.
+ */
+ varlist = pull_var_clause((Node *) newexpr, 0);
+ foreach (lc, varlist)
+ {
+ Var *var = (Var *) lfirst(lc);
+
+ /* System columns are rejected ealier. */
+ Assert(var->varattno > 0);
+
+ if (var->varattno > tab->oldDesc->natts)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg ("column \"%s\" does not exist",
+ NameStr(TupleDescAttr(rel->rd_att, var->varattno - 1)->attname)),
+ errdetail("An expression cannot reference columns added in the same command.")));
+ }
newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
newval->attnum = attribute.attnum;
- newval->expr = expression_planner(defval);
+ newval->expr = newexpr;
tab->newvals = lappend(tab->newvals, newval);
}
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index f62c93f468..b5e58991bd 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -593,6 +593,9 @@ ERROR: cannot use generated column "b" in column generation expression
DETAIL: A generated column cannot reference another generated column.
ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
ERROR: column "z" does not exist
+ALTER TABLE gtest25 ADD COLUMN x int, ADD COLUMN y int GENERATED ALWAYS AS (x * 4) STORED; -- error
+ERROR: column "x" does not exist
+DETAIL: An expression cannot reference columns added in the same command.
-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
a int,
diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql
index 6a56ae260f..3b00f81b54 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -316,6 +316,7 @@ ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3) STORED;
SELECT * FROM gtest25 ORDER BY a;
ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED; -- error
ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
+ALTER TABLE gtest25 ADD COLUMN x int, ADD COLUMN y int GENERATED ALWAYS AS (x * 4) STORED; -- error
-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
--
2.23.0
