Re: Dumping/restoring fails on inherited generated column

On 2020-04-23 08:35, Masahiko Sawada wrote:
> After investigating this issue, I think that current DDLs regarding
> inherited tables and generated columns seem not to work fine.

Right, there were a number of combinations that were not properly
handled. The attached patch should fix them all. It's made against
PG12 but also works on master. See contained commit message and
documentation for details.

(This does not touch the issues with pg_dump, but it helps clarify the
cases that pg_dump needs to handle.)

> 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);

With my patch, this becomes an error.

> 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

With my patch, this also becomes an error.

> 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...

This is allowed with my patch (which is basically an expanded version of
your patch).

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