From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Dumping/restoring fails on inherited generated column
Date: Wed, 4 Dec 2019 21:17:21 +0100
Message-ID: <fd559544-274c-f514-d8bf-358d2c539455@2ndquadrant.com> (raw)
In-Reply-To: <15830.1575468847@sss.pgh.pa.us>
References: <15830.1575468847@sss.pgh.pa.us>
On 2019-12-04 15:14, Tom Lane wrote:
> Run the regression tests with "make installcheck", then:
>
> $ pg_dump -Fc regression >r.dump
> $ createdb r2
> $ pg_restore -d r2 r.dump
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 6005; 2604 24821 DEFAULT gtest1_1 b postgres
> pg_restore: error: could not execute query: ERROR: column "b" of relation "gtest1_1" is a generated column
> Command was: ALTER TABLE ONLY public.gtest1_1 ALTER COLUMN b SET DEFAULT (a * 2);
>
>
> pg_restore: warning: errors ignored on restore: 1
> $
>
> It looks like gtest1_1 inherits column "b" from gtest1, so possibly
> pg_dump is just confused about the combination of inheritance and
> generated columns.
Yeah, there was some stuff about the "separate" dumping of defaults that
I apparently forgot to address. The attached patch fixes it. I'll see
about adding a test case for it, too.
> I see this in v12 as well as HEAD. One interesting question is how come
> the pg_upgrade test isn't failing --- maybe binary-upgrade mode handles
> this case differently?
Binary upgrade dumps out even inherited columns, so it won't run into
the "separate" case that's the issue here.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:v1-0001-Fix-dumping-of-inherited-generated-columns.patch (text/plain, 893B) download
From 0ef24c98edf468ebe906c5b04e3ddf9e53bda02f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 4 Dec 2019 21:14:19 +0100
Subject: [PATCH v1] Fix dumping of inherited generated columns
---
src/bin/pg_dump/pg_dump.c | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex 33e58fa287..87b3c5b09c 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -8515,6 +8515,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
{
attrdefs[j].separate = true;
}
+ else if (tbinfo->attgenerated[adnum - 1])+ {+ /* generated columns only go with the root table */+ attrdefs[j].separate = false;+ }
else if (!shouldPrintColumn(dopt, tbinfo, adnum - 1))
{
/* column will be suppressed, print default separately */
--
2.24.0
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: peter.eisentraut@2ndquadrant.com, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: Dumping/restoring fails on inherited generated column
In-Reply-To: <fd559544-274c-f514-d8bf-358d2c539455@2ndquadrant.com>
* 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