Обсуждение: pgsql: Add support for partitioned tables and indexes in REINDEX

Add support for partitioned tables and indexes in REINDEX

Until now, REINDEX was not able to work with partitioned tables and
indexes, forcing users to reindex partitions one by one.  This extends
REINDEX INDEX and REINDEX TABLE so as they can accept a partitioned
index and table in input, respectively, to reindex all the partitions
assigned to them with physical storage (foreign tables, partitioned
tables and indexes are then discarded).

This shares some logic with schema and database REINDEX as each
partition gets processed in its own transaction after building a list of
relations to work on.  This choice has the advantage to minimize the
number of invalid indexes to one partition with REINDEX CONCURRENTLY in
the event a cancellation or failure in-flight, as the only indexes
handled at once in a single REINDEX CONCURRENTLY loop are the ones from
the partition being working on.

Isolation tests are added to emulate some cases I bumped into while
developing this feature, particularly with the concurrent drop of a
leaf partition reindexed.  However, this is rather limited as LOCK would
cause REINDEX to block in the first transaction building the list of
partitions.

Per its multi-transaction nature, this new flavor cannot run in a
transaction block, similarly to REINDEX SCHEMA, SYSTEM and DATABASE.

Author: Justin Pryzby, Michael Paquier
Reviewed-by: Anastasia Lubennikova
Discussion: https://postgr.es/m/db12e897-73ff-467e-94cb-4af03705435f.adger.lj@alibaba-inc.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/a6642b3ae060976b42830b7dc8f29ec190ab05e4

Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out     |  21 ++
contrib/postgres_fdw/sql/postgres_fdw.sql          |  20 ++
doc/src/sgml/ref/reindex.sgml                      |  13 +-
src/backend/catalog/index.c                        |  25 +--
src/backend/commands/indexcmds.c                   | 245 ++++++++++++++++-----
src/backend/tcop/utility.c                         |   6 +-
src/include/commands/defrem.h                      |   4 +-
src/test/isolation/expected/reindex-partitions.out | 107 +++++++++
src/test/isolation/isolation_schedule              |   1 +
src/test/isolation/specs/reindex-partitions.spec   |  59 +++++
src/test/regress/expected/create_index.out         | 158 ++++++++++++-
src/test/regress/sql/create_index.sql              |  88 +++++++-
src/tools/pgindent/typedefs.list                   |   1 +
13 files changed, 656 insertions(+), 92 deletions(-)

Michael Paquier <michael@paquier.xyz> writes:
> Add support for partitioned tables and indexes in REINDEX

Buildfarm isn't finding the isolation test to be stable.

            regards, tom lane

On Mon, Sep 07, 2020 at 10:16:25PM -0400, Tom Lane wrote:
> Michael Paquier <michael@paquier.xyz> writes:
>> Add support for partitioned tables and indexes in REINDEX
>
> Buildfarm isn't finding the isolation test to be stable.

Yes, I have noticed the failure, and waited for more data to gather
while thinking about what I could do.  I have not come up with
anything, at least yet, so for now I have just removed the test.  The
thing was rather limited as-is as it was able just interact with the
first transaction of REINDEX building the list of partitions.
Perhaps, by using a fourth session taking a SHARE lock while another
uses at least a SHARE UPDATE EXCLUSIVE lock on one of the partitions,
it should be possible to make REINDEX wait in one of its inner
transactions for a partition.  It may be tricky to make that reliable
for the buildfarm though.
--
Michael