Re: Declarative partitioning - another take

On 2016/08/31 16:17, Robert Haas wrote:
> On Wed, Aug 31, 2016 at 12:37 PM, Amit Langote wrote:
>> What I was trying to understand is why this would not be possible
>> with a design where partition bound is stored in the catalog as a property
>> of individual partitions instead of a design where we store collection of
>> partition bounds as a property of the parent.
>
> From the point of view of feasibility, I don't think it matters very
> much where the property is stored; it's the locking that is the key
> thing. In other words, I think this *would* be possible if the
> partition bound is stored as a property of individual partitions, as
> long as it can't change without a lock on the parent.
>
> 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.

Thanks,
Amit