On Thu, 16 Jul 2020 at 04:29, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Peter Eisentraut <peter.eisentr...@2ndquadrant.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
fix_gcolumn_dump.patch
Description: Binary data
