Hmm, so I'm now unsure what the actual proposals for handling pg_dump are. We seem to have the following three proposals:
1. Alvaro: use CREATE INDEX ON ONLY <parent> (not recursive ), followed by CREATE INDEX ON <partition>, followed by ALTER INDEX <on_parent> ATTACH PARTITION <on_partition>. I provide an ALTER INDEX DETACH PARTITION for symmetry and because it can be used to replace the index. Pros: the database is always restored identically to what was in the original. Con: The index hierarchy might be "partial", that is, lack a component index on some partition. 2. David's: use CREATE INDEX ON <partition>, followed by CREATE INDEX ON <parent>. This will use the matching mechanism to automatically attach the index on partition to index on parent. If some partition lacks a matching index, one is created automatically by the creation on parent. If you want to replace the index on a partition, use a new (as yet unimplemented) ALTER INDEX REPLACE. No need to add ONLY to the table name in CREATE INDEX, since the command always recurses. (This seems good to me, because I Pro: the index is never "partial" (missing a partition). Con: the matching mechanism might choose a different index on restore than what was selected in the database being dumped. 3. Robert's: use CREATE INDEX ON ONLY <parent>, which creates a shell index on parent only (no recursion), followed by CREATE INDEX ON <partition>. DETACH is not provided. If you ATTACH an index for a partition that already has one index attached, then (1) the newly attached one replaces the original (i.e. effectively REPLACE) or (2) you get an error and we implement a separate ALTER INDEX REPLACE command. It's not clear to me how or when the shell index becomes a real index. Robert, can you please clarify the terms of your proposal? How is it better than mine? Is David's concern about a "partial" index (i.e. an index that doesn't exist in some partition) solved by it? I have code for proposals 1 and 2. I don't like proposal 2, and David & Ashutosh don't like (1). Maybe if we all understand (3) we can agree on using that one? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
