10000 Fix several DDL issues of generated columns versus inheritance · postgrespro/postgres_cluster@086ffdd · GitHub
[go: up one dir, main page]
Skip to content

Commit 086ffdd

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 501e41d commit 086ffdd

File tree

4 files changed

+146
-7
lines changed
  • doc/src/sgml
    • < 10000 div style="width:100%;display:flex">
      ddl.sgml
  • src

    • 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+
    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)
    0