Horton

Thread

Commits

  1. Clarify the default partition's role

  2. Add comment on no default partition with hash partitioning

  1. no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-06T22:27:35Z

    Given the discussion starting at
https://postgr.es/m/CAFjFpRdBiQjZm8sG9+s0x8Re-afHds6MFLgGuw0wVUNLGrVOQg@mail.gmail.com
we don't have default-partition support with the hash partitioning
scheme.  That seems a reasonable outcome, but I think we should have a
comment about it (I had to search the reason for this restriction in the
hash-partitioning patch set).  How about the attached?  Does anyone see
a reason to make this more verbose, and if so to what?

... unless somebody wants to argue that we should have the feature; if
so please share your patch.

Thanks

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  2. Re: no default hash partition

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-06T22:36:38Z

    Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Given the discussion starting at
> https://postgr.es/m/CAFjFpRdBiQjZm8sG9+s0x8Re-afHds6MFLgGuw0wVUNLGrVOQg@mail.gmail.com
> we don't have default-partition support with the hash partitioning
> scheme.  That seems a reasonable outcome, but I think we should have a
> comment about it (I had to search the reason for this restriction in the
> hash-partitioning patch set).  How about the attached?  Does anyone see
> a reason to make this more verbose, and if so to what?

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?

			regards, tom lane

  3. Re: no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-06T22:53:10Z

    On 2019-Aug-06, Tom Lane wrote:

> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Given the discussion starting at
> > https://postgr.es/m/CAFjFpRdBiQjZm8sG9+s0x8Re-afHds6MFLgGuw0wVUNLGrVOQg@mail.gmail.com
> > we don't have default-partition support with the hash partitioning
> > scheme.  That seems a reasonable outcome, but I think we should have a
> > comment about it (I had to search the reason for this restriction in the
> > hash-partitioning patch set).  How about the attached?  Does anyone see
> > a reason to make this more verbose, and if so to what?
> 
> 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.

Not enlightened enough ATM for a succint enough explanation, but I'll
take suggestions.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  4. Re: no default hash partition

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-06T22:58:44Z

    Alvaro Herrera <alvherre@2ndquadrant.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?

			regards, tom lane

  5. Re: no default hash partition

    Stephen Frost <sfrost@snowman.net> — 2019-08-06T23:02:28Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Alvaro Herrera <alvherre@2ndquadrant.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

  6. Re: no default hash partition

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-06T23:35:12Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> 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.

Well, with lists Alvaro's point holds: you might know a priori that
some of the values are infrequent and don't deserve their own partition.
The thing about hash is that the entries should (in theory) get spread
out to all partitions pretty evenly, so it's hard to see why a user
would want to treat any partition differently from any other.

			regards, tom lane

  7. Re: no default hash partition

    Stephen Frost <sfrost@snowman.net> — 2019-08-06T23:42:08Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> 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.
> 
> Well, with lists Alvaro's point holds: you might know a priori that
> some of the values are infrequent and don't deserve their own partition.
> The thing about hash is that the entries should (in theory) get spread
> out to all partitions pretty evenly, so it's hard to see why a user
> would want to treat any partition differently from any other.

Yeah, that's a fair argument, but giving the user a way to say that
would address it.  As in, "create me a list-partitioned table for these
values, plus a default."  Anyhow, I'm sure that I'm taking this beyond
what we need to do right now, just sharing where I think it'd be good
for things to go.

Thanks!

Stephen

  8. Re: no default hash partition

    Amit Langote <amitlangote09@gmail.com> — 2019-08-07T02:27:26Z

    Hi Alvaro,

On Wed, Aug 7, 2019 at 7:27 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> Given the discussion starting at
> https://postgr.es/m/CAFjFpRdBiQjZm8sG9+s0x8Re-afHds6MFLgGuw0wVUNLGrVOQg@mail.gmail.com
> we don't have default-partition support with the hash partitioning
> scheme.  That seems a reasonable outcome, but I think we should have a
> comment about it (I had to search the reason for this restriction in the
> hash-partitioning patch set).

That hash-partitioned tables can't have default partition is mentioned
in the CREATE TABLE page:

"If DEFAULT is specified, the table will be created as a default
partition of the parent table. The parent can either be a list or
range partitioned table. A partition key value not fitting into any
other partition of the given parent will be routed to the default
partition. There can be only one default partition for a given parent
table."

>  How about the attached?  Does anyone see
> a reason to make this more verbose, and if so to what?

If the outcome of this discussion is that we expand our internal
documentation of why there's no default hash partition, then should we
also expand the user documentation somehow?

Thanks,
Amit

  9. Re: no default hash partition

    Amit Langote <amitlangote09@gmail.com> — 2019-08-07T02:32:31Z

    Hi,

On Wed, Aug 7, 2019 at 8:02 AM Stephen Frost <sfrost@snowman.net> wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Alvaro Herrera <alvherre@2ndquadrant.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.

Maybe the reason that we don't create all partitions automatically is
that hash-partitioning developers thought that such a feature could be
built later [1].  Maybe you know, but I think it's just that we
implemented the syntax needed to get things like pg_dump/upgrade
working sanely, that is, a command to define each partition
separately, and... stopped there.  There're no other intrinsic reasons
that I know of for this implementation order.  pg_partman helps with
the automation, with features that users want in most or all cases --
define all needed partitions for a given modulus, define time series
partitions for a given window, etc.  Maybe not everyone likes to rely
on an external tool, so the core at some point will have features to
perform some if not all of the tasks that pg_partman does, with the
added benefit that the new feature might allow the core to optimize
partitioning better.

Btw, there was even a discussion started recently to discuss the
user-level feature:

Subject: Creating partitions automatically at least on HASH?
https://www.postgresql.org/message-id/alpine.DEB.2.21.1907150711080.22273%40lancre

Thanks,
Amit

[1] https://www.postgresql.org/message-id/CA%2BTgmobGH4zK27y42gGbtvfWFPnATHcocMZ%3DHkJF51KLkKY_xw%40mail.gmail.com

  10. Re: no default hash partition

    Robert Haas <robertmhaas@gmail.com> — 2019-08-07T03:26:19Z

    On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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 think it would only be sensible as a temporary state.  The system
allows more than one modulus so that you can do partition split
incrementally.  For example if you have 8 partitions all with modulus
8 and with remainders 0..7, you could:

- detach the partition with (modulus 8, remainder 0)
- attach two new partitions with (modulus 16, remainder 0) and
(modulus 16, remainder 8)
- move the data from the old partition to the new ones

Then you'd have 9 partitions, and you'd only have taken the amount of
downtime needed to repartition 1/8th of your data.  You could then
repeat this process one partition at a time during additional
maintenance windows, and end up with 16 partitions in the end.
Without the ability to have more than one modulus, or if you had
chosen not to double the modulus but to change it to some other value
like 13, you would've needed to repartition all the data at once,
which would have required one much longer outage.  You can argue about
whether the ability to do this kind of thing is useful, but it seemed
to me that it was.

I think, as Amit says, that having an automatic partition creation
feature for hash partitions (and maybe other kinds, but certainly for
hash) would be a useful thing to add to the system. I also think that
it might be useful to add some commands to automate partition
splitting (and maybe combining) although I think there's some design
work to be done there to figure out exactly what we should build.  I
don't think it's ever useful to have a hash-partitioned table with an
incomplete set of partitions long term, but it makes things simpler to
allow that temporarily, for example during dump restoration.
Therefore, I see no reason why we would want to go to the trouble of
allowing hash-partitioned tables to have default partitions; it would
just encourage people to do things that don't really make any sense.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  11. Re: no default hash partition

    David Fetter <david@fetter.org> — 2019-08-07T03:46:58Z

    On Tue, Aug 06, 2019 at 06:58:44PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.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?

+1 for requiring that hash partitions not have gaps, ideally by making
one call create all the partitions.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  12. Re: no default hash partition

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2019-08-07T04:58:34Z

    At Tue, 6 Aug 2019 23:26:19 -0400, Robert Haas <robertmhaas@gmail.com> wrote in <CA+TgmoZpAsYY+naYpuw+fG=J1wYTXrhk=3uEYYa_Nz=Jwck+eg@mail.gmail.com>
> On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think, as Amit says, that having an automatic partition creation
> feature for hash partitions (and maybe other kinds, but certainly for
> hash) would be a useful thing to add to the system. I also think that
> it might be useful to add some commands to automate partition
> splitting (and maybe combining) although I think there's some design
> work to be done there to figure out exactly what we should build.  I
> don't think it's ever useful to have a hash-partitioned table with an
> incomplete set of partitions long term, but it makes things simpler to
> allow that temporarily, for example during dump restoration.
> Therefore, I see no reason why we would want to go to the trouble of
> allowing hash-partitioned tables to have default partitions; it would
> just encourage people to do things that don't really make any sense.

+1.

By the way, couldn't we offer a means to check for gaps in a hash
partition? For example, the output of current \d+ <parent>
contains the Partitoins section that shows a list of
partitions. I think that we can show all gaps there.

=# \d+ p
                               Partitioned table "public.p"
...
Partition key: HASH (a)
Partitions: c1 FOR VALUES WITH (modulus 4, remainder 0),
            c3 FOR VALUES WITH (modulus 4, remainder 3),
            GAP (modulus 4, remainder 1),
            GAP (modulus 4, remainder 2)

Or

Partitions: c1 FOR VALUES WITH (modulus 4, remainder 0),
            c3 FOR VALUES WITH (modulus 4, remainder 3),
Gaps: (modulus 4, remainder 1), (modulus 4, remainder 2)


regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  13. Re: no default hash partition

    Amit Langote <amitlangote09@gmail.com> — 2019-08-07T05:51:45Z

    Horiguchi-san,

On Wed, Aug 7, 2019 at 1:59 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Tue, 6 Aug 2019 23:26:19 -0400, Robert Haas <robertmhaas@gmail.com> wrote:
> > On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I think, as Amit says, that having an automatic partition creation
> > feature for hash partitions (and maybe other kinds, but certainly for
> > hash) would be a useful thing to add to the system. I also think that
> > it might be useful to add some commands to automate partition
> > splitting (and maybe combining) although I think there's some design
> > work to be done there to figure out exactly what we should build.  I
> > don't think it's ever useful to have a hash-partitioned table with an
> > incomplete set of partitions long term, but it makes things simpler to
> > allow that temporarily, for example during dump restoration.
> > Therefore, I see no reason why we would want to go to the trouble of
> > allowing hash-partitioned tables to have default partitions; it would
> > just encourage people to do things that don't really make any sense.
>
> +1.
>
> By the way, couldn't we offer a means to check for gaps in a hash
> partition? For example, the output of current \d+ <parent>
> contains the Partitoins section that shows a list of
> partitions. I think that we can show all gaps there.
>
> =# \d+ p
>                                Partitioned table "public.p"
> ...
> Partition key: HASH (a)
> Partitions: c1 FOR VALUES WITH (modulus 4, remainder 0),
>             c3 FOR VALUES WITH (modulus 4, remainder 3),
>             GAP (modulus 4, remainder 1),
>             GAP (modulus 4, remainder 2)
>
> Or
>
> Partitions: c1 FOR VALUES WITH (modulus 4, remainder 0),
>             c3 FOR VALUES WITH (modulus 4, remainder 3),
> Gaps: (modulus 4, remainder 1), (modulus 4, remainder 2)

I imagine showing this output would require some non-trivial code on
the client side (?) to figure out the gaps.  If our intention in the
long run is to make sure that such gaps only ever appear temporarily,
that is, when running a command to increase the number of hash
partitions (as detailed in Robert's email), then a user would never
see those gaps.  So, maybe writing such code wouldn't be worthwhile in
the long run?

Thanks,
Amit

  14. Re: no default hash partition

    Magnus Hagander <magnus@hagander.net> — 2019-08-07T09:01:36Z

    On Wed, Aug 7, 2019 at 5:26 AM Robert Haas <robertmhaas@gmail.com> wrote:

> On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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 think it would only be sensible as a temporary state.  The system
> allows more than one modulus so that you can do partition split
> incrementally.  For example if you have 8 partitions all with modulus
> 8 and with remainders 0..7, you could:
>
> - detach the partition with (modulus 8, remainder 0)
> - attach two new partitions with (modulus 16, remainder 0) and
> (modulus 16, remainder 8)
> - move the data from the old partition to the new ones
>
> Then you'd have 9 partitions, and you'd only have taken the amount of
> downtime needed to repartition 1/8th of your data.  You could then
> repeat this process one partition at a time during additional
> maintenance windows, and end up with 16 partitions in the end.
> Without the ability to have more than one modulus, or if you had
> chosen not to double the modulus but to change it to some other value
> like 13, you would've needed to repartition all the data at once,
> which would have required one much longer outage.  You can argue about
> whether the ability to do this kind of thing is useful, but it seemed
> to me that it was.
>
> I think, as Amit says, that having an automatic partition creation
> feature for hash partitions (and maybe other kinds, but certainly for
> hash) would be a useful thing to add to the system. I also think that
> it might be useful to add some commands to automate partition
> splitting (and maybe combining) although I think there's some design
> work to be done there to figure out exactly what we should build.  I
> don't think it's ever useful to have a hash-partitioned table with an
> incomplete set of partitions long term, but it makes things simpler to
> allow that temporarily, for example during dump restoration.
> Therefore, I see no reason why we would want to go to the trouble of
> allowing hash-partitioned tables to have default partitions; it would
> just encourage people to do things that don't really make any sense.
>

Another usecase for not having all partitions temporarily is if some of
them should be different enough that you  don't want them auto-created. A
common one would be that they should be on different tablespaces, but that
can of course be solved by moving the partition after it had been
auto-created (and should be fast since at this point it would be empty).
But imagine you wanted one partition to be a FOREIGN one for example, you
can't ALTER a partition to become foreign, you'd have to drop it and
recreate it, in which case not having created it in the first place
would've been better. That's pretty weird for hash partitioning, but one
could certainly imagine having *all* partitions of a hash partitioned table
be FOREIGN...

None of that is solved by having a default partition for it though, since
it would only be a temporary state. It only goes to that if we do want to
auto-create the hash partitions (which I think would be really useful for
the most common usecase), we should have a way not to do it. Either by only
autocreating them if a specific keyword is given, or by having a keyword
that would prevent it.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

  15. Re: no default hash partition

    Stephen Frost <sfrost@snowman.net> — 2019-08-07T14:04:03Z

    Greetings,

* Amit Langote (amitlangote09@gmail.com) wrote:
> On Wed, Aug 7, 2019 at 1:59 PM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> > At Tue, 6 Aug 2019 23:26:19 -0400, Robert Haas <robertmhaas@gmail.com> wrote:
> > > On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > I think, as Amit says, that having an automatic partition creation
> > > feature for hash partitions (and maybe other kinds, but certainly for
> > > hash) would be a useful thing to add to the system. I also think that
> > > it might be useful to add some commands to automate partition
> > > splitting (and maybe combining) although I think there's some design
> > > work to be done there to figure out exactly what we should build.  I
> > > don't think it's ever useful to have a hash-partitioned table with an
> > > incomplete set of partitions long term, but it makes things simpler to
> > > allow that temporarily, for example during dump restoration.
> > > Therefore, I see no reason why we would want to go to the trouble of
> > > allowing hash-partitioned tables to have default partitions; it would
> > > just encourage people to do things that don't really make any sense.
> >
> > +1.
> >
> > By the way, couldn't we offer a means to check for gaps in a hash
> > partition? For example, the output of current \d+ <parent>
> > contains the Partitoins section that shows a list of
> > partitions. I think that we can show all gaps there.
> >
> > =# \d+ p
> >                                Partitioned table "public.p"
> > ...
> > Partition key: HASH (a)
> > Partitions: c1 FOR VALUES WITH (modulus 4, remainder 0),
> >             c3 FOR VALUES WITH (modulus 4, remainder 3),
> >             GAP (modulus 4, remainder 1),
> >             GAP (modulus 4, remainder 2)
> >
> > Or
> >
> > Partitions: c1 FOR VALUES WITH (modulus 4, remainder 0),
> >             c3 FOR VALUES WITH (modulus 4, remainder 3),
> > Gaps: (modulus 4, remainder 1), (modulus 4, remainder 2)
> 
> I imagine showing this output would require some non-trivial code on
> the client side (?) to figure out the gaps.  If our intention in the
> long run is to make sure that such gaps only ever appear temporarily,
> that is, when running a command to increase the number of hash
> partitions (as detailed in Robert's email), then a user would never
> see those gaps.  So, maybe writing such code wouldn't be worthwhile in
> the long run?

I tend to agree that it might not be useful to have this code,
particularly not on the client side, but we've dealt with the issue of
"the client would need non-trivial code for this" in the past by having
a server-side function for the client to call (eg: pg_get_expr(),
pg_get_ruledef()).  If we really think this would be valuable to show
and we don't want the client to have to have a bunch of code for it,
doing something similar here could address that.

One thing I've wished for is a function that would give me a range type
back for a partition (would be neat to be able to use a range type to
specify a partition's range when creating it too).

Thanks,

Stephen

  16. Re: no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-07T14:59:13Z

    On 2019-Aug-07, Amit Langote wrote:

> That hash-partitioned tables can't have default partition is mentioned
> in the CREATE TABLE page:
> 
> "If DEFAULT is specified, the table will be created as a default
> partition of the parent table. The parent can either be a list or
> range partitioned table. A partition key value not fitting into any
> other partition of the given parent will be routed to the default
> partition. There can be only one default partition for a given parent
> table."

This approach of documenting by omission seems unhelpful.  Yes, I'd like
to expand that too.


-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  17. Re: no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-07T16:25:02Z

    On 2019-Aug-06, Stephen Frost wrote:

> Yeah, that's a fair argument, but giving the user a way to say that
> would address it.  As in, "create me a list-partitioned table for these
> values, plus a default."  Anyhow, I'm sure that I'm taking this beyond
> what we need to do right now, just sharing where I think it'd be good
> for things to go.

Fabien Coelho already submitted a patch for this IIRC.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  18. Re: no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-07T16:29:45Z

    On 2019-Aug-07, Amit Langote wrote:

> That hash-partitioned tables can't have default partition is mentioned
> in the CREATE TABLE page:
> 
> "If DEFAULT is specified, the table will be created as a default
> partition of the parent table. The parent can either be a list or
> range partitioned table. A partition key value not fitting into any
> other partition of the given parent will be routed to the default
> partition. There can be only one default partition for a given parent
> table."

Actually, it also says this (in the blurb for the PARTITION OF clause):

      Creates the table as a <firstterm>partition</firstterm> of the specified
      parent table. The table can be created either as a partition for specific
      values using <literal>FOR VALUES</literal> or as a default partition
      using <literal>DEFAULT</literal>.  This option is not available for
      hash-partitioned tables.

which I think is sufficient.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  19. Re: no default hash partition

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-07T16:44:13Z

    Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Actually, it also says this (in the blurb for the PARTITION OF clause):

>       Creates the table as a <firstterm>partition</firstterm> of the specified
>       parent table. The table can be created either as a partition for specific
>       values using <literal>FOR VALUES</literal> or as a default partition
>       using <literal>DEFAULT</literal>.  This option is not available for
>       hash-partitioned tables.

> which I think is sufficient.

Hm, that's rather confusingly worded IMO.  Is the antecedent of "this
option" just DEFAULT, or does it mean that you can't use FOR VALUES,
or perchance it means that you can't use a PARTITION OF clause
at all?

			regards, tom lane

  20. Re: no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-07T16:55:59Z

    On 2019-Aug-07, Tom Lane wrote:

> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Actually, it also says this (in the blurb for the PARTITION OF clause):
> 
> >       Creates the table as a <firstterm>partition</firstterm> of the specified
> >       parent table. The table can be created either as a partition for specific
> >       values using <literal>FOR VALUES</literal> or as a default partition
> >       using <literal>DEFAULT</literal>.  This option is not available for
> >       hash-partitioned tables.
> 
> > which I think is sufficient.
> 
> Hm, that's rather confusingly worded IMO.  Is the antecedent of "this
> option" just DEFAULT, or does it mean that you can't use FOR VALUES,
> or perchance it means that you can't use a PARTITION OF clause
> at all?

Uh, you're right, I hadn't noticed that.  Not my text.  I think this can
be fixed easily as in the attached.  There are other options, but I like
this one the best.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  21. Re: no default hash partition

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-07T21:22:01Z

    Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Aug-07, Tom Lane wrote:
>> Hm, that's rather confusingly worded IMO.  Is the antecedent of "this
>> option" just DEFAULT, or does it mean that you can't use FOR VALUES,
>> or perchance it means that you can't use a PARTITION OF clause
>> at all?

> Uh, you're right, I hadn't noticed that.  Not my text.  I think this can
> be fixed easily as in the attached.  There are other options, but I like
> this one the best.

OK, but maybe also s/created as a default partition/created as the default
partition/ ?  Writing "a" carries the pretty clear implication that there
can be more than one, and contradicting that a sentence later doesn't
improve it.

			regards, tom lane

  22. Re: no default hash partition

    Amit Langote <amitlangote09@gmail.com> — 2019-08-08T01:01:38Z

    On Thu, Aug 8, 2019 at 6:22 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > On 2019-Aug-07, Tom Lane wrote:
> >> Hm, that's rather confusingly worded IMO.  Is the antecedent of "this
> >> option" just DEFAULT, or does it mean that you can't use FOR VALUES,
> >> or perchance it means that you can't use a PARTITION OF clause
> >> at all?
>
> > Uh, you're right, I hadn't noticed that.  Not my text.  I think this can
> > be fixed easily as in the attached.  There are other options, but I like
> > this one the best.
>
> OK, but maybe also s/created as a default partition/created as the default
> partition/ ?  Writing "a" carries the pretty clear implication that there
> can be more than one, and contradicting that a sentence later doesn't
> improve it.

+1.  Maybe also remove the last sentence of the 2nd paragraph, that
is, this one:

There can be only one default partition for a given parent table.

Regards,
Amit

  23. Re: no default hash partition

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-08-08T20:08:51Z

    On 2019-Aug-08, Amit Langote wrote:

> On Thu, Aug 8, 2019 at 6:22 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > OK, but maybe also s/created as a default partition/created as the default
> > partition/ ?  Writing "a" carries the pretty clear implication that there
> > can be more than one, and contradicting that a sentence later doesn't
> > improve it.
> 
> +1.  Maybe also remove the last sentence of the 2nd paragraph, that
> is, this one:
> 
> There can be only one default partition for a given parent table.

Thanks!  I pushed with these two changes.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services