Subject: Re: Implementing Incremental View Maintenance
To: Yugo NAGATA <nagata@sraoss.co.jp>
Cc: Tatsuo Ishii <ishii@sraoss.co.jp>, pgsql-hackers@lists.postgresql.org
References: <20201001140627.1f8fafe595f545e026d110c6@sraoss.co.jp>
 <20201005181618.31f2baf8583358fcfbed26cd@sraoss.co.jp>
 <024ba056-6b4f-b26c-af17-297c1bbca65a@postgrespro.ru>
 <20201112.205355.393498768557598337.t-ishii@sraoss.co.jp>
 <89729da8-9042-7ea0-95af-e415df6da14d@postgrespro.ru>
 <20201124182133.2b7d2ee8767f4bb946226ecc@sraoss.co.jp>
 <5663f5f0-48af-686c-bf3c-62d279567e2a@postgrespro.ru>
 <20201124191138.f57e0955622f09e333538d7b@sraoss.co.jp>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <172440c3-d7cf-7642-97d1-2996c70ab9fc@postgrespro.ru>
Date: Wed, 25 Nov 2020 15:16:05 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <20201124191138.f57e0955622f09e333538d7b@sraoss.co.jp>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk


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.

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.

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?

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company