sraoss / pgsql-ivm

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

Concurrent DML causes data error in views with tuple dupulicates #148

Open yugo-n opened 2 years ago

yugo-n commented 2 years ago

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

Setup: Create table t( a int); Insert into t select 1 from generate_series(1,3); create incremental materialized view s as select count(*) from t;

S1: begin;delete from t where ctid in (select ctid from t limit 1); S2: begin;delete from t where ctid in (select ctid from t limit 1 offset 1); S1: commit; S2: commit;

yugo-n commented 1 year ago

It was resolved by always taking exclusive lock when a view is maintained for now. However, I would like to look for another way to reduce the lock. Anyway, we need some code cleaning for updating the patch.