Re: Dumping/restoring fails on inherited generated column

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.

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