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

Hi Bruce,

As requested.

Note that this is on very capable hardware in the form of an HP Z840 
workstation with NVMe. I have processed the entire OpenStreetMap 
"Planet" file with this hardware and configuration with success, it 
actually fails on a much smaller Geofabrik "Italy" extract:

version |

---------------------------------------------------------------------------------------------------------------------------------+
PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit|

|name |current_setting |source              |

|--------------------------------|-----------------------------------------------------------------|--------------------|
|application_name                |DBeaver 22.1.2 - SQLEditor 
<Script-2.sql>                        |session             |
|autovacuum_work_mem |8000kB |configuration file  |
|checkpoint_completion_target |0.9 |configuration file  |
|checkpoint_timeout |5min |configuration file  |
|client_encoding |UTF8 |client              |
|cluster_name |14/main |configuration file  |
|cursor_tuple_fraction |1 |configuration file  |
|DateStyle                       |ISO, DMY |client              |
|default_statistics_target |1000 |configuration file  |
|default_text_search_config |pg_catalog.english |configuration file  |
|dynamic_shared_memory_type |posix |configuration file  |
|effective_cache_size |100GB |configuration file  |
|effective_io_concurrency |500 |configuration file  |
|extra_float_digits |3 |session             |
|jit |on |configuration file  |
|lc_messages |en_US.UTF-8 |configuration file  |
|lc_monetary |nl_NL.UTF-8 |configuration file  |
|lc_numeric |nl_NL.UTF-8 |configuration file  |
|lc_time |nl_NL.UTF-8 |configuration file  |
|listen_addresses                |localhost, |configuration file |
|log_destination |stderr |configuration file  |
|log_line_prefix                 |%m [%p] %q%u@%d |configuration file  |
|log_rotation_age |1h |configuration file  |
|log_rotation_size |10000kB |configuration file  |
|log_statement |none |configuration file  |
|log_timezone |Europe/Amsterdam |configuration file  |
|log_truncate_on_rotation |on |configuration file  |
|logging_collector |off |configuration file  |
|maintenance_io_concurrency |500 |configuration file  |
|maintenance_work_mem |8000MB |configuration file  |
|max_connections |1000 |configuration file  |
|max_parallel_maintenance_workers|28 |configuration file  |
|max_parallel_workers |128 |configuration file  |
|max_parallel_workers_per_gather |56 |configuration file  |
|max_stack_depth |2MB |environment variable|
|max_wal_senders |0 |configuration file  |
|max_wal_size |25GB |configuration file  |
|max_worker_processes |128 |configuration file  |
|min_wal_size |1GB |configuration file  |
|parallel_leader_participation |off |configuration file  |
|parallel_setup_cost |100 |configuration file  |
|parallel_tuple_cost |0.025 |configuration file  |
|password_encryption |md5 |configuration file  |
|port |5433 |configuration file  |
|random_page_cost |1 |configuration file  |
|search_path                     |osm, public, "$user" 
|session             |
|shared_buffers |75GB |configuration file  |
|ssl |off |configuration file  |
|ssl_cert_file |server.crt |configuration file  |
|ssl_key_file |server.key |configuration file  |
|synchronous_commit |off |configuration file  |
|temp_buffers |8000MB |configuration file  |
|temp_tablespaces |osm_i |configuration file  |
|TimeZone |Europe/Berlin |client              |
|track_activity_query_size |10000B |configuration file  |
|wal_compression |on |configuration file  |
|wal_level |minimal |configuration file  |
|work_mem |2000MB |configuration file  |


Op 22-7-2022 om 21:07 schreef Bruce Momjian:
> 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');
>