Re: Dumping/restoring fails on inherited generated column

On Thu, 16 Jul 2020 at 04:29, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> >> 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.
>
> > committed to master and PG12
>
> So ... this did not actually fix the dump/restore problem. In fact,
> it's worse, because in HEAD I see two failures not one when doing the
> same test proposed at the start of this thread:
>
> 1. make installcheck
> 2. pg_dump -Fc regression >r.dump
> 3. createdb r2
> 4. pg_restore -d r2 r.dump
>
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 6253; 2604 226187 DEFAULT gtest1_1 b postgres
> pg_restore: error: could not execute query: ERROR: column "b" of relation "gtest1_1" is a generated column
> Command was: ALTER TABLE ONLY public.gtest1_1 ALTER COLUMN b SET DEFAULT (a * 2);
>
>
> pg_restore: from TOC entry 6279; 2604 227276 DEFAULT gtest30_1 b postgres
> pg_restore: error: could not execute query: ERROR: cannot use column reference in DEFAULT expression
> Command was: ALTER TABLE ONLY public.gtest30_1 ALTER COLUMN b SET DEFAULT (a * 2);
>
>
> pg_restore: warning: errors ignored on restore: 2
>

The minimum reproducer is:

create table a (a int, b int generated always as (a * 2) stored);
create table aa () inherits (a);

pg_dump produces the following DDLs:

CREATE TABLE public.a (
a integer,
b integer GENERATED ALWAYS AS ((a * 2)) STORED
);

CREATE TABLE public.aa (
)
INHERITS (public.a);

ALTER TABLE ONLY public.aa ALTER COLUMN b SET DEFAULT (a * 2);

However, the ALTER TABLE fails.

By commit 086ffddf, the child tables must have the same generation
expression as the expression defined in the parent. So I think pg_dump
should not generate the last DDL. I've attached the patch fixing this
issue.

Apart from the fix, I wonder if we can add a test that dumps the
database where executed 'make check' and restore it to another
database.

Regards,

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