Skip to content

Commit a970edb

Browse files
peterepetere
committed
Fix ALTER TABLE / INHERIT with generated columns
When running ALTER TABLE t2 INHERIT t1, we must check that columns in t2 that correspond to a generated column in t1 are also generated and have the same generation expression. Otherwise, this would allow creating 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 parent ae9492a commit a970edb

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+
CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
117+
ALTER TABLE 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
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
118123
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
119124
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
120125

126+
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
127+
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
128+
CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED);
129+
ALTER TABLE gtestxx_2 INHERIT gtest1; -- error
130+
CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
131+
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
132+
CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
133+
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
134+
121135
-- test multiple inheritance mismatches
122136
CREATE TABLE gtesty (x int, b int);
123137
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error

0 commit comments

Comments
 (0)