Re: Documentation improvements for partitioning

On Wed, Feb 15, 2017 at 9:10 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> * "ERROR: cannot create index on partitioned table "measurement_year_month""
>>> is misleading because you can create indexes on partitions
>>
>> Do you mean that this should not cause an error at all, but create the
>> specified index on partitions as part of running the command? Should the
>> code to handle that be part of this release?
>
> Sounds fairly basic to me. If you don't support this, then presumably
> every ORM, pgAdmin etc will all be broken.

I don't see why that should be the case.

> And 1000 people will need to write a script that does what we could do
> easily in a loop internally.

Now that is probably true.

> At present you haven't even documented how you'd do this.

It's not that hard to figure it out, though. A HINT wouldn't be a bad
idea, certainly.

There are some really thorny problems with making index creation
cascade to all of the partitions. I think it's worth doing, but
there's a lot of stuff to think about before you go and start writing
code. Most obviously, if you can use a single CREATE INDEX statement
to create indexes on all of the partitions, then probably you ought to
also be able to use DROP INDEX to get rid of all of those indexes. In
other words, it should probably work a lot like what already happens
with constraints: constraints cascade from the parent down to the
children, but we still know which child object goes with which parent
object, so if the parent object is dropped we can get rid of all of
the children. I think we need something similar here, although if we
restrict it to the partitioning case and don't make it work with table
inheritance then it can be simpler since table partitioning doesn't
allow multiple inheritance. Presumably we'd want other index commands
like REINDEX to cascade similarly.

Also, it's not entirely clear what the semantics should be. If the
partitioning key is (a) and you ask for an index on (a, b), you could
conceivably omit a from the indexes created on partitions that only
cover a single value of a. (That case is easy to detect when list
partitioning is in use.) Should we try do that elimination, or just
do what the user asked for? Will users be unhappy if we try to do
this sort of column elimination but it only works in simple cases?
Think about the possibility that there are partitioning expressions
rather than partitioning columns before concluding we can make it work
in all cases. On the other hand, if you ask for a UNIQUE index on
(b), should we go ahead and create such an index on each partition,
ensuring uniqueness within each partition, or should we refuse to
proceed on the grounds that we can't be sure that such an index will
ensure global uniqueness? If you do the former, someone might find
the behavior surprising, but if you do the latter, you might annoy
people who know what they're asking for and want that thing but can't
get it. I suspect we want to eventually allow a user to ask for
either one, because eventually we'll probably have global indexes, and
then you really need a way to say whether you want a global index or a
partitioned non-global index. But that requires agreeing on syntax,
which is complicated and will probably involve a lot of bikeshedding
(as well it should - these are big decisions).

I think it would be a bad idea to try to fix this problem for v10.
One of the earlier versions of the patch allowed indexes on the parent
table as if it were just a regular empty table, which did not seem
useful. I asked him to disallow that so as to keep our options open
for the future. I see no reason why v11 or v12 can't fill in the
functionality in this area. Right now we're 2 weeks away from the
start of the last CommitFest, and that's not the time to go start
writing a complex patch for a feature that isn't even particularly
well-defined. If somebody really cared about this
make-an-index-for-everything-in-the-hierarchy problem, they could've
written a patch for that at any time in the last 5 years; it's not
strictly dependent on the new partitioning stuff. Nobody's done that,
and trying to throw together something now in the last couple of weeks
could easily end with us getting it wrong and then having to face the
unpleasant prospect of either leaving it broken or breaking backward
compatibility to fix it.

> It leaves me asking what else is missing.

There is certainly a lot of room for improvement here but I don't
understand your persistent negativity about what's been done thus far.
I think it's pretty clearly a huge step forward, and I think Amit
deserves a ton of credit for making it happen. The improvements in
bulk loading performance alone are stupendous. You apparently have
the idea that somebody could have written an even larger patch that
solved even more problems at once, but this was already a really big
patch, and IMHO quite a good one.

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