Re: Dumping/restoring fails on inherited generated column

pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Dumping/restoring fails on inherited generated column
Date: Fri, 29 Jan 2021 11:41:15 -0500
Message-ID: <2808494.1611938475@sss.pgh.pa.us> (raw)
In-Reply-To: <41888c80-e5bd-f9e5-75dc-fb377113168c@2ndquadrant.com>
References: <15830.1575468847@sss.pgh.pa.us>
	<fd559544-274c-f514-d8bf-358d2c539455@2ndquadrant.com>
	<31905.1575491779@sss.pgh.pa.us>
	<d6ebcc2e-3e6f-6915-6616-70ef665aed66@2ndquadrant.com>
	<24369.1580758361@sss.pgh.pa.us>
	<b1c831dd-d520-5e7f-0304-0eeed39c9996@2ndquadrant.com>
	<CA+fd4k4cHKUmEyHiPHx0QL3T-DXAP8O1CP-hUvo1WX6RwNC=tA@mail.gmail.com>
	<3f272982-0e0d-17a1-3fb6-fedc74197676@2ndquadrant.com>
	<c9848634-76c1-3c21-fc19-f1b7eebb640f@2ndquadrant.com>
	<3169466.1594841366@sss.pgh.pa.us>
	<CA+fd4k4fck6xzsF8mPWeVSZFBLtiA2cQxrPqBaYcoZGc+hZ=JQ@mail.gmail.com>
	<CA+fd4k6pLzrZDQsdsxcS06AwGRf1DgwOw84sFq9oXNw+83nB1g@mail.gmail.com>
	<e87e2efe-c820-c0d3-f7a0-0789beb78ff1@2ndquadrant.com>
	<CAD21AoCfgYKJo3cEhoUr4cKWxmSxi_gdHQDmOJ5XKC-dDc4yBw@mail.gmail.com>
	<e8e3f666-9050-1029-42cf-d0ecfdb8902e@2ndquadrant.com>
	<0f57d5e9-4112-1ba8-64d8-c02062c1e95d@postgrespro.ru>
	<41888c80-e5bd-f9e5-75dc-fb377113168c@2ndquadrant.com>

Peter Eisentraut <peter.eisentraut@2ndquadrant.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.

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

view thread (37+ messages)  latest in thread

Message-ID: <2808494.1611938475@sss.pgh.pa.us>
Permalink:  ../2808494.1611938475@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2808494.1611938475@sss.pgh.pa.us

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, peter.eisentraut@2ndquadrant.com, a.lubennikova@postgrespro.ru, sawada.mshk@gmail.com, masahiko.sawada@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
  Subject: Re: Dumping/restoring fails on inherited generated column
  In-Reply-To: <2808494.1611938475@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox