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
