Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iyhSk-0001Mk-6W for pgsql-hackers@arkaria.postgresql.org; Mon, 03 Feb 2020 19:32:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iyhSi-0005q7-VB for pgsql-hackers@arkaria.postgresql.org; Mon, 03 Feb 2020 19:32:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iyhSi-0005q0-M8 for pgsql-hackers@lists.postgresql.org; Mon, 03 Feb 2020 19:32:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iyhSf-0003Ee-RI for pgsql-hackers@lists.postgresql.org; Mon, 03 Feb 2020 19:32:48 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 013JWfmT024370; Mon, 3 Feb 2020 14:32:42 -0500 From: Tom Lane To: Peter Eisentraut cc: pgsql-hackers@lists.postgresql.org Subject: Re: Dumping/restoring fails on inherited generated column In-reply-to: References: <15830.1575468847@sss.pgh.pa.us> <31905.1575491779@sss.pgh.pa.us> Comments: In-reply-to Peter Eisentraut message dated "Mon, 03 Feb 2020 19:58:43 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <24368.1580758361.1@sss.pgh.pa.us> Date: Mon, 03 Feb 2020 14:32:41 -0500 Message-ID: <24369.1580758361@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Peter Eisentraut writes: > On 2019-12-04 21:36, Tom Lane wrote: >> I wonder if the right fix is to not generate a DO_ATTRDEF >> object at all for generated columns in child tables. Am >> I right in guessing that we propagate generated-ness to >> child tables automatically? > Right. New patch using that approach attached. (Could use more > extensive comments.) This looks more plausible than the previous attempt, but it's clearly still not right, because this is what it changes in the regression test dump: --- r.dump.head 2020-02-03 14:16:15.774305437 -0500 +++ r.dump.patch 2020-02-03 14:18:08.599109703 -0500 @@ -15244,14 +15244,7 @@ -- Name: gtest1_1 b; Type: DEFAULT; Schema: public; Owner: postgres -- -ALTER TABLE ONLY public.gtest1_1 ALTER COLUMN b SET DEFAULT (a * 2); - - --- --- Name: gtest30_1 b; Type: DEFAULT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY public.gtest30_1 ALTER COLUMN b SET DEFAULT (a * 2); +ALTER TABLE ONLY public.gtest1_1 ALTER COLUMN b SET DEFAULT NULL; -- This is showing us at least two distinct problems. Now as for "gtest30_1", what we have is that in the parent table "gtest30", column b exists but it has no default; the generated property is only added at the child table gtest30_1. So we need to emit ALTER COLUMN SET GENERATED ALWAYS for gtest30_1.b. HEAD is already doing the wrong thing there (it's emitting the expression, but as a plain default not GENERATED). And this patch makes it emit nothing, even worse. I think the key point here is that "attislocal" refers to whether the column itself is locally defined, not to whether its default is. Things are evidently also going wrong for "gtest1_1". In that case the generated property is inherited from the parent gtest1, so we shouldn't be emitting anything ... how come the patch fails to make it do that? regards, tom lane