Concurrency issue with DROP INDEX CONCURRENTLY

Поиск
Список
Период
Сортировка
От Kiriakos Georgiou
Тема Concurrency issue with DROP INDEX CONCURRENTLY
Дата
Msg-id C6D0E2EE-8BD1-4567-A0A4-64A5C9C74E8D@olympiakos.com
обсуждение исходный текст
Ответы Re: Concurrency issue with DROP INDEX CONCURRENTLY  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Concurrency issue with DROP INDEX CONCURRENTLY  (Kiriakos Georgiou <kg.postgresql@olympiakos.com>)
Re: Concurrency issue with DROP INDEX CONCURRENTLY  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список pgsql-general
Hello,

I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I can summarize with this test scenario:

/**************************************************/

— suppose we have this table and index
create table test(x int);
create index idx1 on test(x);

— now suppose with the database “live” and the above table super busy (lots of queries on the table using index idx1),
Idecide to make the index unique 
create unique index concurrently idx2 on test(x); — runs fine
drop index concurrently idx1; — took 3 hours to finish, since the table is super busy

/**************************************************/

Taking 3 hours to drop the index is not surprising (lots of queries on the table using idx1).  What surprises me is the
dropindex causes havoc with concurrency on the table, causing queries to pile up. 
Once the drop index finishes, everything goes back to normal.

I thought by using the CONCURRENTLY option, the drop index is “safe” from concurrency issues for the underlying table,
butin the above scenario it doesn’t appear to be “safe”. 

I am trying to formulate a theory to explain this.  Any ideas?

Regards,
Kiriakos


В списке pgsql-general по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: PostgreSQL
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Using PostgreSQL for service discovery and health-check