Re: query hangs out

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Антон Глушаков <a(dot)glushakov86(at)gmail(dot)com>, ikramuddin <ikram(dot)amani815(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: query hangs out
Date: 2025-05-21 12:38:20
Message-ID: 18617cd83b190c4209a9b16597aaacbfa7ba4df8.camel@cybertec.at
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Антон Глушаков 2025-05-21 13:27:01 Re: query hangs out
Previous Message Антон Глушаков 2025-05-21 09:06:54 Re: query hangs out