Re: Dumping/restoring fails on inherited generated column

On Thu, 23 Jul 2020 at 19:55, Masahiko Sawada
<masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
>
> 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.
>

This issue is not fixed yet. I've attached the updated version patch
and registered it to commit fest so as not to forget. Please review
it.

Regards,

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