Re: While restoring -getting error if dump contain sql statements generated from generated.sql file

On Tue, 14 Apr 2020 at 22:41, tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> wrote:
>
> Hi ,
>
> We have a sql file called 'generated.sql' under src/test/regress/sql
> folder . if we run this file on psql , take the dump and try to restore
> it on another db
> we are getting error like -
>
> psql:/tmp/x:434: ERROR: column "b" of relation "gtest1_1" is a
> generated column
> psql:/tmp/x:441: ERROR: cannot use column reference in DEFAULT expression
>
> These sql statements , i copied from the dump file
>
> postgres=# CREATE TABLE public.gtest30 (
> postgres(# a integer,
> postgres(# b integer
> postgres(# );
> CREATE TABLE
> postgres=#
> postgres=# CREATE TABLE public.gtest30_1 (
> postgres(# )
> postgres-# INHERITS (public.gtest30);
> CREATE TABLE
> postgres=# ALTER TABLE ONLY public.gtest30_1 ALTER COLUMN b SET DEFAULT
> (a * 2);
> ERROR: cannot use column reference in DEFAULT expression
> postgres=#
>
> Steps to reproduce -
>
> connect to psql - ( ./psql postgres)
> create database ( create database x;)
> connect to database x (\c x )
> execute generated.sql file (\i ../../src/test/regress/sql/generated.sql)
> take the dump of x db (./pg_dump -Fp x > /tmp/t.dump)
> create another database (create database y;)
> Connect to y db (\c y)
> execute plain dump sql file (\i /tmp/t.dump)
>

Good catch. The minimum reproducer is to execute the following
queries, pg_dump and pg_restore/psql.

-- test case 1
create table a (a int, b int generated always as (a * 2) stored);
create table a1 () inherits(a);

-- test case 2
create table b (a int, b int generated always as (a * 2) stored);
create table b1 () inherits(b);
alter table only b alter column b drop expression;

After executing the above queries, pg_dump will generate the following queries:

-- test case 1
CREATE TABLE public.a (
a integer,
b integer GENERATED ALWAYS AS ((a * 2)) STORED
);
ALTER TABLE public.a OWNER TO masahiko;
CREATE TABLE public.a1 (
)
INHERITS (public.a);
ALTER TABLE public.a1 OWNER TO masahiko;
ALTER TABLE ONLY public.a1 ALTER COLUMN b SET DEFAULT (a * 2); -- error!

-- test case 2
CREATE TABLE public.b (
a integer,
b integer
);
ALTER TABLE public.b OWNER TO masahiko;
CREATE TABLE public.b1 (
)
INHERITS (public.b);
ALTER TABLE public.b1 OWNER TO masahiko;
ALTER TABLE ONLY public.b1 ALTER COLUMN b SET DEFAULT (a * 2); -- error!

pg_dump generates the same SQL "ALTER TABLE ... ALTER COLUMN b SET
DEFAULT (a * 2);" but the errors vary.

test case 1:
ERROR: column "b" of relation "a1" is a generated column

test case 2:
ERROR: cannot use column reference in DEFAULT expression

In both cases, I think we can simply get rid of that ALTER TABLE
queries if we don't support changing a normal column to a generated
column using ALTER TABLE .. ALTER COLUMN.

I've attached a WIP patch. I'll look at this closely and add regression tests.

Regards,

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