canceling statement due to conflict with recovery

Craig Ringer
Craig Ringer


You got this error on a standby:

ERROR: canceling statement due to conflict with recovery
DETAIL: User was holding a relation lock for too long

The reason for this error is:

  1. A long-running SELECT query begins on the standby which will obtain an AccessSharedLock on the relations being queried.
  2. Something on the master server obtains an AccessExclusiveLock on a table being queried in #1. Because this is an AccessExclusiveLock, the standby server must be made aware, so the master server writes a record inWALto mention that this lock was obtained.
  3. Master generates additional WAL.
  4. Standby receives the latest WAL from master and sees that an exclusive lock must be taken, but the lock conflicts with the lock taken in #1. Standby server waits for #1 to allow it to finish.
  5. on the standby, if max_standby_streaming_delay (or max_standby_archive_delay) are > 0, then after this time has elapsed since #4, then all conflicting queries (which includes #1) will be terminated with the message that you're receiving. This will allow replay to continue replaying the access exclusive lock.


Possible solutions:

  1. Never run any queries on the standby server which take longer to execute than max_standby_streaming_delay or max_standby_archive_delay.
  2. Increase max_standby_streaming_delay and max_standby_archive_delay so that they are both higher than the longest query which will ever run on the standby server. This may cause replay to be delayed for extended periods of time.
  3. Don't obtain AccessExclusiveLocks on the master server.
  4. Run queries which may take longer than max_standby_streaming_delay or max_standby_archive_delay on the master server instead.

Root Cause

User was holding a relation lock for too long

Was this article helpful?

0 out of 0 found this helpful