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.
