Re: record identical operator - Mailing list pgsql-hackers

On Tue, Sep 24, 2013 at 12:00 PM, Stephen Frost <sfrost@snowman.net> wrote:
> It wouldn't address my concerns anyway, which are around these binary
> operators and the update-in-place approach being risky and setting us up
> for problems down the road.

I think that if you want to hold up a bug fix to a feature that's
already committed, you need to be more specific than to say that there
might be problems down the road.  I can't see that you've identified
any consequence of this change that is clearly bad.  I understand that
you don't like the idea of making binary-comparison operators
user-visible due to the risk of user-confusion, but we have lots of
confusing features already and we handle that by documenting them.
This one doesn't seem any worse than average; in fact, to me it seems
rather minor.

At any rate, I think we're getting distracted from the real point
here.  Here's what this patch is attempting to fix:

rhaas=# create table sales (txn_date date, amount numeric);
CREATE TABLE
rhaas=# insert into sales values ('2013-09-01', '100.00'),
('2013-09-01', '150.00'), ('2013-09-02', '75.0');
INSERT 0 3
rhaas=# create materialized view sales_summary as select txn_date,
sum(amount) as amount from sales group by 1;
SELECT 2
rhaas=# create unique index on sales_summary (txn_date);
CREATE INDEX
rhaas=# select * from sales_summary;          txn_date  | amount
------------+--------2013-09-01 | 250.002013-09-02 |   75.0
(2 rows)

rhaas=# update sales set amount = round(amount, 2);
UPDATE 3
rhaas=# refresh materialized view concurrently sales_summary;
REFRESH MATERIALIZED VIEW
rhaas=# select * from sales_summary; txn_date  | amount
------------+--------2013-09-01 | 250.002013-09-02 |   75.0
(2 rows)

rhaas=# refresh materialized view sales_summary;
REFRESH MATERIALIZED VIEW
rhaas=# select * from sales_summary; txn_date  | amount
------------+--------2013-09-01 | 250.002013-09-02 |  75.00
(2 rows)

Note that, after we change the data in the underlying table, the
output of the query changes.  But REFRESH MATERIALIZED VIEW
CONCURRENTLY doesn't fix it.  However, REFRESH MATERIALIZED VIEW
without CONCURRENTLY does fix it.  That's a bug, because if there are
two ways of refreshing a materialized view they should both produce
the same answer.  Shouldn't they?  The fact that users can write
queries that don't always give the same answer is not a reason why
it's OK for REFRESH CONCURRENTLY to misbehave on queries that do.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company