Re: [HACKERS] Materialized views and unique indexes
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
Re: [HACKERS] Materialized views and unique indexes
On Fri, Mar 8, 2013 at 11:33 AM, Josh Berkus j...@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
Re: [HACKERS] Materialized views and unique indexes
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
Re: [HACKERS] Materialized views and unique indexes
Craig Ringer cr...@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
Re: [HACKERS] Materialized views and unique indexes
On Fri, Mar 8, 2013 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@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
