Re: Dumping/restoring fails on inherited generated column

On Fri, 25 Sep 2020 at 22:07, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
>
> I have been analyzing this issue again.  We have a few candidate patches
> that do very similar things for avoiding dumping the generation
> expression of table gtest1_1.  We can figure out later which one of
> these we like best.  But there is another issue lurking nearby.  The
> table hierarchy of gtest30, which is created in the regression tests
> like this:
>
> CREATE TABLE gtest30 (
>      a int,
>      b int GENERATED ALWAYS AS (a * 2) STORED
> );
> CREATE TABLE gtest30_1 () INHERITS (gtest30);
> ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION;
>
> This drops the generation expression from the parent table but not the
> child table.  This is currently dumped like this:
>
> CREATE TABLE public.gtest30 (
>      a integer,
>      b integer
> );
>
> CREATE TABLE public.gtest30_1 (
> )
> INHERITS (public.gtest30);
>
> ALTER TABLE ONLY public.gtest30_1 ALTER COLUMN b SET DEFAULT (a * 2);
>
> The proposed patches will cause the last statement to be omitted, but
> that still won't recreate the original state.  The problem is that there
> is no command to make a column generated afterwards, like the SET
> DEFAULT command, so we can't dump it like this.  We would have to produce
>
> CREATE TABLE public.gtest30 (
>      a integer,
>      b integer
> );
>
> CREATE TABLE public.gtest30_1 (
>      b integer GENERATED ALWAYS AS (a * 2) STORED
> )
> INHERITS (public.gtest30);
>
> but this will create the column "b" of gtest30_1 as attlocal, which the
> original command sequence does not.
>
> We could probably fix this by having ALTER TABLE ONLY / DROP EXPRESSION
> update the attlocal column of direct children to true, to make the
> catalog state look like something that can be restored.  However, that's
> a fair amount of complicated code, so for now I propose to just prohibit
> this command, meaning you can't use ONLY in this command if there are
> children.  This is new in PG13, so this change would have very limited
> impact in practice.
>
> Proposed patch attached.

+1

If we have ALTER TABLE ONLY / DROP EXPRESSION update the attlocal
column of children to true to fix the issue you raised, my proposed
patch is not necessary. OTOH if we fix it by prohibiting the command,
I guess we need both patches to fix the issues.

Regards,

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