Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4ac8aaa

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

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
@@ -192,13 +192,15 @@ typedef struct NewConstraint
192192
* Phase 3 copy (this could be either a new column with a non-null default, or
193193
* a column that we're changing the type of). Columns without such an entry
194194
* are just copied from the old table during ATRewriteTable. Note that the
195-
* expr is an expression over *old* table values.
195+
* expr is an expression over *old* table values, except when is_generated
196+
* is true; then it is an expression over columns of the *new* tuple.
196197
*/
197198
typedef struct NewColumnValue
198199
{
199200
AttrNumberattnum;/* which column */
200201
Expr *expr;/* expression to compute */
201202
ExprState *exprstate;/* execution state */
203+
boolis_generated;/* is it a GENERATED expression? */
202204
} NewColumnValue;
203205

204206
/*
@@ -4961,14 +4963,19 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
49614963

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

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

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

49784985
ExecStoreVirtualTuple(newslot);
49794986

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

58965924
tab->newvals = lappend(tab->newvals, newval);
58975925
}
@@ -10379,6 +10407,7 @@ ATPrepAlterColumnType(List **wqueue,
1037910407
newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
1038010408
newval->attnum = attnum;
1038110409
newval->expr = (Expr *) transform;
10410+
newval->is_generated = false;
1038210411

1038310412
tab->newvals = lappend(tab->newvals, newval);
1038410413
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 COLUMNb TYPE numeric;
630+
DETAIL: Column "a" is used by generated column "x".
631+
ALTER TABLE gtest27 ALTER COLUMNx 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 gtest25ORDER BY a;
317317
ALTERTABLE gtest25 ADD COLUMN xint GENERATED ALWAYSAS (b*4) STORED;-- error
318318
ALTERTABLE gtest25 ADD COLUMN xint GENERATED ALWAYSAS (z*4) STORED;-- error
319+
ALTERTABLE gtest25 ADD COLUMN cint DEFAULT42,
320+
ADD COLUMN xint GENERATED ALWAYSAS (c*4) STORED;
321+
ALTERTABLE gtest25 ADD COLUMN dint DEFAULT101;
322+
ALTERTABLE gtest25 ALTER COLUMN dSET DATA TYPE float8,
323+
ADD COLUMN y float8 GENERATED ALWAYSAS (d*4) STORED;
324+
SELECT*FROM gtest25ORDER BY a;
325+
\d gtest25
319326

320327
-- ALTER TABLE ... ALTER COLUMN
321328
CREATETABLEgtest27 (
322329
aint,
323-
bint GENERATED ALWAYSAS (a*2) STORED
330+
bint,
331+
xint GENERATED ALWAYSAS ((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
ALTERTABLE gtest27 ALTER COLUMN a TYPEtext;-- error
327-
ALTERTABLE gtest27 ALTER COLUMNb TYPEnumeric;
335+
ALTERTABLE gtest27 ALTER COLUMNx TYPEnumeric;
328336
\d gtest27
329337
SELECT*FROM gtest27;
330-
ALTERTABLE gtest27 ALTER COLUMN b TYPEboolean USING b<>0;-- error
331-
332-
ALTERTABLE gtest27 ALTER COLUMN b DROP DEFAULT;-- error
338+
ALTERTABLE gtest27 ALTER COLUMN x TYPEboolean USING x<>0;-- error
339+
ALTERTABLE gtest27 ALTER COLUMN x DROP DEFAULT;-- error
340+
-- It's possible to alter the column types this way:
341+
ALTERTABLE gtest27
342+
DROP COLUMN x,
343+
ALTER COLUMN a TYPEbigint,
344+
ALTER COLUMN b TYPEbigint,
345+
ADD COLUMN xbigint GENERATED ALWAYSAS ((a+ b)*2) STORED;
333346
\d gtest27
347+
-- Ideally you could just do this, but not today (and should x change type?):
348+
ALTERTABLE 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
CREATETABLEgtest26 (

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp