pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16119: pg_dump omits columns specification for matviews
6+ messages / 3 participants
[nested] [flat]
* BUG #16119: pg_dump omits columns specification for matviews
@ 2019-11-15 22:16 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2019-11-15 22:16 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dmitry.telpt@gmail.com
The following bug has been logged on the website:
Bug reference: 16119
Logged by: Dmitry Telpt
Email address: dmitry.telpt@gmail.com
PostgreSQL version: 9.4.25
Operating system: Linux/Docker
Description:
Hi there,
We've faced up with an issue how pg_dump exports materialzed views, it
doesn't export columns declaration that is legal parameter of CREATE DDL
instruction.
As I can see from db_dump source, it's intent behaviour and supposed that
columns declarion will be picked up from query statement when matview is
created by target instance. However, if matview is 'static' (doesn't have a
query statement), it sets default column names (column1, column2, etc) that
breaks all dependent tables/views/matviews, for instance:
postgres=> CREATE MATERIALIZED VIEW test_mv
(id, alias) AS
VALUES (1, 5), (2, 6), (3, 17)
;
SELECT 3
postgres=> \d+ test_mv
Materialized view "public.test_mv"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
alias | integer | | plain | |
View definition:
VALUES (1,5), (2,6), (3,17);
but pg_dump produces the following DDL statement:
--
-- Name: test_mv; Type: MATERIALIZED VIEW; Schema: public; Owner: postgres
--
CREATE MATERIALIZED VIEW public.test_mv AS
VALUES (1,5), (2,6), (3,17)
WITH NO DATA;
that leads to the following declaration in target instance:
postgres=> \d+ test_mv
Materialized view "public.test_mv"
Column | Type | Modifiers | Storage | Stats target | Description
---------+---------+-----------+---------+--------------+-------------
column1 | integer | | plain | |
column2 | integer | | plain | |
View definition:
VALUES (1,5), (2,6), (3,17);
the version of engine or db_dump doesn't matter, the same behavior.
it's minor bug, since dump file may be fixed manually before restoring but
when you don't have access to file (for instance, AWS RDS upgrade
procedure), it becomes very painful.
Thanks,
Dmitry
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #16119: pg_dump omits columns specification for matviews
@ 2019-11-16 06:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2019-11-16 06:05 UTC (permalink / raw)
To: dmitry.telpt@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> We've faced up with an issue how pg_dump exports materialzed views, it
> doesn't export columns declaration that is legal parameter of CREATE DDL
> instruction.
> As I can see from db_dump source, it's intent behaviour and supposed that
> columns declarion will be picked up from query statement when matview is
> created by target instance. However, if matview is 'static' (doesn't have a
> query statement), it sets default column names (column1, column2, etc) that
> breaks all dependent tables/views/matviews, for instance:
> postgres=> CREATE MATERIALIZED VIEW test_mv
> (id, alias) AS
> VALUES (1, 5), (2, 6), (3, 17)
> ;
Hmm ... I agree this is not good, but this example doesn't seem like
a matview that anyone would create in practice. For curiosity's sake,
would you explain the actual use-case that you hit this in?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #16119: pg_dump omits columns specification for matviews
@ 2019-11-17 01:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2019-11-17 01:02 UTC (permalink / raw)
To: dmitry.telpt@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> We've faced up with an issue how pg_dump exports materialzed views, it
> doesn't export columns declaration that is legal parameter of CREATE DDL
> instruction.
I figured out what was going wrong here and pushed a fix for it.
I'm still a bit curious about the real use-case, since a materialized
view made from just a VALUES clause seems a bit pointless.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #16119: pg_dump omits columns specification for matviews
@ 2019-11-18 19:08 Gmail <dmitry.telpt@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Gmail @ 2019-11-18 19:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Tom,
Sorry for a little delay in response, there was no email access over the weekend =)
You know, everything that seems to be ugly for developer maybe 'desired' for customer and contrary in real world.
So, in our case, we're not owner or primary user of schema, only consume data that is maintained by another app and there is inter-team agreement that we can ONLY have views in their schema, that's a cause of such 'strange' solution.
Thanks for a fast feedback and great job!
Just a question, will the fix be back-ported to current supported branches like 9.4/9.5/9.6 or only included in 12?
Thank you again,
Dmitry
> On Nov 16, 2019, at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
>> We've faced up with an issue how pg_dump exports materialzed views, it
>> doesn't export columns declaration that is legal parameter of CREATE DDL
>> instruction.
>
> I figured out what was going wrong here and pushed a fix for it.
> I'm still a bit curious about the real use-case, since a materialized
> view made from just a VALUES clause seems a bit pointless.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #16119: pg_dump omits columns specification for matviews
@ 2019-11-19 17:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gmail <dmitry.telpt@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2019-11-19 17:47 UTC (permalink / raw)
To: Gmail <dmitry.telpt@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Gmail <dmitry.telpt@gmail.com> writes:
> Just a question, will the fix be back-ported to current supported branches like 9.4/9.5/9.6 or only included in 12?
I back-patched it, see
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bf2efc55da9a1a33da32fa383db9db2f2...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #16119: pg_dump omits columns specification for matviews
@ 2019-11-19 20:04 Gmail <dmitry.telpt@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Gmail @ 2019-11-19 20:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
my apology, didn't notice 'Like the previous patch, back-patch to all supported branches.' in the comment.
And it seems to have been little bit late with report, so it wasn't included into the current minor update... am I right that next minor update will happen in February?
Thank you,
Dmitry
> On Nov 19, 2019, at 9:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I back-patched it, see
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bf2efc55da9a1a33da32fa383db9db2f2...
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-11-19 20:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-15 22:16 BUG #16119: pg_dump omits columns specification for matviews PG Bug reporting form <noreply@postgresql.org>
2019-11-16 06:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-17 01:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-18 19:08 ` Gmail <dmitry.telpt@gmail.com>
2019-11-19 17:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-19 20:04 ` Gmail <dmitry.telpt@gmail.com>
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