Message-ID: <18617cd83b190c4209a9b16597aaacbfa7ba4df8.camel@cybertec.at>
Subject: Re: query hangs out
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?UTF-8?Q?=D0=90=D0=BD=D1=82=D0=BE=D0=BD_?=
 =?UTF-8?Q?=D0=93=D0=BB=D1=83=D1=88=D0=B0=D0=BA=D0=BE=D0=B2?=
	 <a.glushakov86@gmail.com>, ikramuddin <ikram.amani815@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Wed, 21 May 2025 14:38:20 +0200
In-Reply-To: 
 <CAHnOmacs_JB_wjA9g=PZyuH5jtbFb_qkf-dS7h7H4bkS4nTQeg@mail.gmail.com>
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.1 (3.56.1-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/18617cd83b190c4209a9b16597aaacbfa7ba4df8.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-05-21 at 12:06 +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:
> The problem is with only one table.
>=20
> As a result, I determined that the problem is on page 5 of the table (I m=
ade SELECT ctid selections until it hangs).
> Then I tried to delete rows by ctid (5, 0-100) from the table until I fou=
nd the problematic row.
>=20
> Content through pageinspect:
> # SELECT * FROM heap_page_items(get_raw_page('"InboxState"', 5)) where lp=
 =3D 51;
> -[ RECORD 1 ]------------------------------------------------------------=
---------------------------------------------------------------------------=
----
> lp =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 51
> lp_off =C2=A0 =C2=A0 =C2=A0| 3760
> lp_flags =C2=A0 =C2=A0| 1
> lp_len =C2=A0 =C2=A0 =C2=A0| 100
> t_xmin =C2=A0 =C2=A0 =C2=A0| 136269917
> t_xmax =C2=A0 =C2=A0 =C2=A0| 66664135
> t_field3 =C2=A0 =C2=A0| 0
> t_ctid =C2=A0 =C2=A0 =C2=A0| (47,13)
> t_infomask2 | 8203
> t_infomask =C2=A0| 4929
> t_hoff =C2=A0 =C2=A0 =C2=A0| 32
> t_bits =C2=A0 =C2=A0 =C2=A0| 1111011000000000
> t_oid =C2=A0 =C2=A0 =C2=A0 |
> t_data =C2=A0 =C2=A0 =C2=A0| \x3e8a7c00000000000100000090877a16b4b308dd94=
60898784c4af2dab692693d29bdf78bcf5153401000000fd55f20f44ec08dd9460f88969b94=
3ab3cd8020000000000

So the tuple is frozen AND updated, with the new version at (47,13).  Odd.
What do you see at (47,13)?

> I couldn't delete it in the standard way (delete from "InboxState" where =
ctid =3D '(5,51)') - it also hangs.
>=20
> But I can freeze it through pg_surgery.
>=20
> # select heap_force_freeze('"InboxState"'::regclass, ARRAY['(5, 51)']::ti=
d[]);
>=20
> Output after freeze:
> digitalarchive=3D# SELECT * FROM heap_page_items(get_raw_page('"InboxStat=
e"', 5)) where lp =3D 51;
> -[ RECORD 1 ]------------------------------------------------------------=
---------------------------------------------------------------------------=
----
> lp =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 51
> lp_off =C2=A0 =C2=A0 =C2=A0| 3760
> lp_flags =C2=A0 =C2=A0| 1
> lp_len =C2=A0 =C2=A0 =C2=A0| 100
> t_xmin =C2=A0 =C2=A0 =C2=A0| 2
> t_xmax =C2=A0 =C2=A0 =C2=A0| 0
> t_field3 =C2=A0 =C2=A0| 0
> t_ctid =C2=A0 =C2=A0 =C2=A0| (5,51)
> t_infomask2 | 11
> t_infomask =C2=A0| 2817
> t_hoff =C2=A0 =C2=A0 =C2=A0| 32
> t_bits =C2=A0 =C2=A0 =C2=A0| 1111011000000000
> t_oid =C2=A0 =C2=A0 =C2=A0 |
> t_data =C2=A0 =C2=A0 =C2=A0| \x3e8a7c00000000000100000090877a16b4b308dd94=
60898784c4af2dab692693d29bdf78bcf5153401000000fd55f20f44ec08dd9460f88969b94=
3ab3cd8020000000000

Now it is only frozen.

> After that, queries to the table started to work normally.
> I'll note that there are absolutely no errors in the postgres logs, check=
sums are enabled, there are no errors for them either.

I would still recommend a dump and restore to get rid of the data corruptio=
n.

> It seems that this is a bug.

Possible.

Yours,
Laurenz Albe