create database hangs forever on WSL - autovacuum deadlock?

[Alicja Kucharczyk <zaledwie10minut@xxxxxxxxx>]

Hi All,

this is a problem from slack channel; it looks like a deadlock. Create database hangs infinitely showing a lock in pg_stat_activity and being mutually locked by autovacuum workers. executing pg_reload_conf couple times releasing the lock. Why sighup helps in this situation? is it a problem with windows memory management?

this is postgres running on windows wsl:  PostgreSQL 14.1 (Ubuntu 14.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

2022-01-08 07:24:06.892 CET [2347] LOG:  process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 1000.918 ms
2022-01-08 07:24:06.892 CET [2347] DETAIL:  Processes holding the lock: 2308, 2318. Wait queue: 2117, 2347.
pid              | 2117
wait_event_type  | Lock
wait_event       | object
state            | active
backend_xid      |
backend_xmin     | 749
query_id         |
query            | create database mytest1;
backend_type     | client backend

postgres  2347  0.0  0.0 221280  2388 ?        Ss   07:24   0:00 postgres: 14/main: autovacuum worker  waiting
postgres  2308  0.0  0.0 221284  2420 ?        Ss   07:15   0:00 postgres: 14/main: autovacuum worker  waiting
postgres  2318  0.0  0.0 221284  2420 ?        Ss   07:16   0:00 postgres: 14/main: autovacuum worker  waiting
postgres  2347  0.0  0.0 221280  2388 ?        Ss   07:24   0:00 postgres: 14/main: autovacuum worker  waiting

2022-01-08 07:36:44.359 CET [2409] postgres@postgres LOG:  duration: 0.183 ms  statement: SELECT pg_reload_conf();
2022-01-08 07:36:44.359 CET [1561] LOG:  received SIGHUP, reloading configuration files
2022-01-08 07:36:44.361 CET [2117] postgres@postgres LOG:  process 2117 acquired ShareLock on object 1 of class 1262 of database 0 after 759697.185 ms
2022-01-08 07:36:44.361 CET [2117] postgres@postgres STATEMENT:  create database mytest1;
2022-01-08 07:36:44.361 CET [2347] LOG:  process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 758470.143 ms
2022-01-08 07:36:44.361 CET [2347] DETAIL:  Process holding the lock: 2117. Wait queue: 2347.
2022-01-08 07:36:46.191 CET [2409] postgres@postgres LOG:  duration: 0.146 ms  statement: SELECT pg_reload_conf();
2022-01-08 07:36:46.191 CET [1561] LOG:  received SIGHUP, reloading configuration files
2022-01-08 07:36:46.193 CET [2347] LOG:  process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 760301.845 ms
2022-01-08 07:36:46.193 CET [2347] DETAIL:  Process holding the lock: 2117. Wait queue: 2347.
2022-01-08 07:36:47.532 CET [2409] postgres@postgres LOG:  duration: 0.113 ms  statement: SELECT pg_reload_conf();
2022-01-08 07:36:47.533 CET [1561] LOG:  received SIGHUP, reloading configuration files
2022-01-08 07:36:47.534 CET [2347] LOG:  process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 761643.434 ms
2022-01-08 07:36:47.534 CET [2347] DETAIL:  Process holding the lock: 2117. Wait queue: 2347.
2022-01-08 07:36:52.496 CET [2409] postgres@postgres LOG:  duration: 0.141 ms  statement: SELECT pg_reload_conf();
2022-01-08 07:36:52.496 CET [1561] LOG:  received SIGHUP, reloading configuration files
2022-01-08 07:36:52.499 CET [2347] LOG:  process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 766607.664 ms
2022-01-08 07:36:52.499 CET [2347] DETAIL:  Process holding the lock: 2117. Wait queue: 2347.
2022-01-08 07:36:52.542 CET [2117] postgres@postgres LOG:  duration: 767877.903 ms  statement: create database mytest1;

pozdrawiam,

best regards,

mit freundlichen Grüßen,

Alicja Kucharczyk