Search Postgresql Archives

Re: Unable to Create or Drop Index Concurrently

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux