On Wed, Feb 15, 2017 at 9:10 AM, Simon Riggs <si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
