Re: Implementing Incremental View Maintenance - Mailing list pgsql-hackers

On Wed, 25 Nov 2020 15:16:05 +0300
Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

> 
> 
> On 24.11.2020 13:11, Yugo NAGATA wrote:
> >
> >> I wonder if it is possible to somehow use predicate locking mechanism of
> >> Postgres to avoid this anomalies without global lock?
> > You mean that, ,instead of using any table lock, if any possibility of the
> > anomaly is detected using predlock mechanism then abort the transaction?
> 
> Yes. If both transactions are using serializable isolation level, then 
> lock is not needed, isn't it?
> So at least you can add yet another simple optimization: if transaction 
> has serializable isolation level,
> then exclusive lock is not required.

As long as we use the trigger approach, we can't handle concurrent view maintenance
in either repeatable read or serializable isolation level.  It is because one
transaction (R= R+dR) cannot see changes occurred in another transaction (S'= S+dS)
in such cases, and we cannot get the incremental change on the view (dV=dR*dS). 
Therefore, in the current implementation, the transaction is aborted when the
concurrent view maintenance happens in repeatable read or serializable.
 
> But I wonder if we can go further so that even if transaction is using 
> read-committed or repeatable-read isolation level,
> we still can replace exclusive table lock with predicate locks.
> 
> The main problem with this approach (from my point of view) is the 
> predicate locks are able to detect conflict but not able to prevent it.
> I.e. if such conflict is detected then transaction has to be aborted.
> And it is not always desirable, especially because user doesn't expect 
> it: how can insertion of single record with unique keys in a table cause 
> transaction conflict?
> And this is what will happen in your example with transactions T1 and T2 
> inserting records in R and S tables.

Yes. I wonder that either aborting transaction or waiting on locks is unavoidable
when a view is incrementally updated concurrently (at least in the immediate
maintenance where a view is update in the same transaction that updates the base
table).
 
> And what do you think about backrgound update of materialized view?
> On update/insert trigger will just add record to some "delta" table and 
> then some background worker will update view.
> Certainly in this case we loose synchronization between main table and 
> materialized view (last one may contain slightly deteriorated data).
> But in this case no exclusive lock is needed, isn't it?

Of course, we are considering this type of view maintenance. This is
deferred maintenance where a view is update after the transaction
that updates the base tables is committed. Views can be updated in
bacground in a appropreate timing or as a response of a user command.

To implement this, we needs a mechanism to maintain change logs which
records changes of base tables. We think that implementing this infrastructure
is not trivial work, so, in the first patch proposal, we decided to start from
immediate approach which needs less code. 

-- 
Yugo NAGATA <nagata@sraoss.co.jp>