pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
confused about material view locks please explain 4+ messages / 2 participants [nested] [flat]
* confused about material view locks please explain @ 2024-07-05 13:28 Krishnakant Mane <kkproghub@gmail.com> 2024-07-05 15:40 ` Re: confused about material view locks please explain Peter J. Holzer <hjp-pgsql@hjp.at> 0 siblings, 1 reply; 4+ messages in thread From: Krishnakant Mane @ 2024-07-05 13:28 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: confused about material view locks please explain 2024-07-05 13:28 confused about material view locks please explain Krishnakant Mane <kkproghub@gmail.com> @ 2024-07-05 15:40 ` Peter J. Holzer <hjp-pgsql@hjp.at> 2024-07-06 05:39 ` Re: confused about material view locks please explain Krishnakant Mane <kkproghub@gmail.com> 0 siblings, 1 reply; 4+ messages in thread From: Peter J. Holzer @ 2024-07-05 15:40 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org 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!" Attachments: signature.asc (application/pgp-signature, 832B) download ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: confused about material view locks please explain 2024-07-05 13:28 confused about material view locks please explain Krishnakant Mane <kkproghub@gmail.com> 2024-07-05 15:40 ` Re: confused about material view locks please explain Peter J. Holzer <hjp-pgsql@hjp.at> @ 2024-07-06 05:39 ` Krishnakant Mane <kkproghub@gmail.com> 2024-07-06 11:49 ` Re: confused about material view locks please explain Peter J. Holzer <hjp-pgsql@hjp.at> 0 siblings, 1 reply; 4+ messages in thread From: Krishnakant Mane @ 2024-07-06 05:39 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org 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. > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: confused about material view locks please explain 2024-07-05 13:28 confused about material view locks please explain Krishnakant Mane <kkproghub@gmail.com> 2024-07-05 15:40 ` Re: confused about material view locks please explain Peter J. Holzer <hjp-pgsql@hjp.at> 2024-07-06 05:39 ` Re: confused about material view locks please explain Krishnakant Mane <kkproghub@gmail.com> @ 2024-07-06 11:49 ` Peter J. Holzer <hjp-pgsql@hjp.at> 0 siblings, 0 replies; 4+ messages in thread From: Peter J. Holzer @ 2024-07-06 11:49 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org 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!" Attachments: signature.asc (application/pgp-signature, 832B) download ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-07-06 11:49 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-05 13:28 confused about material view locks please explain Krishnakant Mane <kkproghub@gmail.com> 2024-07-05 15:40 ` Peter J. Holzer <hjp-pgsql@hjp.at> 2024-07-06 05:39 ` Krishnakant Mane <kkproghub@gmail.com> 2024-07-06 11:49 ` Peter J. Holzer <hjp-pgsql@hjp.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox