Thread: FW: Allow replacement of bloated primary key indexes without foreign key rebuilds

> Today I learnt [1,2,3] that the feature ALTER TABLE .. ADD CONSTRAINT ... USING INDEX we added back in the day is not so useful in the field.

> Having to drop foreign key constraints before this command, and recreate them afterwards makes this command useless to most database setups. I feel sorry

> that no one brought this up when we were implementing the feature; maybe we could've done something about it right then.

Will it impact user such that it will block its operation or something similar or it is a usability issue?

> All we need to do is allow swapping of pg_class.relfilenode of two indexes. This will let the dependency entries stand as they are and allow us to drop the

> bloated primary key index structure without having to rebuild the foreign key constraints.

I have noticed is that currently Oid and pg_class.relfilenode are same for user created tables and indexes. But after your implementation that will not remain same, I am not sure whether it can impact any other path of code.

>As for the syntactical sugar, this can be added to either ALTER TABLE or to ALTER INDEX. Although under no normal circumstances one would need to use >ALTER INDEX to swap two indexes' relfilenode (because one can easily create a duplicate index and drop/rename-in-place the old one), I think it would make > more sense here since it is just an operation on two indexes and has nothing to do with the constraints, apart from the fact that we want to use this feature to

> meddle with the constraints.

> Syntax options:

> ALTER TABLE tbl REPLACE [CONSTRAINT constr] {PRIMARY KEY | UNIQUE} USING INDEX new_index;

> ALTER INDEX ind REPLACE WITH new_index;

After this new syntax there will be 2 ways for users to do the replacement of index, won’t it confuse users for which syntax to use?