On 10/16/23 14:31, Craig McIlwee wrote:
That's what we've already done for the short term solution. It is somewhat in conflict with your statement regarding the number of lockable objects not holding still for long, though. As time goes on and our scheduled jobs automatically create new monthly partitions, or as our schema evolves, we may eventually hit the limits again. That's why we'd like to create some formula that can estimate the max_locks_per_transaction value we should configure (with the previously mentioned multiplier for safety / future proofing). An alternative would be to precreate all partitions we anticipate needing so we don't get surprises down the line, but then we incur extra planning cost for tables that will stay empty for months or even years.
Just set max_locks_per_transaction to Something Big, and go on to other business. 16384 worked for us, after slowly inching up towards 1000. Tom's response let me not worry about setting "so big", and we haven't had any problems since.
-- Born in Arizona, moved to Babylonia.
