Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> You are arguing that we should provide lesser support for numeric
>> columns (and who knows how many other types) in materialized views
>> than we do in streaming replication, pg_dump,
>> suppress_redundant_updates_trigger(), and other places? Why?
>
> Because you're saying that you need SQL semantics, and probably because
> I'm not understanding well enough the problem you're trying to solve.
There are examples in the patch and this thread, but rather than
reference back to those I'll add a new one. Without the patch:
test=# CREATE TABLE nt (id serial PRIMARY KEY, grp citext, num numeric);
CREATE TABLE
test=# INSERT INTO nt (grp, num) VALUES
test-# ('one', '1.0'),
test-# ('one', '2.0'),
test-# ('two', '123.000');
INSERT 0 3
test=# CREATE VIEW nv AS SELECT grp, sum(num) AS num FROM nt GROUP BY grp;
CREATE VIEW
test=# SELECT * FROM nv ORDER BY grp;
grp | num
-----+---------
one | 3.0
two | 123.000
(2 rows)
test=# CREATE MATERIALIZED VIEW nm AS SELECT grp, sum(num) AS num FROM nt GROUP BY grp;
SELECT 2
test=# CREATE UNIQUE INDEX nm_id ON nm (grp);
CREATE INDEX
test=# SELECT * FROM nm ORDER BY grp;
grp | num
-----+---------
one | 3.0
two | 123.000
(2 rows)
test=# UPDATE nt SET grp = 'Two', num = '123.0000' WHERE id = 3;
UPDATE 1
test=# REFRESH MATERIALIZED VIEW CONCURRENTLY nm;
REFRESH MATERIALIZED VIEW
test=# SELECT * FROM nv ORDER BY grp;
grp | num
-----+----------
one | 3.0
Two | 123.0000
(2 rows)
test=# SELECT * FROM nm ORDER BY grp;
grp | num
-----+---------
one | 3.0
two | 123.000
(2 rows)
The problem, as I see it, is that the view and the concurrently
refreshed materialized view don't yield the same results for the
same query. The rows are equal, but they are not the same. With
the patch the matview, after RMVC, looks just the same as the view.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
