Re: Unable to Create or Drop Index Concurrently

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