On Tue, 14 Apr 2020 at 22:41, tushar <[email protected]> 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
generated_column_pg_dump.patch
Description: Binary data
