From: | Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Dumping/restoring fails on inherited generated column |
Date: | 2020-08-27 11:30:02 |
Message-ID: | CA+fd4k6pLzrZDQsdsxcS06AwGRf1DgwOw84sFq9oXNw+83nB1g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
fix_gcolumn_dump_v2.patch | application/octet-stream | 691 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Sachin Khanna | 2020-08-27 11:51:09 | Please help for error ( file <libxml/parser.h> is required for XML support ) |
Previous Message | vignesh C | 2020-08-27 11:26:45 | Re: Parallel copy |