PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16119
PG Version9.4.25
OSLinux/Docker
Opened2019-11-15 22:16:51+00
Reported byDmitry Telpt
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16119
Logged by:          Dmitry Telpt
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-11-15 22:16:51+00PG Bug reporting formBUG #16119: pg_dump omits columns specification for matviews
2019-11-16 06:05:36+00Tom LaneRe: BUG #16119: pg_dump omits columns specification for matviews
2019-11-17 01:02:39+00Tom LaneRe: BUG #16119: pg_dump omits columns specification for matviews
2019-11-18 19:08:29+00GmailRe: BUG #16119: pg_dump omits columns specification for matviews
2019-11-19 17:47:31+00Tom LaneRe: BUG #16119: pg_dump omits columns specification for matviews
2019-11-19 20:04:00+00GmailRe: BUG #16119: pg_dump omits columns specification for matviews