Re: Spurious standby query cancellations

On 14 September 2015 at 12:00, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> It's now possible to fix this by putting a lock wait on the actual lock
>> request, which wasn't available when I first wrote that, hence the crappy
>> wait loop. Using the timeout handler would now be the preferred way to
>> solve this. We can backpatch that to 9.3 if needed, where they were
>> introduced.
>>
>> There's an example of how to use lock waits further down
>> on ResolveRecoveryConflictWithBufferPin(). Could you have a look at doing
>> it that way?
>>
>
> It looks like this will take some major surgery. The heavy weight lock
> manager doesn't play well with others when it comes to timeouts other than
> its own. LockBufferForCleanup is a simple retry loop, but the lock manager
> is much more complicated than that.
>

Not sure I understand this objection. I can't see a reason that my proposal
wouldn't work.

>
>> We probably also need to resurrect my earlier patch to avoid logging
>> AccessExclusiveLocks on temp tables.
>>
>
> I couldn't find that, can you provide a link?
>

http://www.postgresql.org/message-id/CA+U5nMJYGYS+kccJ+=aQxuC_G09hF6zsD-xhu5W9oqGNkbE6nQ@mail.gmail.com

> Another source of frequent AccessExclusiveLocks is vacuum truncation
> attempts. If a table has some occupied pages right at the end which are
> marked as all-visible, then forward scan doesn't visit them. Since it
> didn't visit them, it assumes they might be empty and truncatable and so
> takes the AccessExclusiveLock, only to immediately see that the last page
> is not empty. Perhaps the forward scan could be special-cased to never
> skip the final block of a table. That way if it is not empty, the
> truncation is abandoned before taking the AccessExclusiveLock.
>

We can probably get rid of that lock, but it will require lots of smoke and
mirrors to persuade scans that they don't actually need to scan as far as
they thought they did, because a VACUUM has checked and it knows those
blocks do not contain tuples visible to the scan. Which sounds a little
hairy.

I think Andres is working on putting an end of data watermark in shared
memory rather than using the end of physical file, which sounds like a
better plan.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services