Re: Declarative partitioning - another take

On Tue, Sep 6, 2016 at 9:19 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Aug 31, 2016 at 1:05 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> However, it seems a lot better to make it a property of the parent
>>> from a performance point of view. Suppose there are 1000 partitions.
>>> Reading one toasted value for pg_class and running stringToNode() on
>>> it is probably a lot faster than scanning pg_inherits to find all of
>>> the child partitions and then doing an index scan to find the pg_class
>>> tuple for each and then decoding all of those tuples and assembling
>>> them into some data structure.
>>
>> Seems worth trying. One point that bothers me a bit is how do we enforce
>> partition bound condition on individual partition basis. For example when
>> a row is inserted into a partition directly, we better check that it does
>> not fall outside the bounds and issue an error otherwise. With current
>> approach, we just look up a partition's bound from the catalog and gin up
>> a check constraint expression (and cache in relcache) to be enforced in
>> ExecConstraints(). With the new approach, I guess we would need to look
>> up the parent's partition descriptor. Note that the checking in
>> ExecConstraints() is turned off when routing a tuple from the parent.
>
> [ Sorry for the slow response. ]
>
> Yeah, that's a problem. Maybe it's best to associate this data with
> the childrels after all - or halfway in between, e.g. augment
> pg_inherits with this information. After all, the performance problem
> I was worried about above isn't really much of an issue: each backend
> will build a relcache entry for the parent just once and then use it
> for the lifetime of the session unless some invalidation occurs. So
> if that takes a small amount of extra time, it's probably not really a
> big deal. On the other hand, if we can't build the implicit
> constraint for the child table without opening the parent, that's
> probably going to cause us some serious inconvenience.

Agreed. So I will stick with the existing approach.

Thanks,
Amit