I'm not saying there isn't any possible use case that could be solved with the above (although mind my example of people running with all indexes and the guc always enabled; I don't think thats a sceanrio that anyone thinks should be recommended, but it will be a far more common use case given this design; and btw it wont work well with pg_hint_plan because the GUC/ALTER combo doesn't play well with multiple indexes), but more importantly, if we only solve the simple cases at the expense of the hard problem, we're doing our users a disservice.
So, as proposed:
Replicate-able DDL: Enables a holistic picture but is a foot-gun for the DBA in the "revert" case.
Boolean GUC: Enables some experimentation; can be used to quickly re-enable invisible indexes that are waiting for the DDL to make them visible again. Insufficiently granular for quickly exploring various options.
The granularity issue seems overcome-able:
Multi-Valued GUC: Specify explicitly which invisible indexes to make visible, eliminating the granularity problem of the boolean option. Can provide a "pg_catalog.pg_all_indexes" magic value impossible to exist in reality that would enable the "true" boolean option - false would just be an empty setting.
The foot-gun seems safe enough to offer given the benefit the feature provides.
Even without the GUC the proposed feature seems an improvement over the status quo. The boolean I'd probably leave on the table; while a bit ugly in usage for the expected experimentation the multi-valued text GUC seems reasonable (and will effectively prohibit relying on invisible indexes generally).
Are there other alternative designs this last bit of discussion is meant to promote or are people actively voting for the status quo over the addition of the index visibility attribute? Or, maybe more properly, is index replication the dividing line here and any new feature has to make that aspect optional?
If we are going to bite on the multi-valued text GUC it could just define which indexes to ignore when planning and we'd have the local-only feature done. Which leads then to just implementing this feature (with multi-valued GUC) as the option by which the DBA can choose to apply their local GUC changes across their secondaries without having to (but can if they wish) apply the GUC change to all those machines as well.
David J.
