From: n.kobzarev@aeronavigator.ru
To: 'Laurenz Albe' <laurenz.albe@cybertec.at>
To: pgsql-general@lists.postgresql.org
Subject: RE: Unnecessary locks for partitioned tables
Date: Wed, 9 Nov 2022 16:40:01 +0300
Message-ID: <01a401d8f440$c48bf320$4da3d960$@aeronavigator.ru> (raw)
In-Reply-To: <b6a944c1a7f58d2189b008ac4a523b00c1e124ab.camel@cybertec.at>
References: <012801d8f42c$06ea5af0$14bf10d0$@aeronavigator.ru>
<b6a944c1a7f58d2189b008ac4a523b00c1e124ab.camel@cybertec.at>
> > 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
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 ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: n.kobzarev@aeronavigator.ru, laurenz.albe@cybertec.at
Subject: Re: Unnecessary locks for partitioned tables
In-Reply-To: <01a401d8f440$c48bf320$4da3d960$@aeronavigator.ru>
References: <012801d8f42c$06ea5af0$14bf10d0$@aeronavigator.ru> <b6a944c1a7f58d2189b008ac4a523b00c1e124ab.camel@cybertec.at> <01a401d8f440$c48bf320$4da3d960$@aeronavigator.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox