Re: Query cancellation on hot standby because of buffer pins

On 23 February 2015 at 11:12, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On 23 February 2015 at 08:19, Drazen Kacar <drazen(dot)kacar(at)oradian(dot)com>
> wrote:
>
> > At the time they happened on the standby there was vacuuming of one
> > table participating in the select query on the primary.
>
> The VACUUM will have generated a WAL record that needs super exclusive
> access to the block. Since feedback was enabled that record would not
> have removed data visible by the query, but still needs to edit the
> block.
>
> The query was pinning that block, so this situation led to a delay on
> the standby, which then led to cancellation of the query.
>

OK, that makes sense.

So if I increase max_standby_archive_delay (or set it to infinite) that
shouldn't happen?

My problem with max_standby_archive_delay in this case is that I don't
understand relative priorities between obtaining a lock by queries and
obtaining a lock by vacuum (or other applications of WAL records).

If the first query obtains a lock that the vacuum needs and I have
sufficiently large max_standby_archive delay, I suppose the vacuum will
wait and the query won't be cancelled. What happens if another query that
needs a lock on the same table comes in while vacuum is waiting? Is there a
way to guarantee that the vacuum (which blocks application of subsequent
WAL records, I assume) will be the first one to get the lock? If not, then
(with max_standby_archive_delay=-1) it's possible that the application of
WAL records could wait indefinitely.

OTOH, if max_standby_archive_delay is finite and there is no way to
guarantee that vacuum gets its lock before other queries, then there's no
way to avoid query cancellation.

Am I getting something wrong here?