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

From: jesse(dot)denardo(at)myfarms(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #9616: Materialized view with indexes unable to load from pg_dump
Date: 2014-03-18 07:54:27
Message-ID: 20140318075427.20722.65161@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 9616
Logged by: Jesse Denardo
Email address: jesse(dot)denardo(at)myfarms(dot)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>;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message kukinek1 2014-03-18 09:40:32 BUG #9619: error creating plperl , plperlu language , plperl.dll error
Previous Message Tom Lane 2014-03-18 05:15:20 Re: Re: BUG #9606: pg_class relhaspkey column not updated on removal of primary key