Thread: Unable to Create or Drop Index Concurrently

> 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. 

On Thu, Aug 18, 2022 at 01:57:48PM +0800, Abdul Qoyyuum wrote:
> Hi list,
> 
> We have a running Master-Slave High Availability set up. Naturally, we
> can't run any changes on read-only databases on slave, so we have to do it
> on the master node.
> 
> When trying to run the following command:
> 
> create index concurrently idx_cash_deposit_channel_id_batch_id on
> cash_deposit (channel_id, batch_id);
> 
> 
> Waiting for a long time, and my connection dropped. When checking the
> table, we get the index as INVALID
> 
> Indexes:
>     "pk_cash_deposit" PRIMARY KEY, btree (id)
>     "idx_cash_deposit_channel_id_batch_id" btree (channel_id, batch_id)
> INVALID
> 
> And when dropping the invalid index, also takes a long time, my connection
> timed out, then when logging back in and check the table, it hasn't dropped.

This means that you have some very long transactions.

To make/drop index concurrently, all transactions that have started
before you started create/drop, have to finish.

You can see your oldest transactions by doing:

select * from pg_stat_activity where xact_start is not null order by xact_start

Best regards,

depesz