Skip to content

Commit 14751c3

Browse files
peterepetere
committed
Fix several DDL issues of generated columns versus inheritance
Several combinations of generated columns and inheritance in CREATE TABLE were not handled correctly. Specifically: - Disallow a child column specifying a generation expression if the parent column is a generated column. The child column definition must be unadorned and the parent column's generation expression will be copied. - Prohibit a child column of a generated parent column specifying default values or identity. - Allow a child column of a not-generated parent column specifying itself as a generated column. This previously did not work, but it was possible to arrive at the state via other means (involving ALTER TABLE), so it seems sensible to support it. Add tests for each case. Also add documentation about the rules involving generated columns and inheritance. Discussion: https://www.postgresql.org/message-id/flat/15830.1575468847%40sss.pgh.pa.us https://www.postgresql.org/message-id/flat/2678bad1-048f-519a-ef24-b12962f41807%40enterprisedb.com https://www.postgresql.org/message-id/flat/CAJvUf_u4h0DxkCMCeEKAWCuzGUTnDP-G5iVmSwxLQSXn0_FWNQ%40mail.gmail.com
1 parent bf7233e commit 14751c3

File tree

4 files changed

+146
-7
lines changed

4 files changed

+146
-7
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -324,6 +324,32 @@ CREATE TABLE people (
324324
linkend="sql-createforeigntable"/> for details.
325325
</para>
326326
</listitem>
327+
<listitem>
328+
<para>For inheritance:</para>
329+
<itemizedlist>
330+
<listitem>
331+
<para>
332+
If a parent column is a generated column, a child column must also be
333+
a generated column using the same expression. In the definition of
334+
the child column, leave off the <literal>GENERATED</literal> clause,
335+
as it will be copied from the parent.
336+
</para>
337+
</listitem>
338+
<listitem>
339+
<para>
340+
In case of multiple inheritance, if one parent column is a generated
341+
column, then all parent columns must be generated columns and with the
342+
same expression.
343+
</para>
344+
</listitem>
345+
<listitem>
346+
<para>
347+
If a parent column is not a generated column, a child column may be
348+
defined to be a generated column or not.
349+
</para>
350+
</listitem>
351+
</itemizedlist>
352+
</listitem>
327353
</itemizedlist>
328354
</para>
329355

‎src/backend/commands/tablecmds.c

Lines changed: 56 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2577,12 +2577,55 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
25772577
def->is_local = true;
25782578
/* Merge of NOT NULL constraints = OR 'em together */
25792579
def->is_not_null |= newdef->is_not_null;
2580+
2581+
/*
2582+
* Check for conflicts related to generated columns.
2583+
*
2584+
* If the parent column is generated, the child column must be
2585+
* unadorned and will be made a generated column. (We could
2586+
* in theory allow the child column definition specifying the
2587+
* exact same generation expression, but that's a bit
2588+
* complicated to implement and doesn't seem very useful.) We
2589+
* also check that the child column doesn't specify a default
2590+
* value or identity, which matches the rules for a single
2591+
* column in parse_util.c.
2592+
*/
2593+
if (def->generated)
2594+
{
2595+
if (newdef->generated)
2596+
ereport(ERROR,
2597+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2598+
errmsg("child column \"%s\" specifies generation expression",
2599+
def->colname),
2600+
errhint("Omit the generation expression in the definition of the child table column to inherit the generation expression from the parent table.")));
2601+
if (newdef->raw_default && !newdef->generated)
2602+
ereport(ERROR,
2603+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2604+
errmsg("column \"%s\" inherits from generated column but specifies default",
2605+
def->colname)));
2606+
if (newdef->identity)
2607+
ereport(ERROR,
2608+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2609+
errmsg("column \"%s\" inherits from generated column but specifies identity",
2610+
def->colname)));
2611+
}
2612+
/*
2613+
* If the parent column is not generated, then take whatever
2614+
* the child column definition says.
2615+
*/
2616+
else
2617+
{
2618+
if (newdef->generated)
2619+
def->generated = newdef->generated;
2620+
}
2621+
25802622
/* If new def has a default, override previous default */
25812623
if (newdef->raw_default != NULL)
25822624
{
25832625
def->raw_default = newdef->raw_default;
25842626
def->cooked_default = newdef->cooked_default;
25852627
}
2628+
25862629
}
25872630
else
25882631
{
@@ -2668,11 +2711,19 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
26682711
ColumnDef *def = lfirst(entry);
26692712

26702713
if (def->cooked_default == &bogus_marker)
2671-
ereport(ERROR,
2672-
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2673-
errmsg("column \"%s\" inherits conflicting default values",
2674-
def->colname),
2675-
errhint("To resolve the conflict, specify a default explicitly.")));
2714+
{
2715+
if (def->generated)
2716+
ereport(ERROR,
2717+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2718+
errmsg("column \"%s\" inherits conflicting generation expressions",
2719+
def->colname)));
2720+
else
2721+
ereport(ERROR,
2722+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2723+
errmsg("column \"%s\" inherits conflicting default values",
2724+
def->colname),
2725+
errhint("To resolve the conflict, specify a default explicitly.")));
2726+
}
26762727
}
26772728
}
26782729

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

Lines changed: 43 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -219,12 +219,54 @@ SELECT * FROM gtest1;
219219
4 | 8
220220
(2 rows)
221221

222-
-- test inheritance mismatch
222+
CREATE TABLE gtest_normal (a int, b int);
223+
CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal);
224+
NOTICE: merging column "a" with inherited definition
225+
NOTICE: merging column "b" with inherited definition
226+
\d gtest_normal_child
227+
Table "public.gtest_normal_child"
228+
Column | Type | Collation | Nullable | Default
229+
--------+---------+-----------+----------+------------------------------------
230+
a | integer | | |
231+
b | integer | | | generated always as (a * 2) stored
232+
Inherits: gtest_normal
233+
234+
INSERT INTO gtest_normal (a) VALUES (1);
235+
INSERT INTO gtest_normal_child (a) VALUES (2);
236+
SELECT * FROM gtest_normal;
237+
a | b
238+
---+---
239+
1 |
240+
2 | 4
241+
(2 rows)
242+
243+
-- test inheritance mismatches between parent and child
244+
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
245+
NOTICE: merging column "b" with inherited definition
246+
ERROR: child column "b" specifies generation expression
247+
HINT: Omit the generation expression in the definition of the child table column to inherit the generation expression from the parent table.
248+
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
249+
NOTICE: merging column "b" with inherited definition
250+
ERROR: column "b" inherits from generated column but specifies default
251+
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
252+
NOTICE: merging column "b" with inherited definition
253+
ERROR: column "b" inherits from generated column but specifies identity
254+
-- test multiple inheritance mismatches
223255
CREATE TABLE gtesty (x int, b int);
224256
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
225257
NOTICE: merging multiple inherited definitions of column "b"
226258
ERROR: inherited column "b" has a generation conflict
227259
DROP TABLE gtesty;
260+
CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED);
261+
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
262+
NOTICE: merging multiple inherited definitions of column "b"
263+
ERROR: column "b" inherits conflicting generation expressions
264+
DROP TABLE gtesty;
265+
CREATE TABLE gtesty (x int, b int DEFAULT 55);
266+
CREATE TABLE gtest1_2 () INHERITS (gtest0, gtesty); -- error
267+
NOTICE: merging multiple inherited definitions of column "b"
268+
ERROR: inherited column "b" has a generation conflict
269+
DROP TABLE gtesty;
228270
-- test stored update
229271
CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
230272
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);

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

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,11 +89,31 @@ INSERT INTO gtest1_1 VALUES (4);
8989
SELECT * FROM gtest1_1;
9090
SELECT * FROM gtest1;
9191

92-
-- test inheritance mismatch
92+
CREATE TABLE gtest_normal (a int, b int);
93+
CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal);
94+
\d gtest_normal_child
95+
INSERT INTO gtest_normal (a) VALUES (1);
96+
INSERT INTO gtest_normal_child (a) VALUES (2);
97+
SELECT * FROM gtest_normal;
98+
99+
-- test inheritance mismatches between parent and child
100+
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
101+
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
102+
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
103+
104+
-- test multiple inheritance mismatches
93105
CREATE TABLE gtesty (x int, b int);
94106
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
95107
DROP TABLE gtesty;
96108

109+
CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED);
110+
CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
111+
DROP TABLE gtesty;
112+
113+
CREATE TABLE gtesty (x int, b int DEFAULT 55);
114+
CREATE TABLE gtest1_2 () INHERITS (gtest0, gtesty); -- error
115+
DROP TABLE gtesty;
116+
97117
-- test stored update
98118
CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
99119
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);

0 commit comments

Comments
 (0)