Thread: Materialized views and unique indexes
Hi all,<br /><br />While testing materialized views, I found the following behavior with unique indexes:<br />postgres=#create table aa as select generate_series(1,3) as a;<br />SELECT 3<br />postgres=# create materialized view aamas select * from aa;<br /> SELECT 3<br />postgres=# create unique index aam_ind on aam(a);<br />CREATE INDEX<br />postgres=#insert into aa values (4);<br />INSERT 0 1<br />postgres=# insert into aa values (1);<br />INSERT 0 1<br />postgres=#refresh materialized view aam;<br /> ERROR: could not create unique index "aam_ind"<br />DETAIL: Key (a)=(1)is duplicated.<br />postgres=# select * from aam;<br /> a <br />---<br /> 1<br /> 2<br /> 3<br />(3 rows)<br /><br/>As expected, the refresh failed, but the error message is not really user-friendly. <br /> Shouldn't we output insteadsomething like that?<br />ERROR: could not refresh materialized view because of failure when rebuilding index"<br/>DETAIL: key is duplicated.<br /><br />Thanks,<br />-- <br />Michael<br />
> 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? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Fri, Mar 8, 2013 at 11:33 AM, Josh Berkus <josh@agliodbs.com> wrote:
-- Is there a good reason to allow unique indexes (or constraints in
> 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.
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).
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?
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
On 03/08/2013 10:55 AM, Michael Paquier wrote: > 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? How much does the planner benefit from the implied constraint of a unique index? I almost wonder if it should be allowed at the cost of making the refresh of a matview that fails to comply an error. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndquadrant.com> writes:
> On 03/08/2013 10:55 AM, Michael Paquier wrote:
>> 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?
> How much does the planner benefit from the implied constraint of a
> unique index? I almost wonder if it should be allowed at the cost of
> making the refresh of a matview that fails to comply an error.
A unique constraint can allow join elimination, so I'm thinking that
disallowing them is a bad idea (not to mention that it'd be a
considerable wart in the code to block them for matviews only).
regards, tom lane
On Fri, Mar 8, 2013 at 12:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
-- Craig Ringer <craig@2ndquadrant.com> writes:A unique constraint can allow join elimination, so I'm thinking that
> On 03/08/2013 10:55 AM, Michael Paquier wrote:
>> 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?
> How much does the planner benefit from the implied constraint of a
> unique index? I almost wonder if it should be allowed at the cost of
> making the refresh of a matview that fails to comply an error.
disallowing them is a bad idea (not to mention that it'd be a
considerable wart in the code to block them for matviews only).
Fair argument.
The error message at refresh step should be more explicit though. I still have the feeling that users might be lost if a constraint introduced on matviews is failing during refresh with the current error message.
The error message at refresh step should be more explicit though. I still have the feeling that users might be lost if a constraint introduced on matviews is failing during refresh with the current error message.
Michael