From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Rintaro.Ikeda@nttdata.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: Undetected deadlock between client backend and startup processes on a standby (Previously, Undetected deadlock between primary and standby processes)
Date: Sun, 10 Mar 2024 21:43:11 +0100
Message-ID: <ea96bc84-e242-4179-a440-9d4b8a7bae9f@enterprisedb.com> (raw)
In-Reply-To: <OS7PR01MB117020BA8AFBA8320A3F615A6CE232@OS7PR01MB11702.jpnprd01.prod.outlook.com>
References: <OS7PR01MB11702355B9A28CE07242507B6CE422@OS7PR01MB11702.jpnprd01.prod.outlook.com>
<OS7PR01MB117020BA8AFBA8320A3F615A6CE232@OS7PR01MB11702.jpnprd01.prod.outlook.com>
On 3/4/24 09:35, Rintaro.Ikeda@nttdata.com wrote:
> Hi,
>
> I correct the previous bug report [1] to provide a more accurate
> description. The bug report demonstrated undetected deadlock between
> client backend and startup processes on a standby server. (The title
> in the previous bug report is "Undetected deadlock between primary
> and standby processes". But this was wrong. Actually, this should be
> noted that "Undetected deadlock between client backend and startup
> process on a standby server".)
>
> After the procedures proposed in my bug report [1], a recovery
> conflict is present because the tablespace which startup process
> tries to drop is used by cliend backend process in standby. We see
> the pg_stat_activity (shown below), which implies a deadlock. A
> client backend process waits for AccessExclusiveLock to be released.
> Startup process waits for recovery conflict resolution for dropping
> the tablespace. This deadlock is not resolved after deadlock_timeout
> passes.
>
> (Standby server)
> 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
>
>
> This deadlock is similar to the previously identified and patched
> issue [2], which also involved an undetected deadlock between
> backend process and recovery on a standby server. I think the
> deadlock explained in this report should be detected and resolved.
>
Thanks for the report.
So what are the steps to reproduce this? The previous message did all
kinds of stuff on the primary and then got stuck on pg_switch_wal() on
the primary, but this updated seems to do stuff on the standby and gets
the lockup there.
It seems similar in the sense that it's about interaction between
recovery and a regular backend, but unfortunately
ResolveRecoveryConflictWithVirtualXIDs does not wait for a lock, it just
checks if the XID is still running, so it's invisible to the deadlock
detector :-(
But it's still checked against max_standby_streaming_delay, which should
resolve the deadlock (unless set to -1 to allow infinite delays) at some
point, right?
Also, I'm not very familiar with ResolveRecoveryConflictWithVirtualXIDs,
but it seems it's doing a busy wait. I wonder if that's a good idea, but
it's independent of this bug report.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tomas.vondra@enterprisedb.com, Rintaro.Ikeda@nttdata.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Undetected deadlock between client backend and startup processes on a standby (Previously, Undetected deadlock between primary and standby processes)
In-Reply-To: <ea96bc84-e242-4179-a440-9d4b8a7bae9f@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox