Dear hackers!
I have an idea to facilitate work with index rebuilding.
Usually if we want to rebuild an index without table locks we should do the queries below:
1. create index concurrently... (with different name on the same columns)
2. drop index concurrently <old index>
3. alter index <new_index> rename to <like_old_index>
As you can see, only the last query in this simple algorithm locks table.
Commonly this steps are included to a more complex script and run by cron or manually.
When you have high load databases, maybe some problems appear:
1. it’s dangerous and unacceptable for production to wait unknown
number of minutes or even hours while a table is locked
2. we must write a complex script that sets statement timeout to rename and
implement several checks there to prevent stopping of production at nights
3. it’s uncomfortable to find indexes that couldn’t be renamed during script executing
Then we must execute renaming manually and interrupt it again and
again if it can’t execute in allowable time.
I made a patch to solve this issue (see the attachment).
It allows to avoid locks by a query like this:
“alter index <new_index> rename CONCURRENTLY to <old_index_name>”.
Briefly, I did it by using similar way in the index_create() and index_drop() functions
that set ShareUpdateExclusiveLock instead of AccessShareLock
when the structure DropStmt/CreateStmt has “true” in the stmt->concurrent field.
(In other words, when it "see" "concurretly" in query).
In all other cases (alter table, sequence, etc) I initialized this field as “false” respectively.
I ran it when another transactions to the same table are started but not finished.
Also I used a script that made SELECT\DML queries in a loop to that test tables and
"ALTER INDEX ... RENAME CONCURRENTLY TO ..." works without any errors and
indexes were valid after renaming.
Maybe it’s interesting for someone.
I’ll be very thankful for any ideas!
--
Kind regards,
Andrew Klychkov