Query cancellation on hot standby because of buffer pins

From: Drazen Kacar <drazen(dot)kacar(at)oradian(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Query cancellation on hot standby because of buffer pins
Date: 2015-02-20 08:18:23
Message-ID: CAFxrd3tm_1_4UUL4ULnPzB04JJbvM1FdHpOMuF3fC7dW_8kJ2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.html

But that doesn't explain why this kind of cancellation happens.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2015-02-20 22:48:09 Re: Query cancellation on hot standby because of buffer pins
Previous Message Kevin Grittner 2015-02-18 20:16:52 Re: PostgreSQL File System Based Backup Restartability