> On Aug 17, 2022, at 22:57, Abdul Qoyyuum <aqoyyuum@cardaccess.com.au> wrote:
> Question is, do we have to shutdown traffic and close all existing open connections in order to drop and properly
recreatethe 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
problemto solve is preventing the session from disconnecting, either by finding a way to avoid a timeout, connecting
viascreen 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
thattable 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,
youcan 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.