On Wed, 2022-11-09 at 14:11 +0300, n.kobzarev@aeronavigator.ru wrote:
> Recently I`ve been pushing into life a new project and immediately experienced an
> Out of shared memory error while querying partitioned tables.
>
> ERROR: out of shared memory
> Hint: You might need to increase max_locks_per_transaction.
>
> Ok, let`s increase max_locks_per_transaction, but why this type of query produces so much locks?
> Looks like DB issues locks for all the partitioned objects involved in query and ONLY AFTER THAT
> it does partition pruning.
Yes, of course. It needs an ACCESS SHARE lock when it looks at metadata
like the partition constraint, and locks are held until the end of the
transaction.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com