Date: Sun, 21 Mar 2021 13:29:03 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Amit Langote <amitlangote09@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY
Message-ID: <20210321182903.GD11765@telsasoft.com>
References: <20210321181420.GC11765@telsasoft.com>
 <20210321182200.GA3837@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20210321182200.GA3837@alvherre.pgsql>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210321182903.GD11765%40telsasoft.com>
Precedence: bulk

On Sun, Mar 21, 2021 at 03:22:00PM -0300, Alvaro Herrera wrote:
> On 2021-Mar-21, Justin Pryzby wrote:
> 
> > On Sun, Mar 21, 2021 at 03:01:15PM -0300, Alvaro Herrera wrote:
> > > > But note that it doesn't check if an existing constraint "implies" the new
> > > > constraint - maybe it should.
> > > 
> > > Hm, I'm not sure I want to do that, because that means that if I later
> > > have to attach the partition again with the same partition bounds, then
> > > I might have to incur a scan to recheck the constraint.  I think we want
> > > to make the new constraint be as tight as possible ...
> > 
> > The ATTACH PARTITION checks if any existing constraint impilies the (proposed)
> > partition bounds, not just if constraints are equal.  So I'm suggesting to do
> > the same here.
> 
> So if we do that on DETACH, what would happen on ATTACH?

Do you mean what happens to the constraint that was already there ?
Nothing, since it's not ours to mess with.  Checking ImpliedBy() rather than
equal() doesn't change that.

I proposed this a few years ago for DETACH (without concurrently), specifically
to avoid the partition scans.
https://www.postgresql.org/message-id/20180601221428.GU5164@telsasoft.com
|The docs say: if detaching/re-attach a partition, should first ADD CHECK to
|avoid a slow ATTACH operation.  Perhaps DETACHing a partition could
|implicitly CREATE a constraint which is usable when reATTACHing?

-- 
Justin