Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita6dcf9d

Browse files
committed
Rework handling of invalid indexes with REINDEX CONCURRENTLY
Per discussion with others, allowing REINDEX INDEX CONCURRENTLY to workfor invalid indexes when working directly on them can have a lot ofvalue to unlock situations with invalid indexes without having to use adance involving DROP INDEX followed by an extra CREATE INDEXCONCURRENTLY (which would not work for indexes with constraintdependency anyway). This also does not create extra bloat on therelation involved as this works on individual indexes, so let's enableit.Note that REINDEX TABLE CONCURRENTLY still bypasses invalid indexes aswe don't want to bloat the number of indexes defined on a relation inthe event of multiple and successive failures of REINDEX CONCURRENTLY.More regression tests are added to cover those behaviors, using aninvalid index created with CREATE INDEX CONCURRENTLY.Reported-by: Dagfinn Ilmari Mannsåker, Álvaro HerreraAuthor: Michael PaquierReviewed-by: Peter Eisentraut, Dagfinn Ilmari MannsåkerDiscussion:https://postgr.es/m/20190411134947.GA22043@alvherre.pgsql
1 parentc8e0f6b commita6dcf9d

File tree

5 files changed

+80
-33
lines changed

5 files changed

+80
-33
lines changed

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -586,10 +586,8 @@ Indexes:
586586

587587
The recommended recovery
588588
method in such cases is to drop the index and try again to perform
589-
<command>CREATE INDEX CONCURRENTLY</command>. (Another possibility is to rebuild
590-
the index with <command>REINDEX</command>. However, since <command>REINDEX</command>
591-
does not support concurrent builds, this option is unlikely to seem
592-
attractive.)
589+
<command>CREATE INDEX CONCURRENTLY</command>. (Another possibility is
590+
to rebuild the index with <command>REINDEX INDEX CONCURRENTLY</command>).
593591
</para>
594592

595593
<para>

‎doc/src/sgml/ref/reindex.sgml

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -65,12 +65,11 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURR
6565

6666
<listitem>
6767
<para>
68-
An index build with the <literal>CONCURRENTLY</literal> option failed, leaving
69-
an <quote>invalid</quote> index. Such indexes are useless but it can be
70-
convenient to use <command>REINDEX</command> to rebuild them. Note that
71-
<command>REINDEX</command> will not perform a concurrent build on an invalid index. To build the
72-
index without interfering with production you should drop the index and
73-
reissue the <command>CREATE INDEX CONCURRENTLY</command> command.
68+
If an index build fails with the <literal>CONCURRENTLY</literal> option,
69+
this index is left as <quote>invalid</quote>. Such indexes are useless
70+
but it can be convenient to use <command>REINDEX</command> to rebuild
71+
them. Note that only <command>REINDEX INDEX</command> is able
72+
to perform a concurrent build on an invalid index.
7473
</para>
7574
</listitem>
7675

‎src/backend/commands/indexcmds.c

Lines changed: 6 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -2776,11 +2776,6 @@ ReindexRelationConcurrently(Oid relationOid, int options)
27762776
}
27772777
caseRELKIND_INDEX:
27782778
{
2779-
/*
2780-
* For an index simply add its Oid to list. Invalid indexes
2781-
* cannot be included in list.
2782-
*/
2783-
RelationindexRelation=index_open(relationOid,ShareUpdateExclusiveLock);
27842779
OidheapId=IndexGetRelation(relationOid, false);
27852780

27862781
/* A shared relation cannot be reindexed concurrently */
@@ -2801,25 +2796,13 @@ ReindexRelationConcurrently(Oid relationOid, int options)
28012796
/* Track the heap relation of this index for session locks */
28022797
heapRelationIds=list_make1_oid(heapId);
28032798

2804-
MemoryContextSwitchTo(oldcontext);
2805-
2806-
if (!indexRelation->rd_index->indisvalid)
2807-
ereport(WARNING,
2808-
(errcode(ERRCODE_INDEX_CORRUPTED),
2809-
errmsg("cannot reindex concurrently invalid index \"%s.%s\", skipping",
2810-
get_namespace_name(get_rel_namespace(relationOid)),
2811-
get_rel_name(relationOid))));
2812-
else
2813-
{
2814-
/* Save the list of relation OIDs in private context */
2815-
oldcontext=MemoryContextSwitchTo(private_context);
2816-
2817-
indexIds=lappend_oid(indexIds,relationOid);
2818-
2819-
MemoryContextSwitchTo(oldcontext);
2820-
}
2799+
/*
2800+
* Save the list of relation OIDs in private context. Note
2801+
* that invalid indexes are allowed here.
2802+
*/
2803+
indexIds=lappend_oid(indexIds,relationOid);
28212804

2822-
index_close(indexRelation,NoLock);
2805+
MemoryContextSwitchTo(oldcontext);
28232806
break;
28242807
}
28252808
caseRELKIND_PARTITIONED_TABLE:

‎src/test/regress/expected/create_index.out

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2118,6 +2118,53 @@ Referenced by:
21182118

21192119
DROP MATERIALIZED VIEW concur_reindex_matview;
21202120
DROP TABLE concur_reindex_tab, concur_reindex_tab2, concur_reindex_tab3;
2121+
-- Check handling of invalid indexes
2122+
CREATE TABLE concur_reindex_tab4 (c1 int);
2123+
INSERT INTO concur_reindex_tab4 VALUES (1), (1), (2);
2124+
-- This trick creates an invalid index.
2125+
CREATE UNIQUE INDEX CONCURRENTLY concur_reindex_ind5 ON concur_reindex_tab4 (c1);
2126+
ERROR: could not create unique index "concur_reindex_ind5"
2127+
DETAIL: Key (c1)=(1) is duplicated.
2128+
-- Reindexing concurrently this index fails with the same failure.
2129+
-- The extra index created is itself invalid, and can be dropped.
2130+
REINDEX INDEX CONCURRENTLY concur_reindex_ind5;
2131+
ERROR: could not create unique index "concur_reindex_ind5_ccnew"
2132+
DETAIL: Key (c1)=(1) is duplicated.
2133+
\d concur_reindex_tab4
2134+
Table "public.concur_reindex_tab4"
2135+
Column | Type | Collation | Nullable | Default
2136+
--------+---------+-----------+----------+---------
2137+
c1 | integer | | |
2138+
Indexes:
2139+
"concur_reindex_ind5" UNIQUE, btree (c1) INVALID
2140+
"concur_reindex_ind5_ccnew" UNIQUE, btree (c1) INVALID
2141+
2142+
DROP INDEX concur_reindex_ind5_ccnew;
2143+
-- This makes the previous failure go away, so the index can become valid.
2144+
DELETE FROM concur_reindex_tab4 WHERE c1 = 1;
2145+
-- The invalid index is not processed when running REINDEX TABLE.
2146+
REINDEX TABLE CONCURRENTLY concur_reindex_tab4;
2147+
WARNING: cannot reindex concurrently invalid index "public.concur_reindex_ind5", skipping
2148+
NOTICE: table "concur_reindex_tab4" has no indexes
2149+
\d concur_reindex_tab4
2150+
Table "public.concur_reindex_tab4"
2151+
Column | Type | Collation | Nullable | Default
2152+
--------+---------+-----------+----------+---------
2153+
c1 | integer | | |
2154+
Indexes:
2155+
"concur_reindex_ind5" UNIQUE, btree (c1) INVALID
2156+
2157+
-- But it is fixed with REINDEX INDEX.
2158+
REINDEX INDEX CONCURRENTLY concur_reindex_ind5;
2159+
\d concur_reindex_tab4
2160+
Table "public.concur_reindex_tab4"
2161+
Column | Type | Collation | Nullable | Default
2162+
--------+---------+-----------+----------+---------
2163+
c1 | integer | | |
2164+
Indexes:
2165+
"concur_reindex_ind5" UNIQUE, btree (c1)
2166+
2167+
DROP TABLE concur_reindex_tab4;
21212168
--
21222169
-- REINDEX SCHEMA
21232170
--

‎src/test/regress/sql/create_index.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -849,6 +849,26 @@ REINDEX SCHEMA CONCURRENTLY pg_catalog;
849849
DROP MATERIALIZED VIEW concur_reindex_matview;
850850
DROPTABLE concur_reindex_tab, concur_reindex_tab2, concur_reindex_tab3;
851851

852+
-- Check handling of invalid indexes
853+
CREATETABLEconcur_reindex_tab4 (c1int);
854+
INSERT INTO concur_reindex_tab4VALUES (1), (1), (2);
855+
-- This trick creates an invalid index.
856+
CREATEUNIQUE INDEXCONCURRENTLY concur_reindex_ind5ON concur_reindex_tab4 (c1);
857+
-- Reindexing concurrently this index fails with the same failure.
858+
-- The extra index created is itself invalid, and can be dropped.
859+
REINDEX INDEX CONCURRENTLY concur_reindex_ind5;
860+
\d concur_reindex_tab4
861+
DROPINDEX concur_reindex_ind5_ccnew;
862+
-- This makes the previous failure go away, so the index can become valid.
863+
DELETEFROM concur_reindex_tab4WHERE c1=1;
864+
-- The invalid index is not processed when running REINDEX TABLE.
865+
REINDEX TABLE CONCURRENTLY concur_reindex_tab4;
866+
\d concur_reindex_tab4
867+
-- But it is fixed with REINDEX INDEX.
868+
REINDEX INDEX CONCURRENTLY concur_reindex_ind5;
869+
\d concur_reindex_tab4
870+
DROPTABLE concur_reindex_tab4;
871+
852872
--
853873
-- REINDEX SCHEMA
854874
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp