Re: Creating foreign key on partitioned table is too slow

Hi,

On Thu, Aug 20, 2020 at 3:06 AM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> On 2020-Aug-19, Amit Langote wrote:
> > On Thu, Aug 6, 2020 at 4:25 PM kato-sho(at)fujitsu(dot)com
> > <kato-sho(at)fujitsu(dot)com> wrote:
> > > On Wednesday, August 5, 2020 9:43 AM I wrote:
> > > > I'll report the result before the end of August .
> > >
> > > I test v2-0001-build-partdesc-memcxt.patch at 9a9db08ae4 and it is ok.
> >
> > Is this patch meant for HEAD or back-patching? I ask because v13 got this:
> >
> > commit 5b9312378e2f8fb35ef4584aea351c3319a10422
> > Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > Date: Wed Dec 25 14:43:13 2019 -0500
> >
> > Load relcache entries' partitioning data on-demand, not immediately.
> >
> > which prevents a partitioned table's PartitionDesc from being rebuilt
> > repeatedly as would happen before this commit in Kato-san's case,
> > because it moves RelationBuildPartitionDesc out of the relcache flush
> > code path.
>
> Hmm, so this is a problem only in v11 and v12? It seems that putting
> the patch in master *only* is pointless. OTOH v11 had other severe
> performance drawbacks with lots of partitions, so it might not be needed
> there.
>
> I admit I'm hesitant to carry code in only one or two stable branches
> that exists nowhere else. But maybe the problem is serious enough in
> those branches (that will still live for quite a few years) that we
> should get it there.
>
> OTOH it could be argued that the coding in master is not all that great
> anyway (given the willingness for memory to be leaked) that it should
> apply to all three branches.

Fwiw, I am fine with applying the memory-leak fix in all branches down
to v12 if we are satisfied with the implementation.

That said, I don't offhand know any real world use case beside
Kato-san's that's affected by this leak. Kato-san's case is creating
a foreign key referencing a partitioned table with many partitions,
something that's only supported from v12. (You may have noticed that
the leak that occurs when rebuilding referencing table's PartitionDesc
accumulates while addFkRecurseReferenced is looping on referenced
table's partitions.)

--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com