Re: Dumping/restoring fails on inherited generated column

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> On 2020-02-03 20:32, Tom Lane wrote:
>> This is showing us at least two distinct problems. Now as for
>> "gtest30_1", what we have is that in the parent table "gtest30", column b
>> exists but it has no default; the generated property is only added
>> at the child table gtest30_1. So we need to emit ALTER COLUMN SET
>> GENERATED ALWAYS for gtest30_1.b. HEAD is already doing the wrong
>> thing there (it's emitting the expression, but as a plain default
>> not GENERATED). And this patch makes it emit nothing, even worse.
>> I think the key point here is that "attislocal" refers to whether the
>> column itself is locally defined, not to whether its default is.

> This is a bit of a mess. Let me explain my thinking on generated
> columns versus inheritance.

> If a parent table has a generated column, then any inherited column must
> also be generated and use the same expression. (Otherwise querying the
> parent table would produce results that are inconsistent with the
> generation expression if the rows come from the child table.)

Check.

> If a parent table has a column that is not generated, then I think it
> would be semantically sound if a child table had that same column but
> generated. However, I think it would be very tricky to support this
> correctly, and it doesn't seem useful enough, so I'd rather not do it.

So ... why is that so hard exactly? AFAICS, the existing regression
test cases show that it works fine. Except that pg_dump gets it wrong.
In general, we surely want to support child columns that have defaults
different from the parent column's default, so this doesn't seem quite
that huge a leap to me.

> That's what the gtest30_1 case above shows. It's not even clear whether
> it's possible to dump this correctly in all cases because the syntax
> that you allude to "turn this existing column into a generated column"
> does not exist.

I'm a little confused by that statement. What is this doing, if not
that:

regression=# create table foo (f1 int not null);
CREATE TABLE
regression=# alter table foo alter column f1 add generated always as identity;
ALTER TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------
f1 | integer | | not null | generated always as identity

If we didn't have things like ALTER ... SET GENERATED and
ALTER ... DROP EXPRESSION, I'd be a lot more content to accept
the position that generated-ness is an immutable column property.
But we do have those things, so the restriction you're proposing
seems mighty arbitrary.

regards, tom lane