public inbox for [email protected]
help / color / mirror / Atom feedFrom: Антон Глушаков <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: ikramuddin <[email protected]>
Cc: [email protected]
Subject: Re: query hangs out
Date: Wed, 21 May 2025 16:27:01 +0300
Message-ID: <CAHnOmafmSVpxJGcP6SiVP=4BbdUAspp6K07T9rSvNyae6tk4WQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHnOmadn1UB-t-=Umd_TSEZ=kw48=ecX3EnesABxbPdboB-ZUQ@mail.gmail.com>
<[email protected]>
<CAHnOmafAW_Dqc8NEkmi=HOOMp3xf1DZdtOXauU2c1N5hq9BnVw@mail.gmail.com>
<CAL9MbytuyCsNoKK4Aus8zsXYiBz+G+YAm+Gvoa1Pz+SvGO7fBA@mail.gmail.com>
<CAHnOmacs_JB_wjA9g=PZyuH5jtbFb_qkf-dS7h7H4bkS4nTQeg@mail.gmail.com>
<[email protected]>
> 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 =
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 |
\x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf78bcf5153401000000ad43d6a5273608dd947a749769b943ab3cd8020001000000
> 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.
ср, 21 мая 2025 г. в 15:38, Laurenz Albe <[email protected]>:
> On Wed, 2025-05-21 at 12:06 +0300, Антон Глушаков 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 = 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 |
> \x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf78bcf5153401000000fd55f20f44ec08dd9460f88969b943ab3cd8020000000000
>
> 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 = '(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=# SELECT * FROM
> heap_page_items(get_raw_page('"InboxState"', 5)) where lp = 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 |
> \x3e8a7c00000000000100000090877a16b4b308dd9460898784c4af2dab692693d29bdf78bcf5153401000000fd55f20f44ec08dd9460f88969b943ab3cd8020000000000
>
> 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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: query hangs out
In-Reply-To: <CAHnOmafmSVpxJGcP6SiVP=4BbdUAspp6K07T9rSvNyae6tk4WQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox