Subject: Re: confused about material view locks please explain

From: Krishnakant Mane <[email-masked]>
Date: Sat, 6 Jul 2024 11:09:23 +0530

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.

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 6 Jul 2024 13:49:05 +0200

> 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.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"