Re: ATTACH PARTITION seems to ignore column generation status

On Tue, Jan 10, 2023 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > After thinking about this awhile, I feel that we ought to disallow
> > it in the traditional-inheritance case as well.  The reason is that
> > there are semantic prohibitions on inserting or updating a generated
> > column, eg
>
> > regression=# create table t (f1 int, f2 int generated always as (f1+1) stored);
> > CREATE TABLE
> > regression=# update t set f2=42;
> > ERROR:  column "f2" can only be updated to DEFAULT
> > DETAIL:  Column "f2" is a generated column.
>
> > It's not very reasonable to have to recheck that for child tables,
> > and we don't.  But if one does this:
>
> > regression=# create table pp (f1 int, f2 int);
> > CREATE TABLE
> > regression=# create table cc (f1 int, f2 int generated always as (f1+1) stored) inherits(pp);
> > NOTICE:  merging column "f1" with inherited definition
> > NOTICE:  merging column "f2" with inherited definition
> > CREATE TABLE
> > regression=# insert into cc values(1);
> > INSERT 0 1
> > regression=# update pp set f2 = 99 where f1 = 1;
> > UPDATE 1
> > regression=# table cc;
> >  f1 | f2
> > ----+----
> >   1 | 99
> > (1 row)
>
> > That is surely just as broken as the partition-based case.

Agree that it looks bad.

> So what we need is about like this.  This is definitely not something
> to back-patch, since it's taking away what had been a documented
> behavior.  You could imagine trying to prevent such UPDATEs instead,
> but I judge it not worth the trouble.  If anyone were actually using
> this capability we'd have heard bug reports.

Thanks for the patch.  It looks good, though I guess you said that we
should also change the error message that CREATE TABLE ... PARTITION
OF emits to match the other cases while we're here.  I've attached a
delta patch.

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