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

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@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
>
>