On Fri, 17 Apr 2020 at 22:50, Masahiko Sawada <masahiko.saw...@2ndquadrant.com> wrote: > > On Tue, 14 Apr 2020 at 22:41, tushar <tushar.ah...@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
generated_column_pg_dump_v2.patch
Description: Binary data
