BUG #9616: Materialized view with indexes unable to load from pg_dump - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      9616
Logged by:          Jesse Denardo
Email address:      jesse.denardo@myfarms.com
PostgreSQL version: 9.3.3
Operating system:   Arch Linux
Description:

Linux: Linux hostname 3.13.5-1-ARCH #1 SMP PREEMPT Sun Feb 23 00:25:24 CET
2014 x86_64 GNU/Linux
PostgreSQL: PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.8.2 20140206 (prerelease), 64-bit

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:

# \d example_mat_view;
Materialized view "example_mat_view"
    Column    |       Type        | Modifiers
--------------+-------------------+-----------
 f_id         | integer           |
 f_name       | character varying | collate C
 s_z          | integer[]         |
 c_id         | integer           |
Indexes:
    "example_mat_view_c_id" btree (c_id)
    "example_mat_view_f_id" btree (f_id)

Analyzing the output of pg_dump shows the following:

--
-- Name: example_mat_view; Type: TABLE; Schema: <schema>; Owner: -;
Tablespace:~
--

CREATE TABLE example_mat_view (
    f_id integer,
    f_name character varying COLLATE pg_catalog."C",
    s_z integer[],
    c_id integer
);

--
-- Name: example_mat_view_c_id; Type: INDEX; Schema: <schema>; Owner: -;
Tablespace:~
--

CREATE INDEX example_mat_view_c_id ON example_mat_view USING btree (c_id);


--
-- Name: example_mat_view_f_id; Type: INDEX; Schema: <schema>; Owner: -;
Tablespace:~
--

CREATE INDEX example_mat_view_f_id ON example_mat_view USING btree (f_id);

--
-- Name: _RETURN; Type: RULE; Schema: <schema>; Owner: -
--

CREATE RULE "_RETURN" AS
    ON SELECT TO example_mat_view DO INSTEAD  <sql query>
   ;

--
-- Name: example_mat_view; Type: MATERIALIZED VIEW DATA; Schema: <schema>;
Owner: -
--

REFRESH MATERIALIZED VIEW example_mat_view;



Attempting to restore this dump via psql gives the following errors:

ERROR:  could not convert table "example_mat_view" to a view because it has
indexes
ERROR:  "example_mat_view" is not a materialized view


My independent testing showed that pg_dump usually dumps materialized views
using CREATE MATERIALIZED VIEW, not tables that are later converted into mat
views. I confirmed that the mat view was created normally with CREATE
MATERIALIZED VIEW example_mat_view AS <sql query>;