Hi,
We found a undetected deadlock between a client backend process on the primary and a startup process on the standby during replication.
To reproduce the situation, follow the steps below. After completing the procedures, both the backend process and the startup process are left waiting for each other, resulting in a deadlock. The deadlock is not automatically detected and resolved.
1. (primary) setup the primary database cluster and create a table space
$ initdb -D data --no-locale --encoding=UTF8
$ pg_ctl -D data start
$ mkdir /tmp/hoge1
$ psql -c "CREATE TABLESPACE hoge LOCATION '/tmp/hoge1'"
2. (standby) setup the standby database cluster
$ pg_basebackup -D sby1 -R -T /tmp/hoge1=/tmp/hoge2 -X fetch
$ echo "port = 5433" >> sby1/postgresql.conf
$ echo "temp_tablespaces = 'hoge'" >> sby1/postgresql.conf
$ echo "max_standby_streaming_delay = -1" >> sby1/postgresql.conf
$ pg_ctl -D sby1 start
3. (primary) create table and get ACCESS EXCLUSIVE lock in primary
CREATE TABLE t();
BEGIN;
LOCK TABLE t IN ACCESS EXCLUSIVE MODE;
SELECT pg_switch_wal();
4. (standby) execute SELECT with ORDER BY clause to produce a temporary file.
BEGIN;
SET work_mem TO 64;
DECLARE mycur CURSOR FOR SELECT * FROM generate_series(1, 1000000) n ORDER BY n;
FETCH mycur;
SELECT * FROM t;
5. (new session on the primary) drop the table space in a new session other than the one which created table t().
DROP TABLESPACE hoge;
6. check the waiting event
(primary)
postgres=# select datid, datname, wait_event_type, wait_event, query, backend_type from pg_stat_activity ;
datid | datname | wait_event_type | wait_event | query | backend_type
-------+----------+-----------------+---------------------+-------------------------------------------------------------------------------------------------+------------------------------
5 | postgres | Client | ClientRead | SELECT pg_switch_wal(); | client backend
(standby)
postgres=# select datid, datname, wait_event_type, wait_event, query, backend_type from pg_stat_activity ;
datid | datname | wait_event_type | wait_event | query | backend_type
-------+----------+-----------------+----------------------------+-------------------------------------------------------------------------------------------------+-------------------
5 | postgres | Lock | relation | SELECT * FROM t; | client backend
| | IPC | RecoveryConflictTablespace | | startup
My environment is following.
PostgreSQL: 16.1
OS: Rocky Linux 9
With Regards,
Rintaro Ikeda
NTT DATA GROUP CORPORATION
