Re: On partitioning

On Wed, Dec 3, 2014 at 6:30 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:
> Amit Langote wrote:
> > From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
>
> > > What is an overflow partition and why do we want that?
> >
> > That would be a default partition. That is, where the tuples that
> > don't belong elsewhere (other defined partitions) go. VALUES clause of
> > the definition for such a partition would look like:
> >
> > (a range partition) ... VALUES LESS THAN MAXVALUE
> > (a list partition) ... VALUES DEFAULT
> >
> > There has been discussion about whether there shouldn't be such a
> > place for tuples to go. That is, it should generate an error if a
> > tuple can't go anywhere (or support auto-creating a new one like in
> > interval partitioning?)
>
> In my design I initially had overflow partitions too, because I
> inherited the idea from Itagaki Takahiro's patch. Eventually I realized
> that it's a useless concept, because you can always have leftmost and
> rightmost partitions, which are just regular partitions (except they
> don't have a "low key", resp. "high key"). If you don't define
> unbounded partitions at either side, it's fine, you just raise an error
> whenever the user tries to insert a value for which there is no
> partition.
>
> Not real clear to me how this applies to list partitioning, but I have
> the hunch that it'd be better to deal with that without overflow
> partitions as well.
>

Well, overflow partitions might not sound to be a nice idea and we
might not want to do it or atleast not in first version, however
I think it could be useful in certain cases like if in a long running
transaction user is able to insert many rows into appropriate partitions
and one row falls out of the defined partition's range; an error in such a
case can annoy user, also I think similar situation could occur for
bulk insert (COPY).

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com