rhaas=# create unique index on foo (a collate "C"); CREATE INDEX rhaas=# alter table foo add primary key using index foo_a_idx; ALTER TABLE
Now dump and restore this database. Then:
Notice that the collation specifier is gone. Oops.
The intent of the 'USING INDEX' feature was to work around the problem that PRIMARY KEY indexes cannot be reindexed concurrently to reduce bloat.
Considering that the feature doesn't work [1] (at least as shown in example in the docs [2]) if there are any foreign keys referencing the table, there's scope for improvement.
There was proposal to support reindexing the primary key index, but I am not sure where that stands. If that's already in, or soon to be in core, we may put a deprecation notice around USING INDEX. OTOH, if reindexing PKeys is too difficult, we may want to support index-replacement via a top-level ALTER TABLE subcommand that works in CONCURRENT mode, and not recommend the method shown in the docs (i.e deprecate the USING INDEX syntax).
[1]: The DROP CONSTRAINT part of the command fails if there are any FKeys pointing to this table.
