> On Aug 17, 2022, at 22:57, Abdul Qoyyuum <aqoyy...@cardaccess.com.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.
