Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXo5a-00CkhO-6z for pgsql-general@arkaria.postgresql.org; Wed, 07 Feb 2024 20:00:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rXo5Z-00CyiU-86 for pgsql-general@arkaria.postgresql.org; Wed, 07 Feb 2024 20:00:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXo5X-00CyiM-N3 for pgsql-general@lists.postgresql.org; Wed, 07 Feb 2024 20:00:08 +0000 Received: from sonic311-14.consmr.mail.bf2.yahoo.com ([74.6.131.124]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXo5T-005auT-D7 for pgsql-general@lists.postgresql.org; Wed, 07 Feb 2024 20:00:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.br; s=s2048; t=1707336001; bh=m5J8q9ixCizCh4uxdbi8RX+SBhu+zdG1jBjuKBW0A64=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=UdHcJ2qwWArxvIJuoxyoxYLS/ID9aZr17OMUyUHJPFyg8Q97MEnxYMeRoZ6tnxuuvvH9mWm7QYc+K6KSLighraxS9sZjUsSjxidWsReiCzgMStq06O9x+He1GY44448yR0r/cHe57uHAyGNHixn1WK5Js3zCJWvGFzMMx1uvvPtIxdc7uk6oo7mWJ5X4BGvchXevIW90h03xSVp5T74+pMrNk+i0Bpj8j2qQOBvr6LyMweLabzdzWac2rPDQ1FYxXkqPpLQKZc60s1pDWGz3Hr/O2LcesKNhTAOnF07ReVWSCLlt7Io/IQZuC5tm/klYPbvuJ1jyZWDwzAjSEBrn+A== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1707336001; bh=A8narPQAcdy4AcDeV4yvR9EVEzBr6Ik8IWg8p1/03Ov=; h=X-Sonic-MF:X-Sonic-MF:Date:From:To:Subject:From:Subject; b=UtxoKka8VcV0zsV5AcbiM4XN1835xUBJQBCAKSYAIN7wtEgt3+ypdWwJlms0rdeM7hHM5Qw6d2GDwOpOHTO0S3uz0R5mnpN3f7lFm2pus/2IlCATvj/4y6/Y41R73UbS3kzjmH/7FlIs7/6qJkkPtnhSSyDQT0hDc7zYmSi6Aww0aB0whXW61Faowy/qyChOJe/pMPEk5+/vSVFrZzcKa+qTvNLCg44L9H+oVs+pMegDRkvnJHj3Ui0xEYoLGP8dQQJhA+x1ghStlBfwA4q5dW1HBKqrDaMVavf9zj/As828BdQd2b5KYZvTvdTDCvB3fi8zq6O3Kq+OoF2QpeGDBg== X-YMail-OSG: 68zzkuIVM1n6kpvEGkHrdS9062cBnfg_jHiWwpYaLx0Ah00LaRj866EZUYWwMki SI0NT73rH5OIWtFLy9xgVCHb2VzTtrAwMGHgRu9XoQFkjuU1JJUhzNIL3JmFXX7XFoQBBuBrVIeS omrwmH9j1LC5NRQiQj2xDk6btx6_JS7laz4_asZO2kvHBbO7CBGqYYjOuR.AI4jCOqxMXGTWFbLe E0b4u1I8cEu9Yk.3ylodQhn5xHeHaHF6hAvVZ86_rPHUra5V.QmslHKYXcAMxrNIFrM0SDki88Ze mK8p57jZBy3MwrmY_.SzMNbAazJ2dW1Sf3qc.20XS8oNmE05IGQ0YZN4H6PtuPNGdXV0dcv1yT08 XNAxeKghfFYWEigIIb2BfHcVvC12R4lFuaaNhrbdG3ktXwh.EGV0igRNin0m8T6BCm.U5mX0wFrJ ATn6Of2HYMwYAlyz1dfhu3DLh2GET0OwKgx4d063F4_8aETCJdAZGMxsFkRq7fXqIt8qs2KIJ0Ag j.SW1fnYP4pyxbrXCAo__prZEN32pN8cXny3G4V2uiWJdeM6RNfmqB5MpBlRLZ_2hBzaVgCXHcL3 6lZH.xPZ5T8z0faokSNNrkqSJLG4IUaf9bXfhlcMDsCc5z0adWYfrMIs6SUJn6sUqE8ZmACslBSy aKV7wCGbdcoXzh2YaDUyoSG3zLV2wGxbtCTQ0KXEM46fNxACcsDy6H9kHxI78gr0csc2Y0PiUS4d gFe.BTsV0hwrtw7C2UXcf01rKAFNUWX3aJs926nP3DKh3zgeV0CCPR8Un_1cuhGggPGQQf_QDPGK 71Y_jyNZkPFknqPGSS3IoRpud.He.VWSjWsXyTeb_LYX6iY6ZKT79y2u_vfveqq1OdxNPJgnQrwA QyOYh.otVCWrS4rHTZzvUnnk1IBjytDZ4.xb3PlGdyevZjJ3pFbx7QY1vOX2MAvksijjVl3iEGPo Zo40yoHl9Slbp4vq5rPn4xiGe_FNcOcMifFCISLh4kng2TO.nD9FeUcBlZ07QZfGfOC.Rkuoqlhx NGlp.tpKYOmx71l_IdyeLMC_lJsgLZFKpG2QszAmQ.as9Geh1RwQFTO4DP9IcZuFqFH4OpLXuC60 ygJRZtQKkUej4QECsBzvRZ2neLIsgl910D0u99Nlh6PGDV8cmkmkcStC.ktdiu_IeLapyLaHCwcu ooCuXKTYtRQvJViAK0LmvA1EgyHJWkCDdBJgr2.TjVneBNR7Ij_dL6dwgaCzfn1r8yOxvJdBYtmq G.Q3g9qCvQGRRmRfQbOiQuahtqELg.wwc6TssFfA7O0esJLfmlxLCUNIeuEmvHp7GSxnpJddZhfl CFE49rJhPjiAaDjqpfnufK6xj54nOgnbwcvSXWS.5na..Vv3K33Pq4J2msTbEkIlqAh5w7Px_LgA RyY6_COFXwfw3a1yiG9NcEo5TQwwhRqquG4fvuUqR34RelWxbSassgmc_U8FTpmb6G8mk6su9n7h xIhbgzzfoxHYitX2zaBadQRBTsoiXBtWn3XAHitmql_7qN8F5opyKcbXfVzBdCCNLjb5ZgZPbeZr dCGzI3nHo76FiZPiVDUi9slAfH49jX5YkF4ngL7xTg5hrEGR.rB_s2KzqRLWH4.m1rjCzjSgUx2H G9sWPV0FzFitikjZ7Xgj7uVRKibzku0YJvkSEfogVYu_7dCQq4N4XEeb3Ls6XQKSkItpJamQ0cV1 E0XujcTG8mlwHCGl8r4aMs9C6M_pTwQJwlheEbMijtEy2qEG0D4k1D6iknAMakDxkWp62CTNHKTz 9po8YUeouKX3CZRGQW_sMN7DPe4HXR1R2Y66WIxMx8mjR1cnQdCLWLJw731hqV2eang49ow1_P6n moqX4tG64mHRDUp18DMDzRVusUgDYkiUMMkbAhOz2FcMgqm0jdFTfaD0rxPxaU3PM8a1ndGLHGkW ecFqhL0z7V8dhyqvfCc1PNlMMs7JFiIgjFj1A5KtHay8DuASFLqIwNfyiV0DH5dWC9ichiQgQV2s 4EDrIWD9RiDnkjMP6RtZFA8uu4YsOCDSxhUbYXTahGTgDZLiMCAxGmKz_1MIy_G1d2nqKCrV6Tk8 wyKENoCodJuws0WxzL4cslxYqi7Sc8sEsN6W_8Mbyuwna7T61OCMMt1CByHweRbOa0IjyFoHBroP YSS8LyuJt09GtJ3QZT4hluiR0D1IaG3dZGAirQ3GvzP31_H_KdIAXxcAAFscbmA3xDEDbSuk7YnI CZU09vbVERGJ2NDId3fMgBLflf0Kodh0pvUFzQs1j3OWqUShnYcFAcTq_EGY6GdfwnX1djvlHfzP PFsZ3Y.DLRVJaVH2EDyRATsvg9.Gt6tQP X-Sonic-MF: X-Sonic-ID: ca8336dc-5158-43b0-8ded-05d0adfec25d Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Wed, 7 Feb 2024 20:00:01 +0000 Received: by hermes--production-gq1-5c57879fdf-27p5r (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID 35d8ce641f9949fd62092d08a35152e3; Wed, 07 Feb 2024 19:59:55 +0000 (UTC) X-YMail-OSG: 2GuJ1lEVM1n9J.EDuOIROy0bWm36JTx6sTTLM90aIbnZQn0oZiu4XcyP11R_Zx9 cmiGSRUeRaJ5WBOLpASDIEAPssyZvW0dOk30ddq1x7fIwNJg9RhfpDvsDEYv44TIQIQ2MouOAJry 5S_go8iJ1NZY4P0V98lYRDh6XxbqYQQUEcOA26UvX5Zf2krWsOQiofYoFGllQuKa9yCVjlABZ1U5 GvBFylgFQELbER0Ci9M5wi8t4.m0L0zu3HnfIaSjKxvZm7mIMMGCtvhT6ArE2NpHP2GwDHeHxcoM Q7MntZuwMtBVdXGzgWYx7Y_O7lDORv8p41nAUq0UzRDRgDPJaDqMOVtK9TZyDYrQOEoL9T7XDYMT vQt7WVBjaYQvriWJxG.eYLjwuZ7oBNvj7yG5bMlxIlXWFJpDd3krVwnNvK6PTERmKmzp77p_QoIy _d.TyPfmugS8J0nUubhGt.96x1B9lKGggTZfuwN4MbCB.MLXHGtX8L4q1q1dv.89KWipBbDW3J2I A9g.Ws4RLMNsq.WKX2._qkFPRwzVW.wMyaI7cozyCkQCYRa6YpgXIIQ_1eR4O9fSp5Qgtq9..h0R OKLXPi0zylDhuns186xRhb2C_HPtE_FZm6XOKSMBZs__VEWUowk8rTSoDRfyXMG39AgfYISoTpKB IVSAZu.XOaVancUVnUTEX66TuvR9EXQbkiflWxlTupWJsIBP9uUv.Dzj_c5u3lri.VxxanXLp8S4 iwx1qZL4X5SDzKPyydfLr6QbJSxLlgmJiGQYdHq9oi8XUTSCYE_s8MyCQ2S9wfU5ZmK0sQYBniVb khOeQMUtvPFP6fFIOx5nJ4i4aRfiyqOXzdLYWys.6sChXU16kPl4qyKoaYeBQU7gtsyuhgcy5G2b FeHiykoSF9ygg8XQspC_LJH7Ky2XxLKNCi_vcvv4u509kgKBLAlco9TVkQL3dOu0AUmAmx0pZwXp izmS8wKM3ONeVxQgdScVFUnoE7U1RsewfTr9seWVDZLOQ_LI45NOngv_R_T.sd77ASBnXiS2R8h5 93BSGiLJFxZiibNztJtv3UlqCG7qNahT0N.1O45vmzjR8iRI4.C_YtXPwJYoRFn4ttmkbGcJR9Lg 5jVCAEoEvP8ULM_afOYf4.h9jjIBCI65Iy6HsBaJsn_dFsyCJ9i56e_yALohEeYAdiJ5b9MWmnNu JsLgrS8frUwHSX80GkkTN8spgCM.2d4JtY2cOKUqrg3CcnrAnkU12drv0NJopwoNjbgbrJUu5drq bwNxV1dJ4bAheUJjTkSM2oSppmaZf79bmpXLYj_W0BvRQUnzLdDOIzce78ESmRlWbRzRWSwILDwd dg7vhQwIYNKJeEh._btU_VWaFpN7wLJnSRvO9F8XNrH8LfqCV6P9ROqAI2cEzaX5gSyeGDKHo2PO Wk2_zW1_NPJdHx_7A0eY6KaydX2XxAO1f3mPAlktKA7l4i_huTT6XGkJUTD20855HHspSUa3UWos 6WNf.pFEqi1TgmxrzNvF0aMLqk9fPaQFv.hK0y3zRCMpW7hR4Uw352xCnmqRiS3c9CpkRR0nqyCT MxLxvY1Dcw1vNpt9954iDH9J.dZaX5kAJtZ0zlJWWYaZQA_q8GRmPGb78WCbXAdLo970LOoBu83K EPc9arrwZaGSmJgrXxxx0NwKMrTIjyeRAIkv3wDiUPtfYY6vg5dWDWP9yY4aZGXUxDeCSa3EAoVd dtwMM7PGcavaCh0PIF2kWJ5KPob1a4NvgmwTi_wmdSHgJVjLEEeQftnIb6rh5Mf2LyMnm9MLmUg6 7QoB6gC7kLtslfkqFWhX3umeBV.zAgQZQ.zl0yLS9lIlpBZXI_xVwDfOfr4c2DyDxKI8I_GDtgeK HQJl71FdD1EO5ge2PlEWYuvjweqpxetzjzugyxSypkHl436mRJStZoIAPQNq2jjTS.X66owlIA2h zJOzY_QlGLr.n2V2PNTPBJTA.p_1xhkTN55kd8t6hh0mTu.fT1.4tbD95V9G3fL15_L3AB3WCXSb qt35YP92EQZcmWSBUvXRio6pUFQMQ95sq8.z7h2uiaosWyZcVjIoiPT0N2WyybuA8r2cO_cTyZXo 87SZew8m_bFdg3qvodM8ynr7.2IYwOM4W7RuqMIx1QoNKZTiUjxrkszjj4hpxIuReyA_NHqiETL8 5rw6Szu3VYzBl.yMPqApI6iuKQUT65dHOpIzYvFiFHAuvd1q6DGj67pMBwDXM3JMLs07LHcjhxL0 x_TvYSkLcW.LXbNjDpnE1.xybZ8Caaew971y86DMcpgUfT9MmlFka4x4muVDSCcwakO3AsoZoJga qPawyqLBuFmjQvKFrKruKb1iuluZef6KsEt.qKuyKgyWqnrNmJLIakc2isCMffYG.kmZeje0T9yr JQzB7czl0gN8fGDBRHA-- X-Sonic-MF: X-Sonic-ID: 084441b5-00af-4cdf-aae9-17203efcbf21 Received: from sonic.gate.mail.ne1.yahoo.com by sonic321.consmr.mail.gq1.yahoo.com with HTTP; Wed, 7 Feb 2024 19:59:54 +0000 Date: Wed, 7 Feb 2024 19:59:32 +0000 (UTC) From: abrahim abrahao Reply-To: abrahim abrahao To: Greg Sabino Mullane Cc: "pgsql-general@lists.postgresql.org" Message-ID: <299985666.43419.1707335972964@mail.yahoo.com> In-Reply-To: References: <1059371874.2807306.1706727919318.ref@mail.yahoo.com> <1059371874.2807306.1706727919318@mail.yahoo.com> <1866957898.3683025.1706905527172@mail.yahoo.com> Subject: Re: vacuum freeze wait_event BufferPin MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_43416_2095730218.1707335972957" X-Mailer: WebService/1.1.22046 YMailNorrin Content-Length: 9408 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_43416_2095730218.1707335972957 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks again=C2=A0Greg, I really appreciated all information. On Friday, February 2, 2024 at 08:16:41 p.m. EST, Greg Sabino Mullane <= htamfids@gmail.com> wrote: =20 =20 On Fri, Feb 2, 2024 at 3:25=E2=80=AFPM abrahim abrahao wrote: =C2=A0SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) should not be block= ed by=C2=A0ACCESS SHARE (AccessShareLock).=C2=A0Am I wrong about it? If I a= m not wrong why it still locking it?=C2=A0 Those locks with no other context are somewhat=C2=A0 of a red herring. The = important part is not that the AccessShare is somehow blocking ShareUpdateE= xclusive, but that the ShareUpdateExclusive process is NOT blocking new Acc= essShare processes! In the internals of postgres, vacuumlazy.c tries to gra= b a buffer lock (different concept from all the user-visible locks above). = It politely tries to wait[1] until nobody else is grabbing it (aka pinning = it), then proceeds. The problem is that other processes are allowed to come= along and put a pin in it as well - the vacuum's=C2=A0shareupdateexclusive= =C2=A0lock does not prevent that. So the timeline is: Process X runs a long select and pins the buffer Process V runs a vacuum freeze and tries to lock the buffer. It detects oth= er pins, so it waits. It assumes that whoever is holding the pin will relea= se it someday.Process Y runs another long select and also pins the buffer.P= rocess X ends, and removes its pins.Process V still cannot move - it just k= nows there are still pins. Where they come from does not matter. As long as there is at least one other process holding a pin, the vacuum fr= eeze cannot continue[2]. That's my understanding of the code, anyway. This could be argued as a bug.= I am not sure what a solution would be. Cancelling user=C2=A0queries just = for a vacuum would not be cool, but we could maybe pause future pin-creatin= g actions somehow? For the time being, forcing a super-quick moment of no table access would s= eem to be your best bet, as described earlier. Cheers,Greg [1] https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/= backend/access/heap/vacuumlazy.c;hb=3DHEAD#l975 See backend/storage/buffer/bufmgr.c for LockBufferForCleanup() [2] Quick duplication script:drop table if exists foobar; create table foobar as select 1 as id; alter table foobar set (autovacuum_enabled =3D off); update foobar set id =3D id; Process 1:begin; select *, pg_sleep(111111) from foobar; Process 2:vacuum(freeze,verbose) foobar; /* blocked */ Process 3:begin;=C2=A0select *, pg_sleep(333333) from foobar; Run in order. Kill Process 1 and Process 2 is still blocked. Kill Process 3= and Process 2 finished the vacuum.Note that a regular vacuum (without a fr= eeze) will not get blocked. Cheers,Greg =20 =20 ------=_Part_43416_2095730218.1707335972957 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Thanks again&= nbsp;Greg, I really appreciated all information.
<= /div>

<= div dir=3D"ltr" data-setdir=3D"false">
=20
=20
On Friday, February 2, 2024 at 08:16:41 p.m. EST, Greg = Sabino Mullane <htamfids@gmail.com> wrote:


On Fri, Feb 2, 2024 at 3:25=E2=80=AFPM abrahim abrahao <a= _abrahao@yahoo.com.br> wrote:
 SHARE UPDATE EXCLUSIVE (ShareUpdate= ExclusiveLock) should not be blocked by ACCESS SHARE (AccessShareLock)= . Am I wrong about it? If I am not wrong why it still locking it? = ;

Those lo= cks with no other context are somewhat  of a red herring. The importan= t part is not that the AccessShare is somehow blocking ShareUpdateExclusive= , but that the ShareUpdateExclusive process is NOT blocking new AccessShare= processes! In the internals of postgres, vacuumlazy.c tries to grab a buff= er lock (different concept from all the user-visible locks above). It polit= ely tries to wait[1] until nobody else is grabbing it (aka pinning it), the= n proceeds. The problem is that other processes are allowed to come along a= nd put a pin in it as well - the vacuum's shareupdateexclusive lo= ck does not prevent that.

So the ti= meline is:

Process X runs a long se= lect and pins the buffer
Process V runs a vacu= um freeze and tries to lock the buffer. It detects other pins, so it waits.= It assumes that whoever is holding the pin will release it someday.
<= div>Process Y runs another long select and also pins the buffer.
= Process X ends, and removes its pins.
Process V still cannot move= - it just knows there are still pins. Where they come from does not matter= .

As long as there is at least one = other process holding a pin, the vacuum freeze cannot continue[2].

That's my understanding of the code, anyway= . This could be argued as a bug. I am not sure what a solution would be. Ca= ncelling user queries just for a vacuum would not be cool, but we coul= d maybe pause future pin-creating actions somehow?

For the time being, forcing a super-quick moment of no tabl= e access would seem to be your best bet, as described earlier.
Cheers,
Greg

Se= e backend/storage/buffer/bufmgr.c for LockBufferForCleanup()

[2] Quick duplication script:
drop tabl= e if exists foobar;
create table foobar as select 1 as id= ;
alter table foobar set (autovacuum_enabled =3D off);update foobar set id =3D id;
<= br clear=3D"none">
Process 1:
begin; select *, pg_sleep= (111111) from foobar;

Process 2:
vacuum(freeze,verbose) foobar; /* blocked */

Process 3:
begin; select *, pg_sleep(333333) from foobar;

Run in order. Kill Process= 1 and Process 2 is still blocked. Kill Process 3 and Process 2 finished th= e vacuum.
Note that a regular vacuum (without a freeze) will not = get blocked.

Cheers,
Greg

------=_Part_43416_2095730218.1707335972957--