Re: Query cancellation on hot standby because of buffer pins

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?