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 1uHjTi-008SVB-Ka for pgsql-admin@arkaria.postgresql.org; Wed, 21 May 2025 13:27:26 +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 1uHjTh-006t7P-Id for pgsql-admin@arkaria.postgresql.org; Wed, 21 May 2025 13:27:25 +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 1uHjTh-006t70-16 for pgsql-admin@lists.postgresql.org; Wed, 21 May 2025 13:27:25 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uHjTe-00092T-1l for pgsql-admin@lists.postgresql.org; Wed, 21 May 2025 13:27:23 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-e7569ccf04cso6007342276.0 for ; Wed, 21 May 2025 06:27:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747834042; x=1748438842; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=68fMPaES+1HQD0+KBPBcdVQcDCpsvnoH5gnHrhPr5gE=; b=f8SlsiVqQmzhTYwB6Usj8Jn9pUOL+5rI+1v0wjYbRsht6aN/97S5/FYZ3kkzGnVHis 2UfRyXRW0qFTB0LCIt0AwsFKEWKqPywlSFik/gADEdiGvkUjamQq+0uJBmVFc7mmF7KB AYgca34ktRLixL/bhyTL0gZhYYw4AAHn/BVs/432OAJGD7tAmVn2+qLRmqwgDc56reK9 OWsJ+TjtM9KgGx7SD3EdOkhaYAYHr6sYJUcNdrK5FC7/eo+yPPX+CG5FlrUNTR4fHxMu DwDz1rouRBvV1hdOVYC4cPq44A7iTWMXWtdBRLCJ0DTp3b+NWrV6BF4Yu8FQ8/Pdoi2D FMKw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747834042; x=1748438842; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=68fMPaES+1HQD0+KBPBcdVQcDCpsvnoH5gnHrhPr5gE=; b=mH2GrZiei8KLq3ihlfb+w0bZqNElUWobcbGgTcLcZFVbrx1PFuw5V5oslaW+lg+SR1 a5ShQa0BwsHvgtwe1dsjXb/IKYaMxPlePjNskiDOf16mnsvfy6UdgdZceEZT/oRDOz3F ORxRyL9RTDLBzffkMbbIrLyGrtUGPnr1DsbIgenRPyJMqS/f9sIwoTdmJdI4UwzuYV+I WHICMOkILqn2a6adOKRUfwTsSiKvv6FOfT5vopRRIx2w0QECvFr6SYmwAubGpnddDndx abEEndRLjunOmUVrqMPhdXuTKn2PVORVuN/0PsjlhYiqtjN1Y7sq3TWwsZgdrt+T2QBa z/5Q== X-Forwarded-Encrypted: i=1; AJvYcCVyqwyTnZ5lYvnBrjWH+qHPHse1X6PDZr7JN0PUquRRhMUkGGQsv9qR+Q6oValUZsx/qpy9SMBCCHxWdw==@lists.postgresql.org X-Gm-Message-State: AOJu0YxzFOt7tHDdns9desBj/gJ+/ZPMdut+1/I2gG7HzotY+pKXUh+K S7fRl0xE4VopTvE1loKa20TkZZz1+zBY13Y/23MBjvFrwBWhNWStN/ZMFkZYTKuru/9KUZhpqXD 80YUmsAU/KirggWCu9+h17dGqj7XK0SU= X-Gm-Gg: ASbGncsf51dlWMk+QGEBYg01n7IzCPOpmomqmOAM2DaJ5t9lv6c4s6LuYKioyeZYZPn z63FKkFFueZjkoVLMI/mJhMzgZg/BLtX7VYkj7HQUXkdJ4sCggBIEmbfEfNaisCqc4s0txH3rOB 5eQGToO2NUxoAfHSxDvHyGFvnCzDrU3DFHNY4EPzwaaXY= X-Google-Smtp-Source: AGHT+IF4RfkkR/FHkWDkgvG1icj+jazFkwOnkOun4Hf8bOH7mmuNXjpItxMEu75J0uPxUAO09fd+0MxBEen9lxFiTX8= X-Received: by 2002:a05:6902:1b0a:b0:e7b:82d2:7a28 with SMTP id 3f1490d57ef6-e7b82d27bb5mr24579883276.15.1747834041614; Wed, 21 May 2025 06:27:21 -0700 (PDT) MIME-Version: 1.0 References: <32ad0fda77629362dbdc90136e6d5f667d496e01.camel@cybertec.at> <18617cd83b190c4209a9b16597aaacbfa7ba4df8.camel@cybertec.at> In-Reply-To: <18617cd83b190c4209a9b16597aaacbfa7ba4df8.camel@cybertec.at> From: =?UTF-8?B?0JDQvdGC0L7QvSDQk9C70YPRiNCw0LrQvtCy?= Date: Wed, 21 May 2025 16:27:01 +0300 X-Gm-Features: AX0GCFuYAZbVNc-30Tv84apJIKYDJg_WMgLRakwsgi2r23T_kv7u8hPgMjgsYTY Message-ID: Subject: Re: query hangs out To: Laurenz Albe Cc: ikramuddin , pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002d4b010635a553b5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002d4b010635a553b5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > 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 lp = =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 | \x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf78b= cf5153401000000ad43d6a5273608dd947a749769b943ab3cd8020001000000 > I would still recommend a dump and restore to get rid of the data corruption. Yes, thank you. It was a non-production environment, the data was recreated= . =D1=81=D1=80, 21 =D0=BC=D0=B0=D1=8F 2025=E2=80=AF=D0=B3. =D0=B2 15:38, Laur= enz Albe : > 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. > > > > As a result, I determined that the problem is on page 5 of the table (I > made SELECT ctid selections until it hangs). > > Then I tried to delete rows by ctid (5, 0-100) from the table until I > found the problematic row. > > > > Content through pageinspect: > > # SELECT * FROM heap_page_items(get_raw_page('"InboxState"', 5)) where > lp =3D 51; > > -[ RECORD 1 > ]------------------------------------------------------------------------= ------------------------------------------------------------------- > > lp | 51 > > lp_off | 3760 > > lp_flags | 1 > > lp_len | 100 > > t_xmin | 136269917 > > t_xmax | 66664135 > > t_field3 | 0 > > t_ctid | (47,13) > > t_infomask2 | 8203 > > t_infomask | 4929 > > t_hoff | 32 > > t_bits | 1111011000000000 > > t_oid | > > t_data | > \x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf7= 8bcf5153401000000fd55f20f44ec08dd9460f88969b943ab3cd8020000000000 > > 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" wher= e > ctid =3D '(5,51)') - it also hangs. > > > > But I can freeze it through pg_surgery. > > > > # select heap_force_freeze('"InboxState"'::regclass, ARRAY['(5, > 51)']::tid[]); > > > > Output after freeze: > > digitalarchive=3D# SELECT * FROM > heap_page_items(get_raw_page('"InboxState"', 5)) where lp =3D 51; > > -[ RECORD 1 > ]------------------------------------------------------------------------= ------------------------------------------------------------------- > > lp | 51 > > lp_off | 3760 > > lp_flags | 1 > > lp_len | 100 > > t_xmin | 2 > > t_xmax | 0 > > t_field3 | 0 > > t_ctid | (5,51) > > t_infomask2 | 11 > > t_infomask | 2817 > > t_hoff | 32 > > t_bits | 1111011000000000 > > t_oid | > > t_data | > \x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf7= 8bcf5153401000000fd55f20f44ec08dd9460f88969b943ab3cd8020000000000 > > 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, > checksums are enabled, there are no errors for them either. > > I would still recommend a dump and restore to get rid of the data > corruption. > > > It seems that this is a bug. > > Possible. > > Yours, > Laurenz Albe > --0000000000002d4b010635a553b5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> What do you see at (47,13)?

I have restored the data before the freeze, here is the content (4= 7.13)
SELECT * FROM heap_page_items(get_raw_page('= ;"InboxState"', 47)) where lp =3D 13;
-[ RECORD 1 ]-------= ---------------------------------------------------------------------------= ---------------------------------------------------------
lp =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| 13
lp_off =C2=A0 =C2=A0 =C2=A0| 4632
lp_fla= gs =C2=A0 =C2=A0| 1
lp_len =C2=A0 =C2=A0 =C2=A0| 100
t_xmin =C2=A0 = =C2=A0 =C2=A0| 136385644
t_xmax =C2=A0 =C2=A0 =C2=A0| 136385520
t_fie= ld3 =C2=A0 =C2=A0| 0
t_ctid =C2=A0 =C2=A0 =C2=A0| (47,13)
t_infomask2= | 11
t_infomask =C2=A0| 8337
t_hoff =C2=A0 =C2=A0 =C2=A0| 32
t_bi= ts =C2=A0 =C2=A0 =C2=A0| 1111011000000000
t_oid =C2=A0 =C2=A0 =C2=A0 |t_data =C2=A0 =C2=A0 =C2=A0| \x3e8a7c00000000000100000090877a16b4b308dd94= 60898784c4af2dab692693d29bdf78bcf5153401000000ad43d6a5273608dd947a749769b94= 3ab3cd8020001000000


> I would still recommend a dump and restore to get rid of the = data corruption.
Yes, thank you. It was a non-production env= ironment, the data was recreated.

=D1=81=D1=80, 21 =D0= =BC=D0=B0=D1=8F 2025=E2=80=AF=D0=B3. =D0=B2 15:38, Laurenz Albe <laurenz.albe@cybertec.at>:
<= /div>
On Wed, 2025-05-21 a= t 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.
>
> As a result, I determined that the problem is on page 5 of the table (= I made SELECT ctid selections until it hangs).
> Then I tried to delete rows by ctid (5, 0-100) from the table until I = found the problematic row.
>
> Content through pageinspect:
> # SELECT * FROM heap_page_items(get_raw_page('"InboxState&quo= t;', 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| \x3e8a7c00000000000100000090877a16b4b308d= d9460898784c4af2dab692693d29bdf78bcf5153401000000fd55f20f44ec08dd9460f88969= b943ab3cd8020000000000

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

> I couldn't delete it in the standard way (delete from "InboxS= tate" where ctid =3D '(5,51)') - it also hangs.
>
> But I can freeze it through pg_surgery.
>
> # select heap_force_freeze('"InboxState"'::regclass,= ARRAY['(5, 51)']::tid[]);
>
> Output after freeze:
> digitalarchive=3D# SELECT * FROM heap_page_items(get_raw_page('&qu= ot;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| 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| \x3e8a7c00000000000100000090877a16b4b308d= d9460898784c4af2dab692693d29bdf78bcf5153401000000fd55f20f44ec08dd9460f88969= b943ab3cd8020000000000

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= , checksums 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
--0000000000002d4b010635a553b5--