On Sat, Oct 7, 2017 at 7:04 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> > wrote: >> 2. create one index for each existing partition. These would be >> identical to what would happen if you created the index directly on >> each partition, except that there is an additional dependency to the >> parent's abstract index. > > One thing I'm a bit worried about is how to name these subordinate > indexes. They have to have names because that's how pg_class works, > and those names can't all be the same, again because that's how > pg_class works. There's no problem right away when you first create > the partitioned index, because you can just pick names out of a hat > using whatever name-generating algorithm seems best. However, when > you dump-and-restore (including but not limited to the pg_upgrade > case) you've got to preserve those names. If you just generate a new > name that may or may not be the same as the old one, then it may > collide with a user-specified name that only occurs later in the dump. > Also, you'll have trouble if the user has applied a COMMENT or a > SECURITY LABEL to the index because that command works by name, or if > the user has a reference to the index name inside a function or > whatever. > > These are pretty annoying corner-case bugs because they're not likely > to come up very often. Most people won't notice or care if the index > name changes. But I don't think it's acceptable to just ignore the > problem. An idea I had was to treat the abstract index - to use your > term - sort of the way we treat an extension. Normally, when you > create an index on a partitioned table, it cascades, but for dump and > restore purpose, we tag on some syntax that says "well, don't actually > create the subordinate indexes, i'll tell you about those later". > Then for each subordinate index we issue a separate CREATE INDEX > command followed by ALTER INDEX abstract_index ATTACH PARTITION > concrete_index or something of that sort. That means you can't > absolutely count on the parent index to have all of the children it's > supposed to have but maybe that's OK.
+1. How about CREATE INDEX ... PARTITION OF ... FOR TABLE ...? to create the index and attach it? -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers