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 <dep...@depesz.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
