Re: Unable to Create or Drop Index Concurrently

Apparently just leaving it alone until tomorrow managed to finish
creating/dropping the index. Thank you all very much.

On Thu, Aug 18, 2022 at 5:00 PM hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:

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

--
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043