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