pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
5+ messages / 3 participants
[nested] [flat]

* BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
@ 2020-09-18 04:58 PG Bug reporting form <noreply@postgresql.org>
  2020-09-18 09:38 ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Daniel Gustafsson <daniel@yesql.se>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2020-09-18 04:58 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: andrewbille@gmail.com

The following bug has been logged on the website:

Bug reference:      16622
Logged by:          Andrew Bille
Email address:      andrewbille@gmail.com
PostgreSQL version: 12.4
Operating system:   Ubuntu-20.04
Description:        

When I perform dump/reload with the following inherited table:

CREATE TABLE public.gtest1 (
    a integer NOT NULL,
    b integer GENERATED ALWAYS AS ((a * 2)) STORED
);

CREATE TABLE public.gtest1_1 (
)
INHERITS (public.gtest1);

I get the error:

pg_dump postgres > dump.sql
createdb test
psql test -q -v ON_ERROR_STOP=1 -f dump.sql
psql:dump.sql:50: ERROR:  column "b" of relation "gtest1_1" is a generated
column

in dump.sql i see
...
--
-- Name: gtest1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.gtest1 (
    a integer NOT NULL,
    b integer GENERATED ALWAYS AS ((a * 2)) STORED
);


ALTER TABLE public.gtest1 OWNER TO postgres;

--
-- Name: gtest1_1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.gtest1_1 (
)
INHERITS (public.gtest1);


ALTER TABLE public.gtest1_1 OWNER TO postgres;

--
-- Name: gtest1_1 b; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.gtest1_1 ALTER COLUMN b SET DEFAULT (a * 2);
...

Reproduced on 13rc1 too.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
  2020-09-18 04:58 BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column PG Bug reporting form <noreply@postgresql.org>
@ 2020-09-18 09:38 ` Daniel Gustafsson <daniel@yesql.se>
  2020-09-21 21:20   ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Daniel Gustafsson @ 2020-09-18 09:38 UTC (permalink / raw)
  To: andrewbille@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

> On 18 Sep 2020, at 06:58, PG Bug reporting form <noreply@postgresql.org> wrote:

> psql:dump.sql:50: ERROR:  column "b" of relation "gtest1_1" is a generated
> column

I can reproduce this in git HEAD too.  Off the cuff, ISTM that we should only
dump attrdefs for local columns during non-upgrade dumps?  It works for normal
DEFAULTs but the restored db will have different :location for the attrdef.
For generated columns it errors out as shown above.  I don't really follow the
reason for why we redo the normal DEFAULT for non-local columns today, is that
intentional?

The attached, potentially naive, patch fixes the above issue and passes pg_dump
and pg_upgrade tests.

cheers ./daniel

Attachments:

0001-Skip-dumping-column-defaults-for-inherited-cols.patch (application/octet-stream, 986B) download From 674a8c2e4f2027c86b713ecf9b80dfc878a35624 Mon Sep 17 00:00:00 2001 From: Daniel Gustafsson <daniel@yesql.se> Date: Fri, 18 Sep 2020 10:05:34 +0200 Subject: [PATCH] Skip dumping column defaults for inherited cols --- src/bin/pg_dump/pg_dump.c | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index dce6af09c9..7ede31e41f 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -16296,6 +16296,15 @@ dumpAttrDef(Archive *fout, AttrDefInfo *adinfo) if (!adinfo->separate) return; + /* + * Skip if the column isn't local to the table's definition. Inherited + * columns are handled separately during binary_upgrade to ensure + * compatible column order in the relation files, so only skip for non- + * upgrade cases. + */ + if (!dopt->binary_upgrade && !tbinfo->attislocal[adnum - 1]) + return; + q = createPQExpBuffer(); delq = createPQExpBuffer(); -- 2.21.1 (Apple Git-122.3) =
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
  2020-09-18 04:58 BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column PG Bug reporting form <noreply@postgresql.org>
  2020-09-18 09:38 ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Daniel Gustafsson <daniel@yesql.se>
@ 2020-09-21 21:20   ` Tom Lane <tgl@sss.pgh.pa.us>
  2020-09-29 08:24     ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Daniel Gustafsson <daniel@yesql.se>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2020-09-21 21:20 UTC (permalink / raw)
  To: Daniel Gustafsson <daniel@yesql.se>; +Cc: andrewbille@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Daniel Gustafsson <daniel@yesql.se> writes:
>> On 18 Sep 2020, at 06:58, PG Bug reporting form <noreply@postgresql.org> wrote:
>> psql:dump.sql:50: ERROR:  column "b" of relation "gtest1_1" is a generated
>> column

> I can reproduce this in git HEAD too.

Yeah, this has been complained of before, notably at [1].
The latest patch in that thread is Masahiko-san's at [2],
which is similar to but not exactly like yours.
Could you review/test that one?

(I suspect you are right that binary-upgrade is a special case,
but wouldn't the existing pg_upgrade test catch that?  Also,
do we need more test cases?)

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/15830.1575468847%40sss.pgh.pa.us
[2] https://www.postgresql.org/message-id/CA%2Bfd4k6pLzrZDQsdsxcS06AwGRf1DgwOw84sFq9oXNw%2B83nB1g%40mail...





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
  2020-09-18 04:58 BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column PG Bug reporting form <noreply@postgresql.org>
  2020-09-18 09:38 ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Daniel Gustafsson <daniel@yesql.se>
  2020-09-21 21:20   ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-29 08:24     ` Daniel Gustafsson <daniel@yesql.se>
  2020-09-29 16:38       ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Daniel Gustafsson @ 2020-09-29 08:24 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: andrewbille@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

> On 21 Sep 2020, at 23:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Daniel Gustafsson <daniel@yesql.se> writes:
>>> On 18 Sep 2020, at 06:58, PG Bug reporting form <noreply@postgresql.org> wrote:
>>> psql:dump.sql:50: ERROR:  column "b" of relation "gtest1_1" is a generated
>>> column
> 
>> I can reproduce this in git HEAD too.
> 
> Yeah, this has been complained of before, notably at [1].
> The latest patch in that thread is Masahiko-san's at [2],
> which is similar to but not exactly like yours.
> Could you review/test that one?

Aha, thanks for pointing it out, I had missed that thread.  Looking at the
patch in that thread, we have come to the same conclusion independently.  Both
patches handle attrdefs from generated columms in the same way, they do however
differ for other non-local attrdefs.  Consider the following:

  CREATE TABLE public.gtest2 (b integer DEFAULT 2);
  CREATE TABLE public.gtest2_2 () INHERITS (public.gtest2);

Masahiko-san's patch (and current HEAD) will dump the following (blank rows
omitted for brevity), as will HEAD.  Note the ALTER TABLE ..  SET DEFAULT at
the end.

  CREATE TABLE public.gtest2 (
      b integer DEFAULT 2
  );
  ALTER TABLE public.gtest2 OWNER TO dgustafsson;
  CREATE TABLE public.gtest2_2 (
  )
  INHERITS (public.gtest2);
  ALTER TABLE public.gtest2_2 OWNER TO dgustafsson;
  ALTER TABLE ONLY public.gtest2_2 ALTER COLUMN b SET DEFAULT 2;

Is there is a reason for performing that step, it will drop the existing
attrdef and install an exact copy.  The attached path will dump the same
sequence of operations but without the SET DEFAULT command:

  CREATE TABLE public.gtest2 (
      b integer DEFAULT 2
  );
  ALTER TABLE public.gtest2 OWNER TO dgustafsson;
  CREATE TABLE public.gtest2_2 (
  )
  INHERITS (public.gtest2);
  ALTER TABLE public.gtest2_2 OWNER TO dgustafsson;

Is there a case when the ALTER TABLE ..  SET DEFAULT command is required to
recreate the scheman for non-local attrdefs?  If so, then Maasahiko-san's patch
is where this conversation should continue.

> (I suspect you are right that binary-upgrade is a special case,
> but wouldn't the existing pg_upgrade test catch that?  Also,
> do we need more test cases?)

binary-upgrade is a special case, but one which is already handled by setting
.separate via shouldPrintColumn for attrdefs.  This means that we don't need to
account for this in dumpAttrDef.

cheers ./daniel

Attachments:

0001-Skip-dumping-column-defaults-for-inherited-cols-v2.patch (application/octet-stream, 870B) download From dfbd3a74cfa4369bce39de14cf94ff3b0f622b9a Mon Sep 17 00:00:00 2001 From: Daniel Gustafsson <daniel@yesql.se> Date: Fri, 18 Sep 2020 10:05:34 +0200 Subject: [PATCH] Skip dumping column defaults for inherited cols v2 --- src/bin/pg_dump/pg_dump.c | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 76320468ba..e9998cf0cf 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -16285,6 +16285,13 @@ dumpAttrDef(Archive *fout, AttrDefInfo *adinfo) if (!adinfo->separate) return; + /* + * Skip if the column isn't local to the table's definition as the attrdef + * will be printed with the inheritance parent table definition + */ + if (!tbinfo->attislocal[adnum - 1]) + return; + q = createPQExpBuffer(); delq = createPQExpBuffer(); -- 2.21.1 (Apple Git-122.3) =
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column
  2020-09-18 04:58 BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column PG Bug reporting form <noreply@postgresql.org>
  2020-09-18 09:38 ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Daniel Gustafsson <daniel@yesql.se>
  2020-09-21 21:20   ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Tom Lane <tgl@sss.pgh.pa.us>
  2020-09-29 08:24     ` Re: BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column Daniel Gustafsson <daniel@yesql.se>
@ 2020-09-29 16:38       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2020-09-29 16:38 UTC (permalink / raw)
  To: Daniel Gustafsson <daniel@yesql.se>; +Cc: andrewbille@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

Leaving a bread-crumb for anyone searching the archives in future:
I've forcibly unified this thread into the older one at

https://www.postgresql.org/message-id/flat/15830.1575468847%40sss.pgh.pa.us

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2020-09-29 16:38 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-18 04:58 BUG #16622: pg_dump produces erroneus ALTER TABLE statement for a table with an inherited generated column PG Bug reporting form <noreply@postgresql.org>
2020-09-18 09:38 ` Daniel Gustafsson <daniel@yesql.se>
2020-09-21 21:20   ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-29 08:24     ` Daniel Gustafsson <daniel@yesql.se>
2020-09-29 16:38       ` Tom Lane <tgl@sss.pgh.pa.us>

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