Обсуждение: confused about material view locks please explain

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.

On 2024-07-05 18:58:21 +0530, Krishnakant Mane wrote:
> I have decided to use materialised views (more precisely IVM ).
[...]
> 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 I understand https://github.com/sraoss/pg_ivm correctly, the
materialized view will be updated within the same transaction. So it's
just the same as any other change in the database:

Neither client will wait for the other. The first client will see either
the old or the new state depending on whether the second client manages
to commit soon enough.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 7/5/24 21:10, Peter J. Holzer wrote:
> If I understand https://github.com/sraoss/pg_ivm correctly, the
> materialized view will be updated within the same transaction. So it's
> just the same as any other change in the database:
>
> Neither client will wait for the other. The first client will see either
> the old or the new state depending on whether the second client manages
> to commit soon enough.

Thank you Peter.

So does that mean both the processes work concurrently?

  I had understood that while an update is happening to an IVM (material 
view ) the view is locked till the update is complete.

If so then how would both the clients have simultaneous access?


Regards.


>          

>

On 2024-07-06 11:09:23 +0530, Krishnakant Mane wrote:
>
> On 7/5/24 21:10, Peter J. Holzer wrote:
> > If I understand https://github.com/sraoss/pg_ivm correctly, the
> > materialized view will be updated within the same transaction. So it's
> > just the same as any other change in the database:
> >
> > Neither client will wait for the other. The first client will see either
> > the old or the new state depending on whether the second client manages
> > to commit soon enough.
>
> Thank you Peter.
>
> So does that mean both the processes work concurrently?

I think so, yes. (But I've only read the README. I don't use pg_ivm
myself).


>  I had understood that while an update is happening to an IVM
> (material view) the view is locked till the update is complete.

According to the README[1], an ExclusiveLock is used. The manual[2]
says:

| EXCLUSIVE (ExclusiveLock)
|
|     Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE
|     EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS
|     EXCLUSIVE lock modes. This mode allows only concurrent ACCESS
|     SHARE locks, i.e., only reads from the table can proceed in
|     parallel with a transaction holding this lock mode.

So I think a parallel SELECT would still be possible.

        hp

[1] https://github.com/sraoss/pg_ivm?tab=readme-ov-file#concurrent-transactions
[2] https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"