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 1l7Gtb-0006yH-1r for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Feb 2021 12:04:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l7GtZ-0007Ws-NK for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Feb 2021 12:04:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l7GtY-0007VE-UW for pgsql-hackers@lists.postgresql.org; Wed, 03 Feb 2021 12:04:29 +0000 Received: from [66.111.4.239] (helo=forward5-smtp.messagingengine.com) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l7GtU-0003Fe-K5 for pgsql-hackers@lists.postgresql.org; Wed, 03 Feb 2021 12:04:28 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailforward.nyi.internal (Postfix) with ESMTP id 173B81940F91; Wed, 3 Feb 2021 07:04:13 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Wed, 03 Feb 2021 07:04:13 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; bh=px4aJdMy54bx5ciFt06+OCTvVVc43IDd+Nb6qfF0M 8c=; b=qO5xYuTmrSILDFKITaZZ2e4At/zgPhYEMbTg9T5AxD8WZhzxuQZxbyr0R a1SF2OBF7S7ELKXuOQK7DW7oLo4VK9CjmaSaa87oxYS8cAa9DfGDyk+K/VzSuZE2 Wa3ypxAsUnnGzGUBPuUgM2nWPeyFHKnRlGF6T2BuwxBkh4q+1bliJZcG3X+BMddW AnoXrB8bZoVPDAi8Bk6baJHqc9/vQgh3ZXZIysnWPbsn20yZdSz+SRstXx01nZZc GtDYr3aP8X+zUQgGuY7Xj1F+qZo6qaFgXYVSsE7qvpaduQ9qkv8d0lOFLmM6zoWz NPulC4FW9R03YkUTLjHf1wPmWcujA== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduledrgedvgdefgecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefuvfhfhfhokffffgggjggtgfesthejredttdefjeenucfhrhhomheprfgvthgv rhcugfhishgvnhhtrhgruhhtuceophgvthgvrhdrvghishgvnhhtrhgruhhtsedvnhguqh hurggurhgrnhhtrdgtohhmqeenucggtffrrghtthgvrhhnpeehgefgvdevkeffheetgfet heetudfgleeggfdtffduhfelheefgfduhfffjeetheenucffohhmrghinhepphhoshhtgh hrvghsqhhlrdhorhhgpddvnhguqhhurggurhgrnhhtrdgtohhmnecukfhppeekjedrudej jedrieelrddugedtnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilh hfrhhomhepphgvthgvrhdrvghishgvnhhtrhgruhhtsedvnhguqhhurggurhgrnhhtrdgt ohhm X-ME-Proxy: Received: from april.pezone.net (p57b1458c.dip0.t-ipconnect.de [87.177.69.140]) by mail.messagingengine.com (Postfix) with ESMTPA id D41B6108005C; Wed, 3 Feb 2021 07:04:10 -0500 (EST) Subject: Re: Dumping/restoring fails on inherited generated column To: Tom Lane 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> <2808494.1611938475@sss.pgh.pa.us> Cc: Anastasia Lubennikova , Masahiko Sawada , Masahiko Sawada , PostgreSQL Hackers From: Peter Eisentraut Organization: 2ndQuadrant Message-ID: <058495bf-3015-2963-6f12-9cd8d5a470df@2ndquadrant.com> Date: Wed, 3 Feb 2021 13:04:09 +0100 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <2808494.1611938475@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.239 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2021-01-29 17:41, Tom Lane wrote: > 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. 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/