BUG #9616: Materialized view with indexes unable to load from pg_dump
От | jesse.denardo@myfarms.com |
---|---|
Тема | BUG #9616: Materialized view with indexes unable to load from pg_dump |
Дата | |
Msg-id | 20140318075427.20722.65161@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9616: Materialized view with indexes unable to load from pg_dump
|
Список | 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>;
В списке pgsql-bugs по дате отправления: