sraoss / pgsql-ivm

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

A unique index should not be created in the case of a Cartesian product #151

Closed yugo-n closed 1 year ago

yugo-n commented 2 years ago

Reported by huyajun in https://www.postgresql.org/message-id/tencent_FCAF11BCA5003FD16BDDFDDA5D6A19587809%40qq.com

A unique index should not be created in the case of a Cartesian product

create table base_a (i int primary key, j varchar);
create table base_b (i int primary key, k varchar);
INSERT INTO base_a VALUES
(1,10),
(2,20),
(3,30),
(4,40),
(5,50);
INSERT INTO base_b VALUES
(1,101),
(2,102),
(3,103),
(4,104);
CREATE incremental MATERIALIZED VIEW s as
select base_a.i,base_a.j from base_a,base_b; -- create error because of unique index
yugo-n commented 1 year ago

It is not related to Cartesian product. This is a bug that a unique index could be created even even where there is a primary key attribute from just one of relations in FROM clause. Actually, it is intended that an index is created only if all primary keys of tables in FROM clause appear in the target list.

I misused pull_varnos_of_level but this didn't work, so I fixed it by using get_relids_in_jointree instead.