On 2013-09-23 21:21:53 -0400, Stephen Frost wrote: > Here's an example to illustrate what I'm talking about when it comes > down to "you can't claim that you'll produce exactly what the query > will always, with these types:" > > =# table citext_table; > id | name > ----+------- > 1 | one > 3 | three > 5 | > 4 | Three > 2 | Three > (5 rows) > > =# create MATERIALIZED VIEW citext_matview AS select name from citext_table > where id > 0 group by name; > SELECT 3 > > =# table citext_matview; > name > ------- > > one > three > (3 rows)
I don't really understand why you have a problem with this specific thing here. Kevin's goal seems to be to make materialized views behave consistent with the way a plain view would if the matviews would just have been refreshed fully, concurrently or incrementally and there have been no further data changes. SELECT * FROM table WHERE candidate_key IN (...); used in a view or plainly currently guarantees you that you get the original casing for citext. And if you e.g. have some additional expensive joins, such a matview can very well make sense. What does it matter that ... GROUP BY citext_col; doesn't return the same casing consistently? You're aggregating, not accessing the original data. If you need to separate the different casings now, cast to text. Now, I can perfectly understand having a bit of architectural qualms about Kevin's approach of things on the SQL level. But this doesn't seem to be the thread to discuss that. FWIW, I can't forsee any realistic approach that actually won't do such comparisons (although not necessarily through C). Greetings, Andres Freund -- Andres Freund 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