Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5WqM-0001Qy-57 for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Jan 2021 16:41:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l5Wpp-0004pl-AW for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Jan 2021 16:41:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5Wpp-0004pe-3Q for pgsql-hackers@lists.postgresql.org; Fri, 29 Jan 2021 16:41:25 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5Wpi-0006yh-KP for pgsql-hackers@lists.postgresql.org; Fri, 29 Jan 2021 16:41:24 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 10TGfF4w2808495; Fri, 29 Jan 2021 11:41:15 -0500 From: Tom Lane To: Peter Eisentraut cc: Anastasia Lubennikova , Masahiko Sawada , Masahiko Sawada , PostgreSQL Hackers Subject: Re: Dumping/restoring fails on inherited generated column In-reply-to: <41888c80-e5bd-f9e5-75dc-fb377113168c@2ndquadrant.com> References: <15830.1575468847@sss.pgh.pa.us> <31905.1575491779@sss.pgh.pa.us> <24369.1580758361@sss.pgh.pa.us> <3f272982-0e0d-17a1-3fb6-fedc74197676@2ndquadrant.com> <3169466.1594841366@sss.pgh.pa.us> <0f57d5e9-4112-1ba8-64d8-c02062c1e95d@postgrespro.ru> <41888c80-e5bd-f9e5-75dc-fb377113168c@2ndquadrant.com> Comments: In-reply-to Peter Eisentraut message dated "Fri, 29 Jan 2021 14:14:48 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2808493.1611938475.1@sss.pgh.pa.us> Date: Fri, 29 Jan 2021 11:41:15 -0500 Message-ID: <2808494.1611938475@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Peter Eisentraut 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. 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