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

Commit086ffdd

Browse files
committed
Fix several DDL issues of generated columns versus inheritance
Several combinations of generated columns and inheritance in CREATETABLE 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 rulesinvolving generated columns and inheritance.Discussion:https://www.postgresql.org/message-id/flat/15830.1575468847%40sss.pgh.pa.ushttps://www.postgresql.org/message-id/flat/2678bad1-048f-519a-ef24-b12962f41807%40enterprisedb.comhttps://www.postgresql.org/message-id/flat/CAJvUf_u4h0DxkCMCeEKAWCuzGUTnDP-G5iVmSwxLQSXn0_FWNQ%40mail.gmail.com
1 parent501e41d commit086ffdd

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
@@ -2618,12 +2618,55 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
26182618
def->is_local = true;
26192619
/* Merge of NOT NULL constraints = OR 'em together */
26202620
def->is_not_null |= newdef->is_not_null;
2621+
2622+
/*
2623+
* Check for conflicts related to generated columns.
2624+
*
2625+
* If the parent column is generated, the child column must be
2626+
* unadorned and will be made a generated column. (We could
2627+
* in theory allow the child column definition specifying the
2628+
* exact same generation expression, but that's a bit
2629+
* complicated to implement and doesn't seem very useful.) We
2630+
* also check that the child column doesn't specify a default
2631+
* value or identity, which matches the rules for a single
2632+
* column in parse_util.c.
2633+
*/
2634+
if (def->generated)
2635+
{
2636+
if (newdef->generated)
2637+
ereport(ERROR,
2638+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2639+
errmsg("child column \"%s\" specifies generation expression",
2640+
def->colname),
2641+
errhint("Omit the generation expression in the definition of the child table column to inherit the generation expression from the parent table.")));
2642+
if (newdef->raw_default && !newdef->generated)
2643+
ereport(ERROR,
2644+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2645+
errmsg("column \"%s\" inherits from generated column but specifies default",
2646+
def->colname)));
2647+
if (newdef->identity)
2648+
ereport(ERROR,
2649+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2650+
errmsg("column \"%s\" inherits from generated column but specifies identity",
2651+
def->colname)));
2652+
}
2653+
/*
2654+
* If the parent column is not generated, then take whatever
2655+
* the child column definition says.
2656+
*/
2657+
else
2658+
{
2659+
if (newdef->generated)
2660+
def->generated = newdef->generated;
2661+
}
2662+
26212663
/* If new def has a default, override previous default */
26222664
if (newdef->raw_default != NULL)
26232665
{
26242666
def->raw_default = newdef->raw_default;
26252667
def->cooked_default = newdef->cooked_default;
26262668
}
2669+
26272670
}
26282671
else
26292672
{
@@ -2709,11 +2752,19 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
27092752
ColumnDef *def = lfirst(entry);
27102753

27112754
if (def->cooked_default == &bogus_marker)
2712-
ereport(ERROR,
2713-
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2714-
errmsg("column \"%s\" inherits conflicting default values",
2715-
def->colname),
2716-
errhint("To resolve the conflict, specify a default explicitly.")));
2755+
{
2756+
if (def->generated)
2757+
ereport(ERROR,
2758+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2759+
errmsg("column \"%s\" inherits conflicting generation expressions",
2760+
def->colname)));
2761+
else
2762+
ereport(ERROR,
2763+
(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
2764+
errmsg("column \"%s\" inherits conflicting default values",
2765+
def->colname),
2766+
errhint("To resolve the conflict, specify a default explicitly.")));
2767+
}
27172768
}
27182769
}
27192770

‎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+
CREATETABLEgtest_normal (aint, bint);
93+
CREATETABLEgtest_normal_child (aint, bint GENERATED ALWAYSAS (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+
CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS (a*22) STORED) INHERITS (gtest1);-- error
101+
CREATETABLEgtestx (xint, bint DEFAULT10) INHERITS (gtest1);-- error
102+
CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS IDENTITY) INHERITS (gtest1);-- error
103+
104+
-- test multiple inheritance mismatches
93105
CREATETABLEgtesty (xint, bint);
94106
CREATETABLEgtest1_2 () INHERITS (gtest1, gtesty);-- error
95107
DROPTABLE gtesty;
96108

109+
CREATETABLEgtesty (xint, bint GENERATED ALWAYSAS (x*22) STORED);
110+
CREATETABLEgtest1_2 () INHERITS (gtest1, gtesty);-- error
111+
DROPTABLE gtesty;
112+
113+
CREATETABLEgtesty (xint, bint DEFAULT55);
114+
CREATETABLEgtest1_2 () INHERITS (gtest0, gtesty);-- error
115+
DROPTABLE gtesty;
116+
97117
-- test stored update
98118
CREATETABLEgtest3 (aint, bint GENERATED ALWAYSAS (a*3) STORED);
99119
INSERT INTO gtest3 (a)VALUES (1), (2), (3), (NULL);

0 commit comments

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