Re: Materialized views and unique indexes

On Fri, Mar 8, 2013 at 11:33 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

>
> > As expected, the refresh failed, but the error message is not really
> > user-friendly.
> > Shouldn't we output instead something like that?
> > ERROR: could not refresh materialized view because of failure when
> > rebuilding index"
> > DETAIL: key is duplicated.
>
> Is there a good reason to allow unique indexes (or constraints in
> general) on matviews?
>
Don't think so. It would make sense to block the creation of all the
constraints on matviews.

Just based on the docs, matviews cannot have constraints:
http://www.postgresql.org/docs/devel/static/sql-altermaterializedview.html

Now that you mention it, you can create constraints on them (code at
c805659).
postgres=# create table aa (a int);
CREATE TABLE
postgres=# create materialized view aam as select * from aa;
SELECT 0
postgres=# alter materialized view aam add constraint popo unique(a);
ALTER MATERIALIZED VIEW
postgres=# \d aam
Materialized view "public.aam"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Indexes:
"popo" UNIQUE CONSTRAINT, btree (a)

Also, as it is not mandatory for a unique index to be a constraint, I think
that we should block the creation of unique indexes too to avoid any
problems. Any suggestions?
--
Michael