create database hangs forever on WSL - autovacuum deadlock?

From: Alicja Kucharczyk <zaledwie10minut(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: create database hangs forever on WSL - autovacuum deadlock?
Date: 2022-01-08 07:10:53
Message-ID: CAM=sWa7JA86RGcPifd_b1oQu2BhqRyG02uyhmFY3Av4JYtstkg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)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(at)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(at)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(at)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(at)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(at)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(at)postgres LOG: duration:
767877.903 ms statement: create database mytest1;

pozdrawiam,
best regards,
mit freundlichen Grüßen,
Alicja Kucharczyk

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ali Koca 2022-01-08 13:21:59 How are md5.h: pg_md5_hash() function and below functions working?
Previous Message ewie 2022-01-08 06:22:19 Re: Timestamp with precision output format with trailing zeros