Re: Dumping/restoring fails on inherited generated column - Mailing list pgsql-hackers

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

On Fri, 7 Feb 2020 at 04:36, Peter Eisentraut
<peter.eisentraut@2ndquadrant.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