Re: query hangs out

Thanks for the advice.
I tried to remove all indexes and constraints from the table - it did not
help.
I have a copy of the data (before truncate) - I can test any hypothesis

вт, 20 мая 2025 г. в 18:25, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>:

> On Tue, 2025-05-20 at 16:48 +0300, Антон Глушаков wrote:
> > I encountered a very strange behavior.
> > For any query (even a simple count(*) to one specific table (a small
> 30MB table with 3 indexes,
> > without any specific data types - everything is standard out of the box
> vanilla Postgres) -
> > the query hangs dead. Waited more than 24 hours - the query did not
> complete).
> >
> >
> > Similarly, the vacuum process to the table hangs.
> > Only Kill -9 with a full restart helps
> >
> > I get a backtrace, from it - I then examined the pg_multixact directory,
> which at the time of
> > the problem had swelled to 900MB and had several thousand files.
> > I excluded long and inactive transactions, as well as prepared
> statements.
> >
> > The workaround in the end was this - truncate the table (it was
> successful), then vacuum freeze
> > each DB, and after that the files from pg_multixact disappeared.
> >
> > What could it be? vacuum\freeze\mulitxact settings are default.
> > At the same time, the value pg_database.datminmxid=1
> > Could the problem with the hang be related to the many old files in
> pg_multixact ? (judging by the backtrace - yes)
>
> I can't say for certain, but I have seen cases like that where index
> corruption sent
> processes into an endless loop. Next time you could try to rebuild the
> indexes.
>
> Yours,
> Laurenz Albe
>