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

jesse.denardo@myfarms.com writes:

I have created several materialized views on one database. To copy data to a
new database, I dump the schema(s) of choice using pg_dump and pipe the
result to psql. The exact command looks like:

$ pg_dump -b -n <schema> -O -x -h localhost -U <user> <db> | psql -h <other
host> -d <other db> -U <other user>

The materialized views get copied and restored just fine with one exception:
The one mat view that has indexes. Here is the result of \d on this mat
view:

I can't reproduce this here; it seems clear that there's some triggering
condition other than the mere presence of indexes. Could you submit a
*self contained* example (preferably, a SQL script that makes a database
that pg_dump misbehaves on)?

The output looks a little bit like pg_dump might be trying to break some
circular dependency involving the matview. But it's not obvious how
you'd have gotten into such a situation, and we certainly haven't been
shown all the moving parts.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I've managed to consistently reproduce the error using a condensed script:

File test_mat.sql:

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
);

Execute the following commands to create, dump, drop, and reload the schema:

$ psql -d db -f test_mat.sql
$ pg_dump -n test_mat db > test_mat_dump.sql (notice how the dump creates
"mat" as CREATE TABLE rather than CREATE MATERIALIZED VIEW)
$ psql -d db -c "DROP SCHEMA test_mat CASCADE"
$ psql -d db -f test_mat_dump.sql

This last command produces:

psql:test_mat_dump.sql:82: ERROR: "mat" is not a table or materialized view

"mat" ends up being a view, rather than a materialized view.

If I add the following line to the end of test_mat.sql and repeat the
entire procedure, I get the error I described earlier:

CREATE INDEX test_idx ON test_mat.mat (id);

Produces:

psql:test_mat_dump.sql:82: ERROR: could not convert table "mat" to a view
because it has indexes
psql:test_mat_dump.sql:89: ERROR: "mat" is not a materialized view

--
Jesse Denardo

On Tue, Mar 18, 2014 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jesse Denardo <jesse.denardo@myfarms.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