Re: ALTER TABLE .. ADD PRIMARY KEY .. USING INDEX has dump-restore hazard

On Tue, Jul 21, 2015 at 9:23 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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.
[2]: http://www.postgresql.org/docs/9.4/static/sql-altertable.html

--
Gurjeet Singh http://gurjeet.singh.im/