Re: StandbyAcquireAccessExclusiveLock doesn't necessarily

On 11 September 2018 at 16:11, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Sep 11, 2018 at 10:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > The point of the previous coding here was that perhaps there's some
> > range of number-of-locks-needed where kicking hot-standby queries
> > off of locks would allow recovery to proceed. However, it is (as
> > far as I know) unproven that that actually works, let alone is
> > effective enough to justify maintaining very-hard-to-test code for.
> > The field demand for such behavior can be measured by the number of
> > complaints we've had since breaking it in 9.6, namely zero.
>

Agreed.

> > So no, I do not want to re-implement and maintain that behavior on
> > the strength of just a guess that sometimes it might be useful.
> > If somebody else feels a burning passion for it, they can do the
> > work --- but I'd be inclined to argue that it'd be a HEAD-only
> > performance improvement, not a back-patchable bug fix.
>
> Mmph. Well, I'm not in love with that position, because having the
> standby exit in such a way as to require manual intervention when an
> automated recovery strategy is possible is undesirable, but I'm not
> volunteering to do the work, either, so maybe we don't have many
> alternatives.
>
> I think, though, that it is pretty obvious that the intermediate zone
> which you refer to as "perhaps" existing does indeed exist. Queries
> running on the standby consume lock table slots, and killing them
> frees up those slots. Q.E.D.
>
> I suspect the reason why this hasn't come up much in practice is
> because (1) there are guards against setting various GUCs including
> max_connections and max_locks_per_transaction lower on the standby
> than they are set on the master (cf. CheckRequiredParameterValues) and
> (2) if those guards are not quite sufficient to ensure that the lock
> table on the standby is always as large there as it is on the master,
> it doesn't end up mattering because the number of AccessExclusiveLocks
> on relations is generally going to be a very small percentage of the
> total number of lock table slots. But if somebody's interested in
> working on this, maybe we could construct a TAP test case that
> involves the master running "BEGIN; LOCK TABLE a1, a2, a3, a4, ....;"
> concurrently with some "select pg_sleep from empty1, empty2, ..."
> queries on the standby.

max_connections on standby must be same or higher on standby

standby users are not allowed to request strong locks, so the only strong
locks coming through are AccessExclusiveLocks from master.

max_locks_per_transaction is minimum 10 and it would be reasonable to
assume it is set to same or higher than master also.

Workloads on master are also subject to memory errors, so excessive use of
locks on master would hit limits and that would naturally prune the
workload before it hit the standby.

It's hard to see how any reasonable workload would affect the standby. And
if it did, you'd change the parameter and restart, just like you already
have to do if someone changes max_connections on master first.

So I don't see any problem or anything abnormal in what Tom suggests.

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