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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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