Alter index rename concurrently to
Andrey Klychkov <aaklychkov@mail.ru>
From: Andrey Klychkov <aaklychkov@mail.ru>
To: pgsql-hackers@postgresql.org
Date: 2018-07-16T18:58:06Z
Lists: pgsql-hackers
Attachments
- rename_concurrent.patch (application/x-patch) patch
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
Commits
-
Lower lock level for renaming indexes
- 1b5d797cd4f7 12.0 landed