Date: Sat, 6 Jul 2024 13:49:05 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: confused about material view locks please explain
Message-ID: <20240706114905.i7vw5cnbupnaazkx@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <3eed1916-a166-42b7-862e-692887336652@gmail.com>
 <20240705154035.qjoey3eaqaqitthn@hjp.at>
 <4ba1d48f-c946-404b-9919-231bcbee0130@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="kskfq3ubggggzc7r"
Content-Disposition: inline
In-Reply-To: <4ba1d48f-c946-404b-9919-231bcbee0130@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240706114905.i7vw5cnbupnaazkx%40hjp.at>
Precedence: bulk


--kskfq3ubggggzc7r
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-07-06 11:09:23 +0530, Krishnakant Mane wrote:
>=20
> 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:
> >=20
> > 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.
>=20
> Thank you Peter.
>=20
> 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).


> =A0I 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=3Dreadme-ov-file#concurrent-transa=
ctions
[2] https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-T=
ABLES

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

--kskfq3ubggggzc7r
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaJLykACgkQ8g5IURL+
KF0p6A/+KuDbLh4G8fEnoYAjeScwSePeYq4MUNFGPe5cEyrHXdZROa7MI2U9a6or
8P2jlpRLRPE7kBipvZQsUQMvVLOCW7doCfx/WYssQpfFRjTWCu9llrJtil92QsAX
LZUoAFbRWpfNWeiB0wnEoOZROVVR+uzMEfs3drU0e3tokxhYyAlOPDGeuAdK0szA
49zetCIWoqK+Dqnay13oW16XnOoTyr1rY33oQLERWJUoq5OQe+cXh6NSF/hTVw3+
W/zy+gAAokQclWdJqq+ACKipr27dUr7YwaOFzZn15S+tPJCuq7irUbfg4yOQQzCS
BQmfHA0V7Cd0+9zuZTbsOLJxgoPNeIMamLgzMDICH1QBoAMAReBx2ecwl79wWLiw
kQ2N4vJaAkGmE/C6HMWiFecuB0zrlYjl5eUrUGtJFxtm7d2SQxMFCZaqWznk4Kr3
aAD6yCRILTzx0bPbnz45WO5UsRyP1eMCAwEr4cmJGztQeTNehoYFCZdF0pf3bvxW
la5A6qaCEPpAMuIggjtNSKN5WTYh3iaUfeqMHRn4HPkTgIWEKQvxFalweh/LYqXq
0lRYdvPgNxiWzSla95PAlWoukAinSbeL3hjji5rJntFwmWZDgoUQ0yDFdQvEfwZD
3Lt7QExci28Wz1kI4MMMRNcFvlonTHtwkrHKeLTZ9K5RwgpgWy4=
=Z6l0
-----END PGP SIGNATURE-----

--kskfq3ubggggzc7r--