Interval type problem

[nuko-yokohama]

Use SELECT to search a table containing an Interval type, and set CREATE INCREMENTAL MATERIALIZED VIEW. After that, if an INSERT is executed for that table, an error occurs in the internal management table of IVM.

Execution exsample.

$ psql -U postgres testdb -e -f agg_interval.sql
DROP TABLE IF EXISTS table_a CASCADE;
psql:agg_interval.sql:1: NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view table_a_v
drop cascades to materialized view table_a_mv
drop cascades to materialized view table_a_ivm
DROP TABLE
CREATE TABLE table_a (id int primary key, data interval, dummy text);
CREATE TABLE
INSERT INTO table_a VALUES (generate_series(1, 100000), ( ROUND(random()::numeric,3)::text || ' year')::interval, repeat(' ', 80));
INSERT 0 100000
CREATE VIEW table_a_v AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
CREATE VIEW
CREATE MATERIALIZED VIEW table_a_mv AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
SELECT 1
CREATE INCREMENTAL MATERIALIZED VIEW table_a_ivm AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
SELECT 1
                  List of relations
 Schema |    Name     |       Type        |  Owner
--------+-------------+-------------------+----------
 public | table_a     | table             | postgres
 public | table_a_ivm | materialized view | postgres
 public | table_a_mv  | materialized view | postgres
 public | table_a_v   | view              | postgres
(4 rows)

SELECT 'v' as "view type", * FROM table_a_v
UNION
SELECT 'mv' as "view type", * FROM table_a_mv
UNION
SELECT 'imv' as "view type", * FROM table_a_ivm
;
 view type | count  |  max   |   min    |        sum         |            avg
-----------+--------+--------+----------+--------------------+----------------------------
 v         | 100000 | 1 year | 00:00:00 | 45894 years 3 mons | 5 mons 15 days 05:15:47.52
 imv       | 100000 | 1 year | 00:00:00 | 45894 years 3 mons | 5 mons 15 days 05:15:47.52
 mv        | 100000 | 1 year | 00:00:00 | 45894 years 3 mons | 5 mons 15 days 05:15:47.52
(3 rows)

INSERT INTO table_a VALUES (generate_series(100001, 100010), ( ROUND(random()::numeric, 3)::text || ' year')::interval, repeat(' ', 80));
psql:agg_interval.sql:16: ERROR:  COALESCE types interval and integer cannot be matched
LINE 1: ...vm_count_sum__ = 0 THEN NULL ELSE COALESCE(mv.sum,0) + COALE...
                                                             ^
QUERY:  WITH updt AS (  UPDATE public.table_a_ivm AS mv SET __ivm_count__ = mv.__ivm_count__ + diff.__ivm_count__, count = mv.count + diff.count, max = CASE WHEN mv.__ivm_count_max__ = 0 AND diff.__ivm_count_max__ = 0 THEN NULL ELSE greatest(mv.max,diff.max) END, __ivm_count_max__ = mv.__ivm_count_max__ + diff.__ivm_count_max__, min = CASE WHEN mv.__ivm_count_min__ = 0 AND diff.__ivm_count_min__ = 0 THEN NULL ELSE least(mv.min,diff.min) END, __ivm_count_min__ = mv.__ivm_count_min__ + diff.__ivm_count_min__, sum = CASE WHEN mv.__ivm_count_sum__ = 0 AND diff.__ivm_count_sum__ = 0 THEN NULL ELSE COALESCE(mv.sum,0) + COALESCE(diff.sum, 0) END, __ivm_count_sum__ = mv.__ivm_count_sum__ + diff.__ivm_count_sum__, avg = CASE WHEN mv.__ivm_count_avg__ = 0 AND diff.__ivm_count_avg__ = 0 THEN NULL ELSE (COALESCE(mv.__ivm_sum_avg__,0)+ COALESCE(diff.__ivm_sum_avg__, 0))::interval / (mv.__ivm_count_avg__ + diff.__ivm_count_avg__) END, __ivm_sum_avg__ = COALESCE(mv.__ivm_sum_avg__,0) + COALESCE(diff.__ivm_sum_avg__, 0), __ivm_count_avg__ = mv.__ivm_count_avg__ + diff.__ivm_count_avg__   FROM pg_temp_3.pg_temp_18506 AS diff WHERE (1) = (1)  RETURNING 1) INSERT INTO public.table_a_ivm (SELECT * FROM pg_temp_3.pg_temp_18506 AS diff WHERE (1) NOT IN (SELECT 1 FROM updt));

Execution SQL text.

DROP TABLE IF EXISTS table_a CASCADE;
CREATE TABLE table_a (id int primary key, data interval, dummy text);
INSERT INTO table_a VALUES (generate_series(1, 100000), ( ROUND(random()::numeric,3)::text || ' year')::interval, repeat(' ', 80));
CREATE VIEW table_a_v AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
CREATE MATERIALIZED VIEW table_a_mv AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
CREATE INCREMENTAL MATERIALIZED VIEW table_a_ivm AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;

\d
SELECT 'v' as "view type", * FROM table_a_v
UNION
SELECT 'mv' as "view type", * FROM table_a_mv
UNION
SELECT 'imv' as "view type", * FROM table_a_ivm
;

INSERT INTO table_a VALUES (generate_series(100001, 100010), ( ROUND(random()::numeric, 3)::text || ' year')::interval, repeat(' ', 80));
REFRESH MATERIALIZED VIEW table_a_mv;

\d
SELECT 'v' as "view type", * FROM table_a_v
UNION
SELECT 'mv' as "view type", * FROM table_a_mv
UNION
SELECT 'imv' as "view type", * FROM table_a_ivm
;

[yugo-n]

I fixed this and confirmed that your test case was passed.

===

issue25=# CREATE TABLE table_a (id int primary key, data interval, dummy text);
CREATE TABLE
issue25=# INSERT INTO table_a VALUES (generate_series(1, 100000), ( ROUND(random()::numeric,3)::text || ' year')::interval, repeat(' ', 80));
INSERT 0 100000
issue25=# CREATE VIEW table_a_v AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
CREATE VIEW
issue25=# CREATE MATERIALIZED VIEW table_a_mv AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
SELECT 1
issue25=# CREATE INCREMENTAL MATERIALIZED VIEW table_a_ivm AS SELECT COUNT(id), MAX(data), MIN(data), SUM(data), AVG(data) FROM table_a;
SELECT 1
issue25=# 
issue25=# \d
                 List of relations
 Schema |    Name     |       Type        | Owner  
--------+-------------+-------------------+--------
 public | table_a     | table             | yugo-n
 public | table_a_ivm | materialized view | yugo-n
 public | table_a_mv  | materialized view | yugo-n
 public | table_a_v   | view              | yugo-n
(4 rows)

issue25=# SELECT 'v' as "view type", * FROM table_a_v
issue25-# UNION
issue25-# SELECT 'mv' as "view type", * FROM table_a_mv
issue25-# UNION
issue25-# SELECT 'imv' as "view type", * FROM table_a_ivm
issue25-# ;
 view type | count  |  max   |   min    |        sum         |            avg             
-----------+--------+--------+----------+--------------------+----------------------------
 imv       | 100000 | 1 year | 00:00:00 | 45743 years 2 mons | 5 mons 14 days 16:12:34.56
 mv        | 100000 | 1 year | 00:00:00 | 45743 years 2 mons | 5 mons 14 days 16:12:34.56
 v         | 100000 | 1 year | 00:00:00 | 45743 years 2 mons | 5 mons 14 days 16:12:34.56
(3 rows)

issue25=# 
issue25=# INSERT INTO table_a VALUES (generate_series(100001, 100010), ( ROUND(random()::numeric, 3)::text || ' year')::interval, repeat(' ', 80));
INSERT 0 10
issue25=# REFRESH MATERIALIZED VIEW table_a_mv;
REFRESH MATERIALIZED VIEW
issue25=# 
issue25=# \d
                 List of relations
 Schema |    Name     |       Type        | Owner  
--------+-------------+-------------------+--------
 public | table_a     | table             | yugo-n
 public | table_a_ivm | materialized view | yugo-n
 public | table_a_mv  | materialized view | yugo-n
 public | table_a_v   | view              | yugo-n
(4 rows)

issue25=# SELECT 'v' as "view type", * FROM table_a_v
issue25-# UNION
issue25-# SELECT 'mv' as "view type", * FROM table_a_mv
issue25-# UNION
issue25-# SELECT 'imv' as "view type", * FROM table_a_ivm
issue25-# ;
 view type | count  |  max   |   min    |        sum         |             avg              
-----------+--------+--------+----------+--------------------+------------------------------
 imv       | 100010 | 1 year | 00:00:00 | 45746 years 7 mons | 5 mons 14 days 16:06:34.5312
 mv        | 100010 | 1 year | 00:00:00 | 45746 years 7 mons | 5 mons 14 days 16:06:34.5312
 v         | 100010 | 1 year | 00:00:00 | 45746 years 7 mons | 5 mons 14 days 16:06:34.5312
(3 rows)

[yugo-n]

@nuko-yokohama Could you please confirm this and close this issue if there is no problem?

[nuko-yokohama]

I confirmed that it was fixed. Thank you for the response.

[yugo-n]

Thank you. I'll close this.