Introducing our new eBook: "Best Practices for Optimizing Postgres Query Performance"Download Now

B95: Canceling statement due to conflict with recovery

Category: Standby Server Events
SQLSTATE: 40001 (Class 40 - Transaction Rollback: serialization_failure)
Urgency: low

Example Postgres Log Output:

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
STATEMENT: SELECT 1

Explanation:

This error will occur when the standby server gets updates/deletes in the WAL stream that will invalidate data currently being accessed by a running query.

You will primary see this with long-running queries accessing tables with significant activity on the primary.

Recommended Action:

There are two approaches to avoid this kind of query cancellation:

1) Set hot_standby_feedback = on on the standby, which passed back information to the primary that certain rows are still needed in a table. This will cause bloat on the primary, but allows queries on the secondary to finish reliably.

2) Increase max_standby_archive_delay / max_standby_streaming_delay, which allows the standby server to intentionally increase replication lag to allow queries to finish.

Depending on your use case, either 1) or 2) makes sense - you may also be okay with queries occasionally being canceled, since you can simply try to resubmit the query and it may succeed then.

Learn More:


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →