Re: Creating foreign key on partitioned table is too slow

On 2019-Nov-13, Alvaro Herrera wrote:

> On 2019-Nov-13, Tom Lane wrote:
> 
> > (BTW, a different question one could ask is exactly why
> > RelationBuildPartitionDesc is so profligate of leaked memory.)
> 
> The original partitioning code (f0e44751d717) decided that it didn't
> want to bother with adding a "free" routine for PartitionBoundInfo
> structs, maybe because it had too many pointers, so there's no way for
> RelationBuildPartitionDesc to free everything it allocates anyway.  We
> could add a couple of pfrees and list_frees here and there, but for the
> main thing being leaked we'd need to improve that API.

Ah, we also leak an array of PartitionBoundSpec, which is a Node.  Do we
have any way to free those?  I don't think we do.

In short, it looks to me as if this function was explicitly designed
with the idea that it'd be called in a temp mem context.

I looked at d3f48dfae42f again per your earlier suggestion.  Doing that
memory context dance for partitioned relations does seem to fix the
problem too; we just need to move the context creation to just after
ScanPgRelation, at which point we have the relkind.  (Note: I think the
problematic case is the partitioned table, not the partitions
themselves.  At least, with the attached patch the problem goes away.  I
guess it would be sensible to research whether we need to do this for
relispartition=true as well, but I haven't done that.)

There is indeed some leakage for relations that have triggers too (or
rules), but in order for those to become significant you would have to
have thousands of triggers or rules ...  and in reasonable designs, you
just don't because it doesn't make sense.  But it is not totally
unreasonable to have lots of partitions, and as we improve the system,
more and more people will want to.


Aside: while messing with this I noticed that how significant pg_strtok
is as a resource hog when building partition descs (from the
stringToNode that's applied to each partition's partbound.)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services