> From: Gurjeet Singh [mailto:singh.gurjeet@gmail.com]
> Sent: Friday, July 13, 2012 4:24 AM
>>On Tue, Jul 10, 2012 at 2:40 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
>>> 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?
> Yes, it will have to take an exclusive lock on the index, and possibly the table too, but the operation should be quick to be even noticeable in low load
> conditions.
Which index you are referring here, is it primary key table index?
According to what I have debugged, the locks are taken on foreign key table, constraint object and dependent triggers.
> However, if the x-lock is waiting for some other long running query to finish, then lock queuing logic in Postgres will make new queries to wait for this x-lock to > be taken and released before any new query can begin processing. This is my recollection of the logic from an old conversation, others can weigh in to confirm.
>>> 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?
> Yes, I forgot to mention this in the original post. This feature will be a superset of the feature we introduced in ALTER TABLE. I don't see a way around that,
> except for slowly deprecating the older feature.
After new implementation, there will be no need to perform any operation for table with foreign key and hence reduce the lock time for same as well.
However after implementation of Reindex Concurrently, this feature will also needs to be deprecated which might not happen soon but still I feel it should be considered whether providing new syntax and implementation is really required by users.
With Regards,
Amit Kapila.
