LIMIT clause without ORDER BY should be prohibited when creating incremental materialized views.

[nuko-yokohama]

In SQL, the result of a LIMIT clause without ORDER BY is undefined. If the LIMIT clause is allowed when creating an incremental materialized view, incorrect results will be obtained when the view is updated after updating the source table.

[ec2-user@ip-10-0-1-10 ivm]$ psql --version
psql (PostgreSQL) 13devel-ivm-3bf6953688153fa72dd48478a77e37cf3111a1ee
[ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f limit-problem.sql
DROP TABLE IF EXISTS test CASCADE;
psql:limit-problem.sql:1: NOTICE:  drop cascades to materialized view test_imv
DROP TABLE
CREATE TABLE test (id int primary key, data text);
CREATE TABLE
INSERT INTO test VALUES (generate_series(1, 10), 'foo');
INSERT 0 10
CREATE INCREMENTAL MATERIALIZED VIEW test_imv AS SELECT * FROM test LIMIT 1;
SELECT 1
                               Materialized view "public.test_imv"
    Column     |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
---------------+---------+-----------+----------+---------+----------+--------------+-------------
 id            | integer |           |          |         | plain    |              |
 data          | text    |           |          |         | extended |              |
 __ivm_count__ | bigint  |           |          |         | plain    |              |
View definition:
 SELECT test.id,
    test.data
   FROM test
 LIMIT 1;
Access method: heap
Incremental view maintenance: yes

SELECT * FROM test LIMIT 1;
 id | data
----+------
  1 | foo
(1 row)

TABLE test_imv;
 id | data
----+------
  1 | foo
(1 row)

UPDATE test SET data = 'bar' WHERE id = 1;
UPDATE 1
SELECT * FROM test LIMIT 1;
 id | data
----+------
  2 | foo
(1 row)

TABLE test_imv;
 id | data
----+------
  1 | bar
(1 row)

DELETE FROM test WHERE id = 1;
DELETE 1
SELECT * FROM test LIMIT 1;
 id | data
----+------
  2 | foo
(1 row)

TABLE test_imv;
 id | data
----+------
(0 rows)

ORDER BY clause is not allowed when executing CREATE INCREMENTAL MATELIARIZED VIEW. We propose not to allow LIMIT clauses as well.

[nuko-yokohama]

In "commit e150d964df7e3aeb768e4bae35d15764f8abd284", it was confirmed that a query containing a LIMIT clause and an OFFSET clause failed. This issue will be closed.

CREATE INCREMENTAL MATERIALIZED VIEW test_imv AS SELECT * FROM test LIMIT 1;
psql:limit-problem.sql:5: ERROR:  LIMIT/OFFSET clause is not supported with IVM
CREATE INCREMENTAL MATERIALIZED VIEW test_imv2 AS SELECT * FROM test OFFSET 1;
psql:limit-problem.sql:7: ERROR:  LIMIT/OFFSET clause is not supported with IVM