On 23 February 2015 at 11:12, Simon Riggs <simon@xxxxxxxxxxxxxxx> wrote:
On 23 February 2015 at 08:19, Drazen Kacar <drazen.kacar@xxxxxxxxxxx> 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?
