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
Appearance settings

Commit 64190d6

Browse filesBrowse 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 e48ce7e commit 64190d6
Copy full SHA for 64190d6

File tree

Expand file treeCollapse file tree

3 files changed

+95
-0
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+95
-0
lines changed

‎src/backend/commands/tablecmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/tablecmds.c
+60Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/expected/generated.out
+21Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/generated.sql
+14Lines 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+
CREATE TABLE gtest_normal_child2 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
100+
ALTER TABLE 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
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
101106
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
102107
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
103108

109+
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
110+
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
111+
CREATE TABLE gtestxx_2 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 22) STORED);
112+
ALTER TABLE gtestxx_2 INHERIT gtest1; -- error
113+
CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
114+
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
115+
CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
116+
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
117+
104118
-- test multiple inheritance mismatches
105119
CREATE TABLE gtesty (x int, b int);
106120
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.