Обсуждение: pgsql: REINDEX CONCURRENTLY

REINDEX CONCURRENTLY

This adds the CONCURRENTLY option to the REINDEX command.  A REINDEX
CONCURRENTLY on a specific index creates a new index (like CREATE
INDEX CONCURRENTLY), then renames the old index away and the new index
in place and adjusts the dependencies, and then drops the old
index (like DROP INDEX CONCURRENTLY).  The REINDEX command also has
the capability to run its other variants (TABLE, DATABASE) with the
CONCURRENTLY option (but not SYSTEM).

The reindexdb command gets the --concurrently option.

Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut
Reviewed-by: Andres Freund, Fujii Masao, Jim Nasby, Sergei Kornilov
Discussion:
https://www.postgresql.org/message-id/flat/60052986-956b-4478-45ed-8bd119e9b9cf%402ndquadrant.com#74948a1044c56c5e817a5050f554ddee

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/5dc92b844e680c54a7ecd68de0ba53c949c3d605

Modified Files
--------------
doc/src/sgml/mvcc.sgml                             |   1 +
doc/src/sgml/ref/create_index.sgml                 |   1 +
doc/src/sgml/ref/reindex.sgml                      | 190 ++++-
doc/src/sgml/ref/reindexdb.sgml                    |  10 +
src/backend/catalog/dependency.c                   |   7 +-
src/backend/catalog/index.c                        | 504 +++++++++++-
src/backend/catalog/pg_depend.c                    | 143 ++++
src/backend/commands/indexcmds.c                   | 889 ++++++++++++++++++---
src/backend/commands/tablecmds.c                   |  32 +-
src/backend/nodes/copyfuncs.c                      |   1 +
src/backend/nodes/equalfuncs.c                     |   1 +
src/backend/parser/gram.y                          |  22 +-
src/backend/tcop/utility.c                         |  10 +-
src/bin/psql/common.c                              |  16 +
src/bin/psql/tab-complete.c                        |  18 +-
src/bin/scripts/reindexdb.c                        |  50 +-
src/bin/scripts/t/090_reindexdb.pl                 |  29 +-
src/include/catalog/dependency.h                   |   6 +
src/include/catalog/index.h                        |  16 +-
src/include/commands/defrem.h                      |   6 +-
src/include/nodes/parsenodes.h                     |   1 +
.../isolation/expected/reindex-concurrently.out    |  78 ++
src/test/isolation/isolation_schedule              |   1 +
src/test/isolation/specs/reindex-concurrently.spec |  40 +
src/test/regress/expected/create_index.out         |  97 +++
src/test/regress/sql/create_index.sql              |  62 ++
26 files changed, 2048 insertions(+), 183 deletions(-)

Hi Peter,

On Fri, Mar 29, 2019 at 07:26:53AM +0000, Peter Eisentraut wrote:
> REINDEX CONCURRENTLY
>
> This adds the CONCURRENTLY option to the REINDEX command.  A REINDEX
> CONCURRENTLY on a specific index creates a new index (like CREATE
> INDEX CONCURRENTLY), then renames the old index away and the new index
> in place and adjusts the dependencies, and then drops the old
> index (like DROP INDEX CONCURRENTLY).  The REINDEX command also has
> the capability to run its other variants (TABLE, DATABASE) with the
> CONCURRENTLY option (but not SYSTEM).
>
> The reindexdb command gets the --concurrently option.

A couple of buildfarm members using clang are not happy with this
commit:
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+WARNING:  you don't own a lock of type ShareUpdateExclusiveLock

For example longfin and dragonet:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dragonet&dt=2019-03-29%2007%3A28%3A01
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=longfin&dt=2019-03-29%2007%3A29%3A04

I cannot dig into that myself now unfortunately..
--
Michael