Обсуждение: While restoring -getting error if dump contain sql statementsgenerated from generated.sql file

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)

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

On Tue, 14 Apr 2020 at 22:41, tushar <tushar.ahuja@enterprisedb.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

On Fri, 17 Apr 2020 at 22:50, Masahiko Sawada
<masahiko.sawada@2ndquadrant.com> wrote:
>
> On Tue, 14 Apr 2020 at 22:41, tushar <tushar.ahuja@enterprisedb.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.
>

After more thoughts, the approach of the previous patch doesn't seem
correct. Instead, I think we can change dumpAttrDef so that it skips
emitting the query setting an expression of a generated column if the
column is a generated column.

Currently, we need to emit a query setting the default in the
following three cases (ref. adinfo->separate):

1. default is for column on VIEW
2. shouldPrintColumn() returns false in the two case:
    2-1. the column is a dropped column.
    2-2. the column is not a local column and the table is not a partition.

Since we don't support to set generated column as a default value for
a column of a view the case (1) is always false. And for the case
(2)-1, we don't dump a dropped column. I think the case (2)-2 means a
column inherited from the parent table but these columns are printed
in CREATE TABLE of the parent table and a child table inherits it. We
can have a generated column having a different expression from the
parent one but it will need to drop the inherited one and create a new
generated column. Such operation will make the column a local column,
so these definitions will be printed in the CREATE TABLE of the
inherited table. Therefore, IIUC there is no case where we need a
separate query setting an expression of a generated column.

Also, I've tried to add a regression test for this but pg_dump TAP
tests seem not to have a test if the dumped queries are loaded without
errors. I think we can have such a test but the attached updated
version patch doesn't include tests so far.

Regards,

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