sraoss / pg_ivm

IVM (Incremental View Maintenance) implementation as a PostgreSQL extension
Other
994 stars 27 forks source link

create_immv could create an inconsistent IMMV with concurrent insert into a base table #104

Open yugo-n opened 1 month ago

yugo-n commented 1 month ago

If a base table is modified before calling create_immv in a concurrent transaction, create_immv will create an IMMV inconsistent with the latest contents of the table.

  1. Modify a table in a transaction A.

    postgres=# begin ;
    BEGIN
    postgres=*# insert into yyy values (10);
    INSERT 0 1
  2. Create an IMMV in a transaction B.

    postgres=# begin ;
    BEGIN
    postgres=*# select create_immv('mvyyy','select * from yyy'); -- blocked
  3. Commit the transaction A.

    
    postgres=*# commit;
    COMMIT
    postgres=# select * from yyy;
    i  
    ----
    1
    2
    10
    (3 rows)

4. Commit the transaction B, the IMMV doesn't contain the inserted row.
```sql
(cont.)
NOTICE:  could not create an index on immv "mvyyy" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv 
-------------
           2
(1 row)

postgres=*# end;
COMMIT
postgres=# select * from mvyyy;
 i 
---
 1
 2
(2 rows)
yugo-n commented 1 month ago

When create_immv was called before the table modification, the resultant was correct.