Re: ERROR: too many dynamic shared memory segment

On Wed, Nov 18, 2020 at 4:21 AM Ben Kanouse <kanobt61(at)gmail(dot)com> wrote:
> My database is experiencing a 'ERROR: too many dynamic shared memory
> segment' error from time to time. It seems to happen most when traffic
> is high, and it happens with semi-simple select statements that run a
> parallel query plan with a parallel bitmap heap scan. It is currently
> on version 12.4.
>
> Here is an example query: https://explain.depesz.com/s/aatA

Hmm, not sure how this plan could cause the problem, even if running
many copies of it. In the past, this problem has been reported from
single queries that had a very high number of separate Gather nodes,
or very very large parallel hash joins, or, once you've hit the error
a few times those ways, also due to an ancient DSM leak that was fixed
in 93745f1e (fix present in 12.4).

> I noticed in version 13.1 there is a commit that changes the
> multiplier from 2 to 5:
> https://github.com/postgres/postgres/commit/d061ea21fc1cc1c657bb5c742f5c4a1564e82ee2
>
> maxitems = 64 + 5 * MaxBackends
>
> Should this commit be back-ported to earlier versions of postgres to
> prevent this error in other versions?

Yeah, that seems like a good idea anyway. I will do that tomorrow,
barring objections.