Re: Dumping/restoring fails on inherited generated column

I arrived at this thread while investigating the same issue recently
reported[1].

On Fri, 7 Feb 2020 at 04:36, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
> On 2020-02-03 20:32, Tom Lane wrote:
> > Things are evidently also going wrong for "gtest1_1". In that case
> > the generated property is inherited from the parent gtest1, so we
> > shouldn't be emitting anything ... how come the patch fails to
> > make it do that?
>
> This is fixed by the attached new patch. It needed an additional check
> in flagInhAttrs().
>
> > 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.)

After investigating this issue, I think that current DDLs regarding
inherited tables and generated columns seem not to work fine.

We can make an inherited table have the same column having a different
generation expression as follows:

=# create table p1 (a int, b int generated always as (a + 1) stored);
=# create table c1 (a int, b int generated always as (a + 2) stored)
inherits(p1);

But the column on the inherited table has a default value, the column
will be generation expression with a const value:

=# create table p2 (a int, b int generated always as (a + 1) stored);
=# create table c2 (a int, b int default 10) inherits(p2);
=# \d c2
Table "public.c2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------------------------
a | integer | | |
b | integer | | | generated always as (10) stored
Inherits: p2

Also, CREATE TABLE doesn't support to create a generated column on
inherited table, which is the same name but is a normal column on the
parent table, as follows:

=# create table p3 (a int, b int);
=# create table c3 (a int, b int generated always as (a + 2) stored)
inherits(p3);
ERROR: cannot use column reference in DEFAULT expression
LINE 1: ...reate table c3 (a int, b int generated always as (a + 2) sto...

Aside from the error message seems not correct, it's actually possible
that we can have only the inherited table's column have a generation
expression by:

=# create table p4 (a int, b int);
=# create table c4 (a int);
=# alter table c4 add column b int generated always as (a * 3) stored;
=# alter table c4 inherit p4;

Because of this behavior, pg_dump generates a query for the table c4
that cannot be restored.

I think we can fix these issues with the attached patch but it seems
better discussing the desired behavior first.

Regards,

[1] https://www.postgresql.org/message-id/2678bad1-048f-519a-ef24-b12962f41807@enterprisedb.com

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services