Re: Partitioning WIP patch - Mailing list pgsql-hackers

On 26-02-2015 PM 05:20, Jim Nasby wrote:
> On 2/25/15 7:57 PM, Amit Langote wrote:
>> AIUI, as far as we stay with a design where partitions (children) are
>> individually planned, that might be OK. But, I guess things will get
>> more complicated. I think the role of a parent in planning would remain
>> limited to drive partition-pruning. Am I missing something?
> 
> Isn't the point of adding explicit partitioning to make it faster than
> plain inheritance? Presumably as part of that we'll eventually want to
> NOT plan children individually.
> 

Yes, we'd definitely want to get to a point where planning children
individually is not necessary. But I am afraid we will have to get there
a step at a time. IMHO, solving one problem of partition-pruning would
be a good start. And that will definitely be part of parent's planning
using partition bounds list (not pruning children one-by-one with
relation_excluded_by_constraints()).

>>> I would certainly prefer that we support the capabilities of inheritance
>>> along with partitioning (because in some cases you want both). But it's
>>> going to limit what we can do internally.
>>
>> Just to clarify are you referring to inheritance relationship between a
>> partitioned table and partitions?
> 
> Yes. If it helps, the exact use-case I have in mind is using list-based
> partitioning + additional columns in some/all children (different
> between children). For example, if you need to track different types of
> customer payment methods, you'd have a payment parent table, a list
> partition for credit & debit cards, a different list partition for bank
> accounts, etc.
> 
> The reason I'd like to do this with partitioning vs plain inheritance is
> presumably as we build out partitioning we'll get very useful things
> like the ability to have FKs to properly partitioned tables. Insert
> tuple routing could also be useful.
> 

Unless I'm missing something again, isn't allowing partitions to have
heterogeneous rowtypes a problem in the long run? I'm afraid I'm
confused as to your stand regarding inheritance vs. new partitioning. To
be specific, children with heterogeneous schemas sounds much like what
inheritance would be good for as you say. But then isn't that why we
have to plan children individually which you said new partitioning
should get away from?

>> With explicit partitioning, shouldn't we go in direction where we remove
>> some restrictions imposed by inheritance (think multiple inheritance)? I
>> recall a link Alvaro had started the discussion with think link to a
>> Tom's remark about something very related:
>>
>> http://www.postgresql.org/message-id/1598.1399826841@sss.pgh.pa.us
> 
> That post looks like Tom figured out a way to eliminate a problem that
> hurts inheritance, so that's good.
> 
> My fear is that at some point we'll hit a problem with partitioning that
> we can't solve in the inheritance model. If we allow inheritance
> features into partitioning now we'll painted into a corner. If we
> disallow those features now we can always re-enable them if we get to
> the point where we're in the clear.
> 
> Does that make sense?

Yes, it does. In fact, I do intend to keep them separate the first
attempt of which is to choose to NOT transform a PARTITION OF parent
clause into INHERITS parent. Any code that may look like it's trying to
do that is because the patch is not fully baked yet.

Regards,
Amit