Re: no default hash partition

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > On 2019-Aug-06, Tom Lane wrote:
> >> Seems like "it's likely to cause trouble for users" is just going to
> >> beg the question "why?". Can we explain the hazard succinctly?
> >> Or point to a comment somewhere else that explains it?
>
> > Right ... the "trouble" is just that if the user later wants to add the
> > missing partitions, they'll need to acquire some strong lock (IIRC it's AEL)
> > in the partitioned table, so it effectively means an outage. With
> > list/range partitioning, there's the slight advantage that you don't
> > have to guess all your partitions in advance, or cover data values that
> > are required for a very small number of rows. In hash partitioning you
> > can't really predict which values are those going to be, and the set of
> > missing partitions is perfectly known.
>
> Hmm. So given the point about it being hard to predict which hash
> partitions would receive what values ... under what circumstances
> would it be sensible to not create a full set of partitions? Should
> we just enforce that there is a full set, somehow?

I imagine there's good reasons this wasn't just done (for this or
various other things), but couldn't we enforce it by just creating them
all..? Sure would simplify a lot of things for users. Similairly for
list partitions, I would think. Again, I feel like there's probably a
reason why it doesn't just work(tm) like that, but it sure would be
nice.

Of course, there's the other side of things where it'd sure be nice to
automatically have partitions created for time-based partitions when
appropriate (yes, basically doing what pg_partman already does, but in
core somehow..), but for hash partitions we don't need to deal with
that.

Thanks,

Stephen