Thread: While restoring -getting error if dump contain sql statementsgenerated from generated.sql file
While restoring -getting error if dump contain sql statementsgenerated from generated.sql file
From
tushar
Date:
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
Re: While restoring -getting error if dump contain sql statementsgenerated from generated.sql file
From
Masahiko Sawada
Date:
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
Attachment
Re: While restoring -getting error if dump contain sql statementsgenerated from generated.sql file
From
Masahiko Sawada
Date:
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