Re: Dumping/restoring fails on inherited generated column

On 2021-01-29 17:41, Tom Lane wrote:
> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
>> I've had another go at this, and I've found a solution that appears to
>> address all the issues I'm aware of. It's all very similar to the
>> previously discussed patches. The main difference is that previous
>> patches had attempted to use something like tbinfo->attislocal to
>> determine whether a column was inherited, but that's not correct. This
>> patch uses the existing logic in flagInhAttrs() to find whether there is
>> a matching parent column with a generation expression. I've added
>> pg_dump test cases here to check the different variations that the code
>> addresses.
>
> This is a clear improvement on the current situation, and given that
> this issue is over a year old, I think we should push and back-patch
> this in time for February's releases.

done

I will continue working on the other issues that we have been discussing.

> However ... this doesn't solve all the cases noted in this thread.
> In the first example I gave at [1],
>
> d3=# create table parent (f1 int default 2);
> CREATE TABLE
> d3=# create table child (f1 int default 3) inherits(parent);
> NOTICE: merging column "f1" with inherited definition
> CREATE TABLE
> d3=# create table child2() inherits(parent);
> CREATE TABLE
> d3=# alter table child2 alter column f1 set default 42;
> ALTER TABLE
>
> pg_dump still fails to restore child2.f1's non-inherited default.
> That's probably a pre-existing problem, since it doesn't involve
> GENERATED at all, but we shouldn't forget about it.
>
> Also, in the example from [2],
>
> d3=# create table pp1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
> CREATE TABLE
> d3=# create table cc1 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
> CREATE TABLE
> d3=# alter table cc1 inherit pp1;
> ALTER TABLE
>
> pg_dump now omits to dump cc1's generation expression, which seems
> strictly worse than before. Admittedly, the backend likely ought to
> be rejecting this scenario, but it doesn't do so today.
>
> Neither of these points seem like a reason to reject this patch,
> they're just adjacent work that remains to be done.
>
> regards, tom lane
>
> [1] https://www.postgresql.org/message-id/660925.1601397436%40sss.pgh.pa.us
> [2] https://www.postgresql.org/message-id/661371.1601398006%40sss.pgh.pa.us
>
>

--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/