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

Commit64190d6

Browse files
committed
Fix ALTER TABLE / INHERIT with generated columns
When running ALTER TABLE t2 INHERIT t1, we must check that columns int2 that correspond to a generated column in t1 are also generated andhave the same generation expression. Otherwise, this would allowcreating setups that a normal CREATE TABLE sequence would not allow.Discussion:https://www.postgresql.org/message-id/22de27f6-7096-8d96-4619-7b882932ca25@2ndquadrant.com
1 parente48ce7e commit64190d6

File tree

3 files changed

+95
-0
lines changed

3 files changed

+95
-0
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13909,6 +13909,66 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel)
1390913909
errmsg("column \"%s\" in child table must be marked NOT NULL",
1391013910
attributeName)));
1391113911

13912+
/*
13913+
* If parent column is generated, child column must be, too.
13914+
*/
13915+
if (attribute->attgenerated && !childatt->attgenerated)
13916+
ereport(ERROR,
13917+
(errcode(ERRCODE_DATATYPE_MISMATCH),
13918+
errmsg("column \"%s\" in child table must be a generated column",
13919+
attributeName)));
13920+
13921+
/*
13922+
* Check that both generation expressions match.
13923+
*
13924+
* The test we apply is to see whether they reverse-compile to the
13925+
* same source string. This insulates us from issues like whether
13926+
* attributes have the same physical column numbers in parent and
13927+
* child relations. (See also constraints_equivalent().)
13928+
*/
13929+
if (attribute->attgenerated && childatt->attgenerated)
13930+
{
13931+
TupleConstr *child_constr = child_rel->rd_att->constr;
13932+
TupleConstr *parent_constr = parent_rel->rd_att->constr;
13933+
char *child_expr = NULL;
13934+
char *parent_expr = NULL;
13935+
13936+
Assert(child_constr != NULL);
13937+
Assert(parent_constr != NULL);
13938+
13939+
for (int i = 0; i < child_constr->num_defval; i++)
13940+
{
13941+
if (child_constr->defval[i].adnum == childatt->attnum)
13942+
{
13943+
child_expr =
13944+
TextDatumGetCString(DirectFunctionCall2(pg_get_expr,
13945+
CStringGetTextDatum(child_constr->defval[i].adbin),
13946+
ObjectIdGetDatum(child_rel->rd_id)));
13947+
break;
13948+
}
13949+
}
13950+
Assert(child_expr != NULL);
13951+
13952+
for (int i = 0; i < parent_constr->num_defval; i++)
13953+
{
13954+
if (parent_constr->defval[i].adnum == attribute->attnum)
13955+
{
13956+
parent_expr =
13957+
TextDatumGetCString(DirectFunctionCall2(pg_get_expr,
13958+
CStringGetTextDatum(parent_constr->defval[i].adbin),
13959+
ObjectIdGetDatum(parent_rel->rd_id)));
13960+
break;
13961+
}
13962+
}
13963+
Assert(parent_expr != NULL);
13964+
13965+
if (strcmp(child_expr, parent_expr) != 0)
13966+
ereport(ERROR,
13967+
(errcode(ERRCODE_DATATYPE_MISMATCH),
13968+
errmsg("column \"%s\" in child table has a conflicting generation expression",
13969+
attributeName)));
13970+
}
13971+
1391213972
/*
1391313973
* OK, bump the child column's inheritance count. (If we fail
1391413974
* later on, this change will just roll back.)

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -240,6 +240,17 @@ SELECT * FROM gtest_normal;
240240
2 | 4
241241
(2 rows)
242242

243+
CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
244+
ALTER TABLE gtest_normal_child2 INHERIT gtest_normal;
245+
INSERT INTO gtest_normal_child2 (a) VALUES (3);
246+
SELECT * FROM gtest_normal;
247+
a | b
248+
---+---
249+
1 |
250+
2 | 4
251+
3 | 9
252+
(3 rows)
253+
243254
-- test inheritance mismatches between parent and child
244255
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
245256
NOTICE: merging column "b" with inherited definition
@@ -251,6 +262,16 @@ ERROR: column "b" inherits from generated column but specifies default
251262
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
252263
NOTICE: merging column "b" with inherited definition
253264
ERROR: column "b" inherits from generated column but specifies identity
265+
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
266+
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
267+
ERROR: column "b" in child table must be a generated column
268+
CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED);
269+
ALTER TABLE gtestxx_2 INHERIT gtest1; -- error
270+
ERROR: column "b" in child table has a conflicting generation expression
271+
CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
272+
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
273+
CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
274+
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
254275
-- test multiple inheritance mismatches
255276
CREATE TABLE gtesty (x int, b int);
256277
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,11 +96,25 @@ INSERT INTO gtest_normal (a) VALUES (1);
9696
INSERT INTO gtest_normal_child (a)VALUES (2);
9797
SELECT*FROM gtest_normal;
9898

99+
CREATETABLEgtest_normal_child2 (aint, bint GENERATED ALWAYSAS (a*3) STORED);
100+
ALTERTABLE gtest_normal_child2 INHERIT gtest_normal;
101+
INSERT INTO gtest_normal_child2 (a)VALUES (3);
102+
SELECT*FROM gtest_normal;
103+
99104
-- test inheritance mismatches between parent and child
100105
CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS (a*22) STORED) INHERITS (gtest1);-- error
101106
CREATETABLEgtestx (xint, bint DEFAULT10) INHERITS (gtest1);-- error
102107
CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS IDENTITY) INHERITS (gtest1);-- error
103108

109+
CREATETABLEgtestxx_1 (aintNOT NULL, bint);
110+
ALTERTABLE gtestxx_1 INHERIT gtest1;-- error
111+
CREATETABLEgtestxx_2 (aintNOT NULL, bint GENERATED ALWAYSAS (a*22) STORED);
112+
ALTERTABLE gtestxx_2 INHERIT gtest1;-- error
113+
CREATETABLEgtestxx_3 (aintNOT NULL, bint GENERATED ALWAYSAS (a*2) STORED);
114+
ALTERTABLE gtestxx_3 INHERIT gtest1;-- ok
115+
CREATETABLEgtestxx_4 (bint GENERATED ALWAYSAS (a*2) STORED, aintNOT NULL);
116+
ALTERTABLE gtestxx_4 INHERIT gtest1;-- ok
117+
104118
-- test multiple inheritance mismatches
105119
CREATETABLEgtesty (xint, bint);
106120
CREATETABLEgtest1_2 () INHERITS (gtest1, gtesty);-- error

0 commit comments

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