> > On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@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 Thank you for reply! But why it is not necessary in case of custom plan? Oh, I did not explicitly write that, in case of custom plan (first attempts or with force_custom_plan) database holds only a couple of locks! Why in this case it is sufficient to lock only one partition and parent table ? >From my perspective, if exists the case of custom plan that produces locks for >only one partition and parent, we can make the same optimization in case of >generic plan too. Especially when database already identifies possibility of >partition pruning. Also, to query partitioned table metadata it is not required to lock all partitions, but parent only. Isn't it? Nikolay
