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