Re: StandbyAcquireAccessExclusiveLock doesn't necessarily

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.
>
> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company