Message-ID: <3eed1916-a166-42b7-862e-692887336652@gmail.com>
Date: Fri, 5 Jul 2024 18:58:21 +0530
MIME-Version: 1.0
User-Agent: Betterbird (Linux)
Content-Language: en-US
To: pgsql-general@lists.postgresql.org
From: Krishnakant Mane <kkproghub@gmail.com>
Subject: confused about material view locks please explain
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/3eed1916-a166-42b7-862e-692887336652%40gmail.com>
Precedence: bulk

Hello all.

I am an old timer postgresql user for last 17 years.

I have used it for my open source as well as enterprise cloud services.

IN my current fintech solution, I need to do some performance optimisations.

I have decided to use materialised views (more precisely IVM ).

So on my postgresql version 16, I have installed pg_ivm extention.

I have one fundamental question before going ahead with it's actual use 
in production.

So, I have a voucher master and voucher details table.

This system pertains to double entry book keeping (debit and credit types ).

master contains id as serial primary key, date, voucher type and 
narration along with invoice number.

details table contains the id as foreign key, account code, again 
foreign key from the accounts table, drcrtype being integer (3 for 
credit and 4 for debit ).

So a view joining vouchermaster, voucherdetails and accounts is created.

Every time an invoice is generated, both the master and detail table 
will get updated.

the voucher view is used for generating reports such as balance sheet, 
profit loss and cash flow.

Given this setup I have a very specific questionh.

if client 1 has asked for his balance sheet and the view is being 
queried, then what will happen if client 2 happens to create an invoice 
concurrently?

Will the invoice creation (and subsequent voucher table and view update 
) wait for client 1 to complete the select query, or will the select 
query halt till the update happen?

If possible, I would like to avoid a lock on the view or at least allow 
selects on the view while it is being incrementally updated.

Is this possible?

Regards.