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
From 0ef24c98edf468ebe906c5b04e3ddf9e53bda02f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@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.c
index 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
