MIME-Version: 1.0
References: 
 <CAHnOmadn1UB-t-=Umd_TSEZ=kw48=ecX3EnesABxbPdboB-ZUQ@mail.gmail.com>
 <32ad0fda77629362dbdc90136e6d5f667d496e01.camel@cybertec.at>
 <CAHnOmafAW_Dqc8NEkmi=HOOMp3xf1DZdtOXauU2c1N5hq9BnVw@mail.gmail.com>
 <CAL9MbytuyCsNoKK4Aus8zsXYiBz+G+YAm+Gvoa1Pz+SvGO7fBA@mail.gmail.com>
 <CAHnOmacs_JB_wjA9g=PZyuH5jtbFb_qkf-dS7h7H4bkS4nTQeg@mail.gmail.com>
 <18617cd83b190c4209a9b16597aaacbfa7ba4df8.camel@cybertec.at>
 <CAHnOmafmSVpxJGcP6SiVP=4BbdUAspp6K07T9rSvNyae6tk4WQ@mail.gmail.com>
 <1ceba18088383dd1a3cfc3d8dcf4a12615609b2f.camel@cybertec.at>
In-Reply-To: <1ceba18088383dd1a3cfc3d8dcf4a12615609b2f.camel@cybertec.at>
From: =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?=
 <a.glushakov86@gmail.com>
Date: Fri, 23 May 2025 09:51:15 +0300
Message-ID: 
 <CAHnOmadXtVfft-uo9ZJcTP20LfbXViLh1dSZixXzer73Xo1GKg@mail.gmail.com>
Subject: Re: query hangs out
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: ikramuddin <ikram.amani815@gmail.com>, pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000081047a0635c8076a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHnOmadXtVfft-uo9ZJcTP20LfbXViLh1dSZixXzer73Xo1GKg%40mail.gmail.com>
Precedence: bulk

--00000000000081047a0635c8076a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you.
I have investigated the logs to find the cause.
The only thing I found is that the problem started after a failover (we use
patroni with synchronous replication).
The problem is unlikely on the hardware level, since we use a private cloud
with enterprise-level hardware, and the checksum verification in postgres
did not find any problems.
If this is a bug in postgres, and hackers are willing to investigate the
problem, I can provide an archive with pgdata

=D1=81=D1=80, 21 =D0=BC=D0=B0=D1=8F 2025=E2=80=AF=D0=B3. =D0=B2 18:09, Laur=
enz Albe <laurenz.albe@cybertec.at>:

> On Wed, 2025-05-21 at 16:27 +0300, =D0=90=D0=BD=D1=82=D0=BE=D0=BD =D0=93=
=D0=BB=D1=83=D1=88=D0=B0=D0=BA=D0=BE=D0=B2 wrote:
> > > What do you see at (47,13)?
> >
> > I have restored the data before the freeze, here is the content (47.13)
> > SELECT * FROM heap_page_items(get_raw_page('"InboxState"', 47)) where l=
p
> =3D 13;
> > -[ RECORD 1
> ]------------------------------------------------------------------------=
-------------------------------------------------------------------
> > lp          | 13
> > lp_off      | 4632
> > lp_flags    | 1
> > lp_len      | 100
> > t_xmin      | 136385644
> > t_xmax      | 136385520
> > t_field3    | 0
> > t_ctid      | (47,13)
> > t_infomask2 | 11
> > t_infomask  | 8337
> > t_hoff      | 32
> > t_bits      | 1111011000000000
> > t_oid       |
> > t_data      |
> \x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf7=
8bcf5153401000000ad43d6a5273608dd947a749769b943ab3cd8020001000000
>
> That tuple should be visible.
>
> So I'd say you should killed the other tuple with heap_force_kill().
>
> Yours,
> Laurenz Albe
>

--00000000000081047a0635c8076a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thank you.<br>I have investigated the logs to find the cau=
se.<br>The only thing I found is that the problem started after a failover =
(we use patroni with synchronous replication).<br>The problem is unlikely o=
n the hardware level, since we use a private cloud with enterprise-level ha=
rdware, and the checksum verification in postgres did not find any problems=
.<br>If this is a bug in postgres, and hackers are willing to investigate t=
he problem, I can provide an archive with pgdata</div><br><div class=3D"gma=
il_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">=D1=
=81=D1=80, 21 =D0=BC=D0=B0=D1=8F 2025=E2=80=AF=D0=B3. =D0=B2 18:09, Laurenz=
 Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec=
.at</a>&gt;:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On =
Wed, 2025-05-21 at 16:27 +0300, =D0=90=D0=BD=D1=82=D0=BE=D0=BD =D0=93=D0=BB=
=D1=83=D1=88=D0=B0=D0=BA=D0=BE=D0=B2 wrote:<br>
&gt; &gt; What do you see at (47,13)? <br>
&gt; <br>
&gt; I have restored the data before the freeze, here is the content (47.13=
)<br>
&gt; SELECT * FROM heap_page_items(get_raw_page(&#39;&quot;InboxState&quot;=
&#39;, 47)) where lp =3D 13;<br>
&gt; -[ RECORD 1 ]---------------------------------------------------------=
---------------------------------------------------------------------------=
-------<br>
&gt; lp =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 13<br>
&gt; lp_off =C2=A0 =C2=A0 =C2=A0| 4632<br>
&gt; lp_flags =C2=A0 =C2=A0| 1<br>
&gt; lp_len =C2=A0 =C2=A0 =C2=A0| 100<br>
&gt; t_xmin =C2=A0 =C2=A0 =C2=A0| 136385644<br>
&gt; t_xmax =C2=A0 =C2=A0 =C2=A0| 136385520<br>
&gt; t_field3 =C2=A0 =C2=A0| 0<br>
&gt; t_ctid =C2=A0 =C2=A0 =C2=A0| (47,13)<br>
&gt; t_infomask2 | 11<br>
&gt; t_infomask =C2=A0| 8337<br>
&gt; t_hoff =C2=A0 =C2=A0 =C2=A0| 32<br>
&gt; t_bits =C2=A0 =C2=A0 =C2=A0| 1111011000000000<br>
&gt; t_oid =C2=A0 =C2=A0 =C2=A0 |<br>
&gt; t_data =C2=A0 =C2=A0 =C2=A0| \x3e8a7c00000000000100000090877a16b4b308d=
d9460898784c4af2dab692693d29bdf78bcf5153401000000ad43d6a5273608dd947a749769=
b943ab3cd8020001000000<br>
<br>
That tuple should be visible.<br>
<br>
So I&#39;d say you should killed the other tuple with heap_force_kill().<br=
>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--00000000000081047a0635c8076a--