> > 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 >
areheld 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
onlya 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
makethe same optimization in case of generic plan too. Especially when database already identifies possibility of
partitionpruning.
Also, to query partitioned table metadata it is not required to lock all partitions, but parent only. Isn't it?
Nikolay