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

Commit4b10074

Browse files
committed
Disallow whole-row variables in GENERATED expressions.
This was previously allowed, but I think that was just an oversight.It's a clear violation of the rule that a generated column cannotdepend on itself or other generated columns. Moreover, because thecode was relying on the assumption that no such cross-referencesexist, it was pretty easy to crash ALTER TABLE and perhaps otherplaces. Even if you managed not to crash, you got quite unstable,implementation-dependent results.Per report from Vitaly Ustinov.Back-patch to v12 where GENERATED came in.Discussion:https://postgr.es/m/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com
1 parent2b0ee12 commit4b10074

File tree

3 files changed

+23
-2
lines changed

3 files changed

+23
-2
lines changed

‎src/backend/catalog/heap.c

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3020,15 +3020,26 @@ check_nested_generated_walker(Node *node, void *context)
30203020
AttrNumberattnum;
30213021

30223022
relid=rt_fetch(var->varno,pstate->p_rtable)->relid;
3023+
if (!OidIsValid(relid))
3024+
return false;/* XXX shouldn't we raise an error? */
3025+
30233026
attnum=var->varattno;
30243027

3025-
if (OidIsValid(relid)&&AttributeNumberIsValid(attnum)&&get_attgenerated(relid,attnum))
3028+
if (attnum>0&&get_attgenerated(relid,attnum))
30263029
ereport(ERROR,
3027-
(errcode(ERRCODE_SYNTAX_ERROR),
3030+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
30283031
errmsg("cannot use generated column \"%s\" in column generation expression",
30293032
get_attname(relid,attnum, false)),
30303033
errdetail("A generated column cannot reference another generated column."),
30313034
parser_errposition(pstate,var->location)));
3035+
/* A whole-row Var is necessarily self-referential, so forbid it */
3036+
if (attnum==0)
3037+
ereport(ERROR,
3038+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
3039+
errmsg("cannot use whole-row variable in column generation expression"),
3040+
errdetail("This would cause the generated column to depend on its own value."),
3041+
parser_errposition(pstate,var->location)));
3042+
/* System columns were already checked in the parser */
30323043

30333044
return false;
30343045
}

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,13 @@ ERROR: cannot use generated column "b" in column generation expression
4646
LINE 1: ...AYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STO...
4747
^
4848
DETAIL: A generated column cannot reference another generated column.
49+
-- a whole-row var is a self-reference on steroids, so disallow that too
50+
CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
51+
b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STORED);
52+
ERROR: cannot use whole-row variable in column generation expression
53+
LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STOR...
54+
^
55+
DETAIL: This would cause the generated column to depend on its own value.
4956
-- invalid reference
5057
CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STORED);
5158
ERROR: column "c" does not exist

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,9 @@ CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) S
1717
-- references to other generated columns, including self-references
1818
CREATETABLEgtest_err_2a (aintPRIMARY KEY, bint GENERATED ALWAYSAS (b*2) STORED);
1919
CREATETABLEgtest_err_2b (aintPRIMARY KEY, bint GENERATED ALWAYSAS (a*2) STORED, cint GENERATED ALWAYSAS (b*3) STORED);
20+
-- a whole-row var is a self-reference on steroids, so disallow that too
21+
CREATETABLEgtest_err_2c (aintPRIMARY KEY,
22+
bint GENERATED ALWAYSAS (num_nulls(gtest_err_2c)) STORED);
2023

2124
-- invalid reference
2225
CREATETABLEgtest_err_3 (aintPRIMARY KEY, bint GENERATED ALWAYSAS (c*2) STORED);

0 commit comments

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