Skip to content

Commit c24f3b7

Browse files
tglsfdctglsfdc
committed
Fix handling of generated columns in ALTER TABLE.
ALTER TABLE failed if a column referenced in a GENERATED expression had been added or changed in type earlier in the ALTER command. That's because the GENERATED expression needs to be evaluated against the table's updated tuples, but it was being evaluated against the original tuples. (Fortunately the executor has adequate cross-checks to notice the mismatch, so we just got an obscure error message and not anything more dangerous.) Per report from Andreas Joseph Krogh. Back-patch to v12 where GENERATED was added. Discussion: https://postgr.es/m/VisenaEmail.200.231b0a41523275d0.16ea7f800c7@tc7-visena
1 parent a1c003e commit c24f3b7

File tree

3 files changed

+132
-29
lines changed

3 files changed

+132
-29
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 31 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -194,13 +194,15 @@ typedef struct NewConstraint
194194
* Phase 3 copy (this could be either a new column with a non-null default, or
195195
* a column that we're changing the type of). Columns without such an entry
196196
* are just copied from the old table during ATRewriteTable. Note that the
197-
* expr is an expression over *old* table values.
197+
* expr is an expression over *old* table values, except when is_generated
198+
* is true; then it is an expression over columns of the *new* tuple.
198199
*/
199200
typedef struct NewColumnValue
200201
{
201202
AttrNumber attnum; /* which column */
202203
Expr *expr; /* expression to compute */
203204
ExprState *exprstate; /* execution state */
205+
bool is_generated; /* is it a GENERATED expression? */
204206
} NewColumnValue;
205207

206208
/*
@@ -4960,14 +4962,19 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
49604962

49614963
/*
49624964
* Process supplied expressions to replace selected columns.
4963-
* Expression inputs come from the old tuple.
4965+
*
4966+
* First, evaluate expressions whose inputs come from the old
4967+
* tuple.
49644968
*/
49654969
econtext->ecxt_scantuple = oldslot;
49664970

49674971
foreach(l, tab->newvals)
49684972
{
49694973
NewColumnValue *ex = lfirst(l);
49704974

4975+
if (ex->is_generated)
4976+
continue;
4977+
49714978
newslot->tts_values[ex->attnum - 1]
49724979
= ExecEvalExpr(ex->exprstate,
49734980
econtext,
@@ -4976,6 +4983,26 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
49764983

49774984
ExecStoreVirtualTuple(newslot);
49784985

4986+
/*
4987+
* Now, evaluate any expressions whose inputs come from the
4988+
* new tuple. We assume these columns won't reference each
4989+
* other, so that there's no ordering dependency.
4990+
*/
4991+
econtext->ecxt_scantuple = newslot;
4992+
4993+
foreach(l, tab->newvals)
4994+
{
4995+
NewColumnValue *ex = lfirst(l);
4996+
4997+
if (!ex->is_generated)
4998+
continue;
4999+
5000+
newslot->tts_values[ex->attnum - 1]
5001+
= ExecEvalExpr(ex->exprstate,
5002+
econtext,
5003+
&newslot->tts_isnull[ex->attnum - 1]);
5004+
}
5005+
49795006
/*
49805007
* Constraints might reference the tableoid column, so
49815008
* initialize t_tableOid before evaluating them.
@@ -5891,6 +5918,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
58915918
newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
58925919
newval->attnum = attribute.attnum;
58935920
newval->expr = expression_planner(defval);
5921+
newval->is_generated = (colDef->generated != '\0');
58945922

58955923
tab->newvals = lappend(tab->newvals, newval);
58965924
}
@@ -10407,6 +10435,7 @@ ATPrepAlterColumnType(List **wqueue,
1040710435
newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
1040810436
newval->attnum = attnum;
1040910437
newval->expr = (Expr *) transform;
10438+
newval->is_generated = false;
1041010439

1041110440
tab->newvals = lappend(tab->newvals, newval);
1041210441
if (ATColumnChangeRequiresRewrite(transform, attnum))

‎src/test/regress/expected/generated.out

Lines changed: 76 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -593,40 +593,95 @@ ERROR: cannot use generated column "b" in column generation expression
593593
DETAIL: A generated column cannot reference another generated column.
594594
ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
595595
ERROR: column "z" does not exist
596+
ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
597+
ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED;
598+
ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
599+
ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
600+
ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
601+
SELECT * FROM gtest25 ORDER BY a;
602+
a | b | c | x | d | y
603+
---+----+----+-----+-----+-----
604+
3 | 9 | 42 | 168 | 101 | 404
605+
4 | 12 | 42 | 168 | 101 | 404
606+
(2 rows)
607+
608+
\d gtest25
609+
Table "public.gtest25"
610+
Column | Type | Collation | Nullable | Default
611+
--------+------------------+-----------+----------+------------------------------------------------------
612+
a | integer | | not null |
613+
b | integer | | | generated always as (a * 3) stored
614+
c | integer | | | 42
615+
x | integer | | | generated always as (c * 4) stored
616+
d | double precision | | | 101
617+
y | double precision | | | generated always as (d * 4::double precision) stored
618+
Indexes:
619+
"gtest25_pkey" PRIMARY KEY, btree (a)
620+
596621
-- ALTER TABLE ... ALTER COLUMN
597622
CREATE TABLE gtest27 (
598623
a int,
599-
b int GENERATED ALWAYS AS (a * 2) STORED
624+
b int,
625+
x int GENERATED ALWAYS AS ((a + b) * 2) STORED
600626
);
601-
INSERT INTO gtest27 (a) VALUES (3), (4);
627+
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
602628
ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
603629
ERROR: cannot alter type of a column used by a generated column
604-
DETAIL: Column "a" is used by generated column "b".
605-
ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;
630+
DETAIL: Column "a" is used by generated column "x".
631+
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
606632
\d gtest27
607-
Table "public.gtest27"
608-
Column | Type | Collation | Nullable | Default
609-
--------+---------+-----------+----------+--------------------------------------
633+
Table "public.gtest27"
634+
Column | Type | Collation | Nullable | Default
635+
--------+---------+-----------+----------+--------------------------------------------
610636
a | integer | | |
611-
b | numeric | | | generated always as ((a * 2)) stored
637+
b | integer | | |
638+
x | numeric | | | generated always as (((a + b) * 2)) stored
612639

613640
SELECT * FROM gtest27;
614-
a | b
615-
---+---
616-
3 | 6
617-
4 | 8
641+
a | b | x
642+
---+----+----
643+
3 | 7 | 20
644+
4 | 11 | 30
618645
(2 rows)
619646

620-
ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean USING b <> 0; -- error
621-
ERROR: generation expression for column "b" cannot be cast automatically to type boolean
622-
ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error
623-
ERROR: column "b" of relation "gtest27" is a generated column
647+
ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
648+
ERROR: generation expression for column "x" cannot be cast automatically to type boolean
649+
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
650+
ERROR: column "x" of relation "gtest27" is a generated column
651+
-- It's possible to alter the column types this way:
652+
ALTER TABLE gtest27
653+
DROP COLUMN x,
654+
ALTER COLUMN a TYPE bigint,
655+
ALTER COLUMN b TYPE bigint,
656+
ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
624657
\d gtest27
625-
Table "public.gtest27"
626-
Column | Type | Collation | Nullable | Default
627-
--------+---------+-----------+----------+--------------------------------------
628-
a | integer | | |
629-
b | numeric | | | generated always as ((a * 2)) stored
658+
Table "public.gtest27"
659+
Column | Type | Collation | Nullable | Default
660+
--------+--------+-----------+----------+------------------------------------------
661+
a | bigint | | |
662+
b | bigint | | |
663+
x | bigint | | | generated always as ((a + b) * 2) stored
664+
665+
-- Ideally you could just do this, but not today (and should x change type?):
666+
ALTER TABLE gtest27
667+
ALTER COLUMN a TYPE float8,
668+
ALTER COLUMN b TYPE float8; -- error
669+
ERROR: cannot alter type of a column used by a generated column
670+
DETAIL: Column "a" is used by generated column "x".
671+
\d gtest27
672+
Table "public.gtest27"
673+
Column | Type | Collation | Nullable | Default
674+
--------+--------+-----------+----------+------------------------------------------
675+
a | bigint | | |
676+
b | bigint | | |
677+
x | bigint | | | generated always as ((a + b) * 2) stored
678+
679+
SELECT * FROM gtest27;
680+
a | b | x
681+
---+----+----
682+
3 | 7 | 20
683+
4 | 11 | 30
684+
(2 rows)
630685

631686
-- triggers
632687
CREATE TABLE gtest26 (

‎src/test/regress/sql/generated.sql

Lines changed: 25 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -316,21 +316,40 @@ ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3) STORED;
316316
SELECT * FROM gtest25 ORDER BY a;
317317
ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED; -- error
318318
ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
319+
ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
320+
ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED;
321+
ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
322+
ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
323+
ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
324+
SELECT * FROM gtest25 ORDER BY a;
325+
\d gtest25
319326

320327
-- ALTER TABLE ... ALTER COLUMN
321328
CREATE TABLE gtest27 (
322329
a int,
323-
b int GENERATED ALWAYS AS (a * 2) STORED
330+
b int,
331+
x int GENERATED ALWAYS AS ((a + b) * 2) STORED
324332
);
325-
INSERT INTO gtest27 (a) VALUES (3), (4);
333+
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
326334
ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
327-
ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;
335+
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
328336
\d gtest27
329337
SELECT * FROM gtest27;
330-
ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean USING b <> 0; -- error
331-
332-
ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- error
338+
ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
339+
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
340+
-- It's possible to alter the column types this way:
341+
ALTER TABLE gtest27
342+
DROP COLUMN x,
343+
ALTER COLUMN a TYPE bigint,
344+
ALTER COLUMN b TYPE bigint,
345+
ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
333346
\d gtest27
347+
-- Ideally you could just do this, but not today (and should x change type?):
348+
ALTER TABLE gtest27
349+
ALTER COLUMN a TYPE float8,
350+
ALTER COLUMN b TYPE float8; -- error
351+
\d gtest27
352+
SELECT * FROM gtest27;
334353

335354
-- triggers
336355
CREATE TABLE gtest26 (

0 commit comments

Comments
 (0)