Re: Unable to Create or Drop Index Concurrently - Mailing list pgsql-general

From hubert depesz lubaczewski
Subject Re: Unable to Create or Drop Index Concurrently
Date
Msg-id 20220818090044.GB12228@depesz.com
Whole thread Raw
In response to Unable to Create or Drop Index Concurrently  (Abdul Qoyyuum <aqoyyuum@cardaccess.com.au>)
Responses Re: Unable to Create or Drop Index Concurrently
List pgsql-general
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




pgsql-general by date:

Previous
From: hubert depesz lubaczewski
Date:
Subject: Re: Is it possible to keep indexes on different disk location?
Next
From: Vijaykumar Jain
Date:
Subject: Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time