On 23 February 2015 at 10:35, Drazen Kacar <drazen.kacar@oradian.com> wrote:
>
>
> On 23 February 2015 at 11:12, Simon Riggs <simon@2ndquadrant.com> wrote:
>>
>> On 23 February 2015 at 08:19, Drazen Kacar <drazen.kacar@oradian.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?
Yes, you are confusing block and relation level locks. The contention
is at block level.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
