Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?

On Fri, Jul 22, 2022 at 09:56:06AM +0200, Marco Boeringa wrote:
> Unfortunately, after more testing, it turns out this issue still persists in
> PostgreSQL 14.4.
>
> I have now encountered exactly the same problem as described in the original
> issue below: One autovacuum session that never finishes with no wait event (or
> is just incredibly slow and not finishing after many hours although it should
> in minutes considering the relative small dataset and normal operation), and
> the "client backend" and "parallel worker" stuck on the same wait events as
> listed below  with the same "SELECT COUNT (*)" SQL statement.
>
> One thing to note as well, besides this being workstation level hardware with
> ECC RAM, is that I now also activated 'pg_checksums' on the PostgreSQL
> databases, and reloaded all data, so all data should now have checksums.  No
> PostgreSQL error at all is generated via the ODBC connection I use to access
> and update the database when this happens and PostgreSQL appears stuck on the
> autovacuum. So I guess this now means I can now pretty much exclude a hardware
> error, and this must be some software issue, considering the checksums.

You might want to run these queries and show us the output, in case it
suggests a cause:

SELECT version();

-- non-default server settings
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson