Materialized views and unique indexes
Hi all,
While testing materialized views, I found the following behavior with
unique indexes:
postgres=# create table aa as select generate_series(1,3) as a;
SELECT 3
postgres=# create materialized view aam as select * from aa;
SELECT 3
postgres=# create unique index aam_ind on aam(a);
CREATE INDEX
postgres=# insert into aa values (4);
INSERT 0 1
postgres=# insert into aa values (1);
INSERT 0 1
postgres=# refresh materialized view aam;
ERROR: could not create unique index "aam_ind"
DETAIL: Key (a)=(1) is duplicated.
postgres=# select * from aam;
a
---
1
2
3
(3 rows)
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.
Thanks,
--
Michael
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 8, 2013 at 11:33 AM, Josh Berkus <josh@agliodbs.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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 8, 2013 at 12:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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).
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.
--
Michael