search

sraoss / pgsql-ivm

IVM (Incremental View Maintenance) development for PostgreSQL
Other
127 stars 12 forks source link

SELECT statement that is not IMMUTABLE must not be specified when creating a view. #58

Closed nuko-yokohama closed 4 years ago

nuko-yokohama commented 4 years ago

An expression SELECT statement that is not IMMUTABLE must not be specified when creating a view.

In the current implementation, a SELECT statement containing an expression that is not IMMUTABLE can be specified when creating a view. If an incremental materialized view is created from a SELECT statement that contains an expression that is not IMMUTABLE, applying the SELECT statement to the view returns incorrect results. To prevent this, we propose that the same error occur when a non-IMMUTABLE expression is specified in the "CREATE INDEX" statement.

The following is an inappropriate example.

$ psql -U postgres testdb -e -f test/ivm/timeshit.sql
SELECT version();
                                                                        version

-------------------------------------------------------------------------------------------------------------------------------------------------------
-
 PostgreSQL 13devel-ivm-27e3925b1691bc48560337a2e24e5f84f09e0d69 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit
(1 row)

DROP TABLE IF EXISTS base CASCADE;
psql:test/ivm/timeshit.sql:2: NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view base_v
drop cascades to materialized view base_mv
drop cascades to materialized view base_imv
DROP TABLE
CREATE TABLE base (id int primary key, data text, ts timestamp);
CREATE TABLE
CREATE VIEW base_v AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
CREATE VIEW
CREATE MATERIALIZED VIEW base_mv AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
SELECT 0
CREATE INCREMENTAL MATERIALIZED VIEW base_imv AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
SELECT 0
                                      View "public.base_v"
 Column |            Type             | Collation | Nullable | Default | Storage  | Description
--------+-----------------------------+-----------+----------+---------+----------+-------------
 id     | integer                     |           |          |         | plain    |
 data   | text                        |           |          |         | extended |
 ts     | timestamp without time zone |           |          |         | plain    |
View definition:
 SELECT base.id,
    base.data,
    base.ts
   FROM base
  WHERE base.ts >= (now() - '00:00:03'::interval);

                                      Materialized view "public.base_mv"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              |
 data   | text                        |           |          |         | extended |              |
 ts     | timestamp without time zone |           |          |         | plain    |              |
View definition:
 SELECT base.id,
    base.data,
    base.ts
   FROM base
  WHERE base.ts >= (now() - '00:00:03'::interval);
Access method: heap

                                         Materialized view "public.base_imv"
    Column     |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
---------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id            | integer                     |           |          |         | plain    |              |
 data          | text                        |           |          |         | extended |              |
 ts            | timestamp without time zone |           |          |         | plain    |              |
 __ivm_count__ | bigint                      |           |          |         | plain    |              |
View definition:
 SELECT base.id,
    base.data,
    base.ts
   FROM base
  WHERE base.ts >= (now() - '00:00:03'::interval);
Access method: heap
Incremental view maintenance: yes

INSERT INTO base VALUES (generate_series(1,3), 'dummy', clock_timestamp());
INSERT 0 3
SELECT * FROM base_v ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

SELECT * FROM base_mv ORDER BY id;
 id | data | ts
----+------+----
(0 rows)

REFRESH MATERIALIZED VIEW base_mv;
REFRESH MATERIALIZED VIEW
SELECT * FROM base_mv ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

SELECT * FROM base_imv ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

SELECT pg_sleep(3);
 pg_sleep
----------

(1 row)

INSERT INTO base VALUES (generate_series(4,6), 'dummy', clock_timestamp());
INSERT 0 3
SELECT * FROM base_v ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  4 | dummy | 2019-12-22 11:38:29.381414
  5 | dummy | 2019-12-22 11:38:29.381441
  6 | dummy | 2019-12-22 11:38:29.381444
(3 rows)

SELECT * FROM base_mv ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

REFRESH MATERIALIZED VIEW base_mv;
REFRESH MATERIALIZED VIEW
SELECT * FROM base_mv ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  4 | dummy | 2019-12-22 11:38:29.381414
  5 | dummy | 2019-12-22 11:38:29.381441
  6 | dummy | 2019-12-22 11:38:29.381444
(3 rows)

SELECT * FROM base_imv ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
  4 | dummy | 2019-12-22 11:38:29.381414
  5 | dummy | 2019-12-22 11:38:29.381441
  6 | dummy | 2019-12-22 11:38:29.381444
(6 rows)

REFRESH MATERIALIZED VIEW base_mv;
REFRESH MATERIALIZED VIEW
SELECT * FROM base_imv ORDER BY id;
 id | data  |             ts
----+-------+----------------------------
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
  4 | dummy | 2019-12-22 11:38:29.381414
  5 | dummy | 2019-12-22 11:38:29.381441
  6 | dummy | 2019-12-22 11:38:29.381444
(6 rows)
yugo-n commented 4 years ago

Thank you for your reporting this! The discussion is here: https://www.postgresql.org/message-id/CAF3Gu1Z950HqQJzwanbeg7PmUXLc%2B7uZMstfnLeZM9iqDWeW9Q%40mail.gmail.com

We will fix this.

thoshiai commented 4 years ago

This issue is fixed on latest IVM branch and patch. Please close this issue, If there is no problem.

nuko-yokohama commented 4 years ago

I confirmed that it was fixed. isuue is closed.

CREATE INCREMENTAL MATERIALIZED VIEW base_imv AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
psql:timeshit.sql:10: ERROR:  functions in IMMV must be marked IMMUTABLE