Date: Fri, 22 Jul 2022 15:07:29 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Marco Boeringa <marco@boeringa.demon.nl>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in
 PostgreSQL 14.3/4?
Message-ID: <Ytr1cTgWLHhk40DB@momjian.us>
References: <85acb61d-4cbf-7544-c734-429993257547@boeringa.demon.nl>
 <791d1850-f337-8914-1ba0-512f4e0fef26@boeringa.demon.nl>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <791d1850-f337-8914-1ba0-512f4e0fef26@boeringa.demon.nl>
Archived-At: 
 <https://www.postgresql.org/message-id/Ytr1cTgWLHhk40DB%40momjian.us>
Precedence: bulk

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