On 2/9/23 07:45, Kiriakos Georgiou wrote:
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), I decide 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 drop index 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, but in the above scenario it
doesn’t appear to be “safe”.
I am trying to formulate a theory to explain this. Any ideas?
1)From here:
https://www.postgresql.org/docs/current/sql-dropindex.html
"With this option, the command instead waits until conflicting
transactions have completed."
2) Probably too late for this case, but info from
https://www.postgresql.org/docs/current/view-pg-locks.html
and
https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
would be useful.
Regards,
Kiriakos
--
Adrian Klaver
adrian.kla...@aklaver.com
