On Mon, Sep 23, 2013 at 9:21 PM, Stephen Frost <sfrost@snowman.net> 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:"
Your example demonstrates that if a given query can generate two
different outputs, A and B, based on the same input data, then the
contents of the materialized view cannot be equal to be A and also
equal to B. Well, no duh.
Of course, you don't need citext, or any other data type with a loose
notion of equality, to generate that sort of problem:
rhaas=# create table chicken_little (d date);
CREATE TABLE
rhaas=# insert into chicken_little values ('2012-02-21');
INSERT 0 1
rhaas=# create materialized view henny_penny as select d::text from
chicken_little;
SELECT 1
rhaas=# table chicken_little; d
------------2012-02-21
(1 row)
rhaas=# table henny_penny; d
------------2012-02-21
(1 row)
rhaas=# set datestyle = 'german';
SET
rhaas=# table chicken_little; d
------------21.02.2012
(1 row)
rhaas=# table henny_penny; d
------------2012-02-21
(1 row)
But I'm still wondering what this is intended to prove. There are an
infinite number of ways to write queries that produce different
results, and I think we all know that materialized views aren't going
to hold up very well if given such queries. That seems a poor excuse
for not fixing the cases that can be made to work.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company