Re: Unable to Create or Drop Index Concurrently

> On Aug 17, 2022, at 22:57, Abdul Qoyyuum <aqoyyuum(at)cardaccess(dot)com(dot)au> wrote:
> Question is, do we have to shutdown traffic and close all existing open connections in order to drop and properly recreate the index?

No, you don't.

On the CREATE INDEX CONCURRENTLY command, what is likely going on is that when the connection drops, the session terminates, which will terminate the CREATE INDEX CONCURRENTLY command and leave the index in an INVALID state. The problem to solve is preventing the session from disconnecting, either by finding a way to avoid a timeout, connecting via screen or tmux, etc.

On the DROP INDEX, what is likely going on is that the DROP INDEX is waiting for other transactions which are accessing that table to finish, since it needs to take an exclusive lock on the table. If the session drops, the command isn't run, so the index hasn't been dropped. The solution is the same as above. If you are on a version that supports it, you can use the DROP INDEX CONCURRENTLY command to avoid locking issues with the table, since even before the DROP INDEX happens, new transactions attempting to access that table will queue up behind the DROP INDEX.