pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 ?



view thread (6+ messages)  latest in thread

Message-ID: <01a401d8f440$c48bf320$4da3d960$@aeronavigator.ru>
Permalink:  ../01a401d8f440$c48bf320$4da3d960$@aeronavigator.ru/
Also on:    postgresql.org/message-id/01a401d8f440$c48bf320$4da3d960$@aeronavigator.ru

 · 

reply

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