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

On Wed, Sep 9, 2020 at 12:29 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> I also thought it might be resolved using tuple locks and EvalPlanQual
> instead of table level lock, but there is still a unavoidable case.  For
> example, suppose that tuple dR is inserted into R in T1, and dS is inserted
> into S in T2. Also, suppose that dR and dS will be joined in according to
> the view definition. In this situation, without any lock, the change of V is
> computed as dV=dR*S in T1, dV=R*dS in T2, respectively, and dR*dS would not
> be included in the results.  This causes inconsistency. I don't think this
> could be resolved even if we use tuple locks.

I see.  Thanks for the explanation!

> As to aggregate view without join , however, we might be able to use a lock
> of more low granularity as you said, because if rows belonging a group in a
> table is changes, we just update (or delete) corresponding rows in the view.
> Even if there are concurrent transactions updating the same table, we would
> be able to make one of them wait using tuple lock. If concurrent transactions
> are trying to insert a tuple into the same table, we might need to use unique
> index and UPSERT to avoid to insert multiple rows with same group key into
> the view.
>
> Therefore, usual update semantics (tuple locks and EvalPlanQual) and UPSERT
> can be used for optimization for some classes of view, but we don't have any
> other better idea than using table lock for views joining tables.  We would
> appreciate it if you could suggest better solution.

I have nothing, I'm just reading starter papers and trying to learn a
bit more about the concepts at this stage.  I was thinking of
reviewing some of the more mechanical parts of the patch set, though,
like perhaps the transition table lifetime management, since I have
worked on that area before.

> > (Newer papers describe locking schemes that avoid even aggregate-row
> > level conflicts, by taking advantage of the associativity and
> > commutativity of aggregates like SUM and COUNT.  You can allow N
> > writers to update the aggregate concurrently, and if any transaction
> > has to roll back it subtracts what it added, not necessarily restoring
> > the original value, so that nobody conflicts with anyone else, or
> > something like that...  Contemplating an MVCC, no-rollbacks version of
> > that sort of thing leads to ideas like, I dunno, update chains
> > containing differential update trees to be compacted later... egad!)
>
> I am interested in papers you mentioned!  Are they literatures in context of
> incremental view maintenance?

Yeah.  I was skim-reading some parts of [1] including section 2.5.1
"Concurrency Control", which opens with some comments about
aggregates, locking and pointers to "V-locking" [2] for high
concurrency aggregates.  There is also a pointer to G. Graefe and M.
J. Zwilling, "Transaction support for indexed views," which I haven't
located; apparently indexed views are Graefe's name for MVs, and
apparently this paper has a section on MVCC systems which sounds
interesting for us.

[1] https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf
[2] http://pages.cs.wisc.edu/~gangluo/latch.pdf