Sender: Will Storey <wstorey@gmail.com>
Date: Sun, 8 Dec 2024 16:42:38 -0800
From: Will Storey <will@summercat.com>
To: pgsql-general@lists.postgresql.org
Subject: Cancelled query due to buffer deadlock with recovery
Message-ID: <Z1Y8_q_q41S8AKIF@dev.null>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/Z1Y8_q_q41S8AKIF%40dev.null>
Precedence: bulk

Hello!

I am running Postgres 16.6 on a primary and several standby servers. I
recently observed 3 queries being cancelled with this error:

ERROR: canceling statement due to conflict with recovery (SQLSTATE 40P01)

There was this detail with the error:

User transaction caused buffer deadlock with recovery

The standbys have hot_standby_feedback=on and
max_standby_streaming_delay=1min.

As far as I can tell, there were no long running transactions at the time
on either the primary or the standby in question. I did not see any
replication delay according to monitoring.

The queries that were cancelled were reading catalog tables - pg_inherits
joined with pg_class. I typically expect them to complete quickly (they
have a 100ms statement timeout).

I understand that canceling due to conflicts should be expected when using
hot standbys, but I'm struggling to understand why it happened in this
case.

What do you think?

Thank you!

Will