On Tue, Sep 11, 2018 at 10:25 AM, Tom Lane <t...@sss.pgh.pa.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
