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

Started by tusharover 5 years ago3 messages

tushar.ahuja@enterprisedb.com

over 5 years ago

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

Masahiko Sawada

masahiko.sawada@2ndquadrant.com

over 5 years ago

In reply to: tushar (#1)

1 attachment(s)

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.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

Attachments:

generated_column_pg_dump.patchapplication/octet-stream; name=generated_column_pg_dump.patchDownload

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 5db4f5761d..bbd85599dc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8880,6 +8880,8 @@ shouldPrintColumn(DumpOptions *dopt, TableInfo *tbinfo, int colno)
 		return true;
 	if (tbinfo->attisdropped[colno])
 		return false;
+	if (tbinfo->attgenerated[colno])
+		return true;
 	return (tbinfo->attislocal[colno] || tbinfo->ispartition);
 }

Masahiko Sawada

masahiko.sawada@2ndquadrant.com

over 5 years ago

In reply to: Masahiko Sawada (#2)

1 attachment(s)

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

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

Attachments:

generated_column_pg_dump_v2.patchapplication/octet-stream; name=generated_column_pg_dump_v2.patchDownload

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 5db4f5761d..4aa5ed91bd 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16469,6 +16469,10 @@ dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
 	if (!adinfo->separate)
 		return;
 
+	/* ALTER COLUMN doesn't support setting a generated column */
+	if (tbinfo->attgenerated[adnum - 1])
+		return;
+
 	q = createPQExpBuffer();
 	delq = createPQExpBuffer();