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

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

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