Re: confused about material view locks please explain

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"