Alter index rename concurrently to

From: Andrey Klychkov <aaklychkov(at)mail(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Alter index rename concurrently to
Date: 2018-07-16 18:58:06
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

Attachment Content-Type Size
rename_concurrent.patch application/x-patch 21.6 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Borodin 2018-07-16 19:19:05 Re: Alter index rename concurrently to
Previous Message Heikki Linnakangas 2018-07-16 18:41:51 Re: [HACKERS] WAL logging problem in 9.4.3?