Re: BUG #9616: Materialized view with indexes unable to load from pg_dump

Jesse Denardo <jesse(dot)denardo(at)myfarms(dot)com> writes:
> [ pg_dump fails to dump this sanely: ]

> CREATE SCHEMA test_mat;
> CREATE TABLE test_mat.a (
> id integer,
> name character varying
> );
> ALTER TABLE ONLY test_mat.a ADD CONSTRAINT a_pkey PRIMARY KEY (id);
> CREATE MATERIALIZED VIEW test_mat.mat AS (
> SELECT id, name FROM test_mat.a GROUP BY id
> );

OK, the reason why this is odd is that the view definition requires that
a.id be a primary key. Because primary keys are supposed to be emitted
in the post-data section of the dump, while the CREATE MAT VIEW command
will be emitted pre-data, we have a dump-object sorting failure, which
manifests as a dependency loop involving the matview, its rule, a's
primary key index, and the section boundary pseudo-objects.

The code in pg_dump_sort.c assumes that any relation with an ON SELECT
rule must be a regular view, and so it tries to break the loop by
splitting the view into a CREATE TABLE command followed by a CREATE RULE
command. Of course, that results in SQL that recreates a plain view not a
matview; not to mention your original complaint that it fails when there
are indexes on the matview.

I've developed the attached draft patch that fixes this by allowing the
CREATE MATERIALIZED VIEW command to be postponed into the post-data
section. I think this is pretty darn ugly, since people won't expect such
a categorization, but I'm not sure we have any choice so far as existing
branches are concerned.

If we had a CREATE OR REPLACE MATERIALIZED VIEW type of command, we could
imagine fixing this by emitting a dummy create command in the pre-data
section, along the lines of

CREATE MATERIALIZED VIEW mv AS SELECT
NULL::coltype1 AS colname1,
NULL::coltype2 AS colname2,
...
WITH NO DATA;

and then overwriting that with CREATE OR REPLACE MATERIALIZED VIEW at the
point where it's safe to give the real view definition. (If we were to
write code for that, I'd be pretty inclined to change the dumping of
separated views to look similar, instead of the hack they are now.)

Of course, you could argue that that wouldn't be materially less confusing
than the other way. But I suspect we are going to end up doing something
of the sort anyhow, because I have little faith that the technique in this
patch will fix every case of circularities involving matviews.

Comments? Anybody want to try to fix this in another way?

regards, tom lane