I'm seeing query cancellation on hot standby (Postgres 9.3.4, configured with hot_standby_feedback=on) because of buffer pins being held for too long.
Database message looks like this:
ERROR: canceling statement due to conflict with recovery
DETAIL: User was holding shared buffer pin for too long.
STATEMENT: ...
I don't understand what kind of activity (either on primary or on standby) can cause this. The documentation about cancellation on standby lists access exclusive locks, vacuum and drops of tablespace or database on the primary as possible causes for cancellation on the standby.
I'm sure that neither of those things was happening on the primary when the query was cancelled on standby. I also don't know what "too long" means? That is, what's the timeout? I've looked at the source a bit and it seems that the timeout is the value of deadlock_timeout parametar, but I'm not very sure about that.
So can somebody explain why the query needs to be cancelled if it pins the buffer for too long and what exactly "too long" is?
The only thing I managed to find on the net is:
http://t61224.db-postgresql-bugs.dbtalk.info/bug-7546-backups-on-hot-standby-cancelled-despite-hot-standby-on-t61224.htmlBut that doesn't explain why this kind of cancellation happens.
