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

From: Jesse Denardo <jesse(dot)denardo(at)myfarms(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #9616: Materialized view with indexes unable to load from pg_dump
Date: 2014-03-19 08:20:21
Message-ID: CANiVXAgUgW9HX3wtMCrQeD3sdM6brW5qQyWdSWuZ_qoQGQB_-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> jesse(dot)denardo(at)myfarms(dot)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
>
>

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sandro Santilli 2014-03-19 08:57:02 Re: Leaking regexp_replace in 9.3.1 ? (was: [HACKERSUninterruptable regexp_replace in 9.3.1 ?)
Previous Message Sandro Santilli 2014-03-19 07:53:59 Re: Leaking regexp_replace in 9.3.1 ? (was: [HACKERSUninterruptable regexp_replace in 9.3.1 ?)