Re: ATTACH PARTITION seems to ignore column generation status

On Fri, Jan 6, 2023 at 3:53 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> This does not seem good:
>
> regression=# create table pp (a int, b int) partition by range(a);
> CREATE TABLE
> regression=# create table cc (a int generated always as (b+1) stored, b int);
> CREATE TABLE
> regression=# alter table pp attach partition cc for values from ('1') to ('10');
> ALTER TABLE
> regression=# insert into pp values(1,100);
> INSERT 0 1
> regression=# table pp;
> a | b
> -----+-----
> 101 | 100
> (1 row)

This indeed is broken and seems like an oversight. :-(

> I'm not sure to what extent it's sensible for partitions to have
> GENERATED columns that don't match their parent; but even if that's
> okay for payload columns I doubt we want to allow partitioning
> columns to be GENERATED.

Actually, I'm inclined to disallow partitions to have *any* generated
columns that are not present in the parent table. The main reason for
that is the inconvenience of checking that a partition's generated
columns doesn't override the partition key column of an ancestor that
is not its immediate parent, which MergeAttributesIntoExisting() has
access to and would have been locked.

Patch doing it that way is attached. Perhaps the newly added error
message should match CREATE TABLE .. PARTITION OF's, but I found the
latter to be not detailed enough, or maybe that's just me.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com