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

Commita970edb

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 parentae9492a commita970edb

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
@@ -14381,6 +14381,66 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel)
1438114381
errmsg("column \"%s\" in child table must be marked NOT NULL",
1438214382
attributeName)));
1438314383

14384+
/*
14385+
* If parent column is generated, child column must be, too.
14386+
*/
14387+
if (attribute->attgenerated && !childatt->attgenerated)
14388+
ereport(ERROR,
14389+
(errcode(ERRCODE_DATATYPE_MISMATCH),
14390+
errmsg("column \"%s\" in child table must be a generated column",
14391+
attributeName)));
14392+
14393+
/*
14394+
* Check that both generation expressions match.
14395+
*
14396+
* The test we apply is to see whether they reverse-compile to the
14397+
* same source string. This insulates us from issues like whether
14398+
* attributes have the same physical column numbers in parent and
14399+
* child relations. (See also constraints_equivalent().)
14400+
*/
14401+
if (attribute->attgenerated && childatt->attgenerated)
14402+
{
14403+
TupleConstr *child_constr = child_rel->rd_att->constr;
14404+
TupleConstr *parent_constr = parent_rel->rd_att->constr;
14405+
char *child_expr = NULL;
14406+
char *parent_expr = NULL;
14407+
14408+
Assert(child_constr != NULL);
14409+
Assert(parent_constr != NULL);
14410+
14411+
for (int i = 0; i < child_constr->num_defval; i++)
14412+
{
14413+
if (child_constr->defval[i].adnum == childatt->attnum)
14414+
{
14415+
child_expr =
14416+
TextDatumGetCString(DirectFunctionCall2(pg_get_expr,
14417+
CStringGetTextDatum(child_constr->defval[i].adbin),
14418+
ObjectIdGetDatum(child_rel->rd_id)));
14419+
break;
14420+
}
14421+
}
14422+
Assert(child_expr != NULL);
14423+
14424+
for (int i = 0; i < parent_constr->num_defval; i++)
14425+
{
14426+
if (parent_constr->defval[i].adnum == attribute->attnum)
14427+
{
14428+
parent_expr =
14429+
TextDatumGetCString(DirectFunctionCall2(pg_get_expr,
14430+
CStringGetTextDatum(parent_constr->defval[i].adbin),
14431+
ObjectIdGetDatum(parent_rel->rd_id)));
14432+
break;
14433+
}
14434+
}
14435+
Assert(parent_expr != NULL);
14436+
14437+
if (strcmp(child_expr, parent_expr) != 0)
14438+
ereport(ERROR,
14439+
(errcode(ERRCODE_DATATYPE_MISMATCH),
14440+
errmsg("column \"%s\" in child table has a conflicting generation expression",
14441+
attributeName)));
14442+
}
14443+
1438414444
/*
1438514445
* OK, bump the child column's inheritance count. (If we fail
1438614446
* 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
@@ -281,6 +281,17 @@ SELECT * FROM gtest_normal;
281281
2 | 4
282282
(2 rows)
283283

284+
CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
285+
ALTER TABLE gtest_normal_child2 INHERIT gtest_normal;
286+
INSERT INTO gtest_normal_child2 (a) VALUES (3);
287+
SELECT * FROM gtest_normal;
288+
a | b
289+
---+---
290+
1 |
291+
2 | 4
292+
3 | 9
293+
(3 rows)
294+
284295
-- test inheritance mismatches between parent and child
285296
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
286297
NOTICE: merging column "b" with inherited definition
@@ -292,6 +303,16 @@ ERROR: column "b" inherits from generated column but specifies default
292303
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
293304
NOTICE: merging column "b" with inherited definition
294305
ERROR: column "b" inherits from generated column but specifies identity
306+
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
307+
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
308+
ERROR: column "b" in child table must be a generated column
309+
CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED);
310+
ALTER TABLE gtestxx_2 INHERIT gtest1; -- error
311+
ERROR: column "b" in child table has a conflicting generation expression
312+
CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
313+
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
314+
CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
315+
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
295316
-- test multiple inheritance mismatches
296317
CREATE TABLE gtesty (x int, b int);
297318
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
@@ -113,11 +113,25 @@ INSERT INTO gtest_normal (a) VALUES (1);
113113
INSERT INTO gtest_normal_child (a)VALUES (2);
114114
SELECT*FROM gtest_normal;
115115

116+
CREATETABLEgtest_normal_child2 (aint, bint GENERATED ALWAYSAS (a*3) STORED);
117+
ALTERTABLE gtest_normal_child2 INHERIT gtest_normal;
118+
INSERT INTO gtest_normal_child2 (a)VALUES (3);
119+
SELECT*FROM gtest_normal;
120+
116121
-- test inheritance mismatches between parent and child
117122
CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS (a*22) STORED) INHERITS (gtest1);-- error
118123
CREATETABLEgtestx (xint, bint DEFAULT10) INHERITS (gtest1);-- error
119124
CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS IDENTITY) INHERITS (gtest1);-- error
120125

126+
CREATETABLEgtestxx_1 (aintNOT NULL, bint);
127+
ALTERTABLE gtestxx_1 INHERIT gtest1;-- error
128+
CREATETABLEgtestxx_2 (aintNOT NULL, bint GENERATED ALWAYSAS (a*22) STORED);
129+
ALTERTABLE gtestxx_2 INHERIT gtest1;-- error
130+
CREATETABLEgtestxx_3 (aintNOT NULL, bint GENERATED ALWAYSAS (a*2) STORED);
131+
ALTERTABLE gtestxx_3 INHERIT gtest1;-- ok
132+
CREATETABLEgtestxx_4 (bint GENERATED ALWAYSAS (a*2) STORED, aintNOT NULL);
133+
ALTERTABLE gtestxx_4 INHERIT gtest1;-- ok
134+
121135
-- test multiple inheritance mismatches
122136
CREATETABLEgtesty (xint, bint);
123137
CREATETABLEgtest1_2 () INHERITS (gtest1, gtesty);-- error

0 commit comments

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