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

Commit9055344

Browse files
committed
Fix concurrent indexing operations with temporary tables
Attempting to use CREATE INDEX, DROP INDEX or REINDEX with CONCURRENTLYon a temporary relation with ON COMMIT actions triggered unexpectederrors because those operations use multiple transactions internally tocomplete their work. Here is for example one confusing error when usingON COMMIT DELETE ROWS:ERROR: index "foo" already contains dataIssues related to temporary relations and concurrent indexing are fixedin this commit by enforcing the non-concurrent path to be taken fortemporary relations even if using CONCURRENTLY, transparently to theuser. Using a non-concurrent path does not matter in practice as lockscannot be taken on a temporary relation by a session different than theone owning the relation, and the non-concurrent operation is moreeffective.The problem exists with REINDEX since v12 with the introduction ofCONCURRENTLY, and with CREATE/DROP INDEX since CONCURRENTLY exists forthose commands. In all supported versions, this caused only confusingerror messages to be generated. Note that with REINDEX, it was alsopossible to issue a REINDEX CONCURRENTLY for a temporary relation ownedby a different session, leading to a server crash.The idea to enforce transparently the non-concurrent code path fortemporary relations comes originally from Andres Freund.Reported-by: Manuel RiggerAuthor: Michael Paquier, Heikki LinnakangasReviewed-by: Andres Freund, Álvaro Herrera, Heikki LinnakangasDiscussion:https://postgr.es/m/CA+u7OA6gP7YAeCguyseusYcc=uR8+ypjCcgDDCTzjQ+k6S9ksQ@mail.gmail.comBackpatch-through: 9.4
1 parent8bb006a commit9055344

File tree

7 files changed

+106
-8
lines changed

7 files changed

+106
-8
lines changed

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
123123
&mdash; see <xref linkend="SQL-CREATEINDEX-CONCURRENTLY"
124124
endterm="SQL-CREATEINDEX-CONCURRENTLY-title">.
125125
</para>
126+
<para>
127+
For temporary tables, <command>CREATE INDEX</command> is always
128+
non-concurrent, as no other session can access them, and
129+
non-concurrent index creation is cheaper.
130+
</para>
126131
</listitem>
127132
</varlistentry>
128133

‎doc/src/sgml/ref/drop_index.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,11 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="PARAMETER">name</r
5858
performed within a transaction block, but
5959
<command>DROP INDEX CONCURRENTLY</> cannot.
6060
</para>
61+
<para>
62+
For temporary tables, <command>DROP INDEX</command> is always
63+
non-concurrent, as no other session can access them, and
64+
non-concurrent index drop is cheaper.
65+
</para>
6166
</listitem>
6267
</varlistentry>
6368

‎src/backend/catalog/index.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1374,6 +1374,15 @@ index_drop(Oid indexId, bool concurrent)
13741374
LOCKTAGheaplocktag;
13751375
LOCKMODElockmode;
13761376

1377+
/*
1378+
* A temporary relation uses a non-concurrent DROP. Other backends can't
1379+
* access a temporary relation, so there's no harm in grabbing a stronger
1380+
* lock (see comments in RemoveRelations), and a non-concurrent DROP is
1381+
* more efficient.
1382+
*/
1383+
Assert(get_rel_persistence(indexId)!=RELPERSISTENCE_TEMP||
1384+
!concurrent);
1385+
13771386
/*
13781387
* To drop an index safely, we must grab exclusive lock on its parent
13791388
* table. Exclusive lock on the index alone is insufficient because

‎src/backend/commands/indexcmds.c

Lines changed: 20 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -315,6 +315,7 @@ DefineIndex(Oid relationId,
315315
boolskip_build,
316316
boolquiet)
317317
{
318+
boolconcurrent;
318319
char*indexRelationName;
319320
char*accessMethodName;
320321
Oid*typeObjectId;
@@ -345,6 +346,18 @@ DefineIndex(Oid relationId,
345346
Snapshotsnapshot;
346347
inti;
347348

349+
/*
350+
* Force non-concurrent build on temporary relations, even if CONCURRENTLY
351+
* was requested. Other backends can't access a temporary relation, so
352+
* there's no harm in grabbing a stronger lock, and a non-concurrent DROP
353+
* is more efficient. Do this before any use of the concurrent option is
354+
* done.
355+
*/
356+
if (stmt->concurrent&&get_rel_persistence(relationId)!=RELPERSISTENCE_TEMP)
357+
concurrent= true;
358+
else
359+
concurrent= false;
360+
348361
/*
349362
* count attributes in index
350363
*/
@@ -370,7 +383,7 @@ DefineIndex(Oid relationId,
370383
* relation. To avoid lock upgrade hazards, that lock should be at least
371384
* as strong as the one we take here.
372385
*/
373-
lockmode=stmt->concurrent ?ShareUpdateExclusiveLock :ShareLock;
386+
lockmode=concurrent ?ShareUpdateExclusiveLock :ShareLock;
374387
rel=heap_open(relationId,lockmode);
375388

376389
relationId=RelationGetRelid(rel);
@@ -571,8 +584,8 @@ DefineIndex(Oid relationId,
571584
indexInfo->ii_ExclusionStrats=NULL;
572585
indexInfo->ii_Unique=stmt->unique;
573586
/* In a concurrent build, mark it not-ready-for-inserts */
574-
indexInfo->ii_ReadyForInserts= !stmt->concurrent;
575-
indexInfo->ii_Concurrent=stmt->concurrent;
587+
indexInfo->ii_ReadyForInserts= !concurrent;
588+
indexInfo->ii_Concurrent=concurrent;
576589
indexInfo->ii_BrokenHotChain= false;
577590
indexInfo->ii_AmCache=NULL;
578591
indexInfo->ii_Context=CurrentMemoryContext;
@@ -660,7 +673,7 @@ DefineIndex(Oid relationId,
660673
* A valid stmt->oldNode implies that we already have a built form of the
661674
* index. The caller should also decline any index build.
662675
*/
663-
Assert(!OidIsValid(stmt->oldNode)|| (skip_build&& !stmt->concurrent));
676+
Assert(!OidIsValid(stmt->oldNode)|| (skip_build&& !concurrent));
664677

665678
/*
666679
* Make the catalog entries for the index, including constraints. Then, if
@@ -674,8 +687,8 @@ DefineIndex(Oid relationId,
674687
coloptions,reloptions,stmt->primary,
675688
stmt->isconstraint,stmt->deferrable,stmt->initdeferred,
676689
allowSystemTableMods,
677-
skip_build||stmt->concurrent,
678-
stmt->concurrent, !check_rights,
690+
skip_build||concurrent,
691+
concurrent, !check_rights,
679692
stmt->if_not_exists);
680693

681694
ObjectAddressSet(address,RelationRelationId,indexRelationId);
@@ -691,7 +704,7 @@ DefineIndex(Oid relationId,
691704
CreateComments(indexRelationId,RelationRelationId,0,
692705
stmt->idxcomment);
693706

694-
if (!stmt->concurrent)
707+
if (!concurrent)
695708
{
696709
/* Close the heap and we're done, in the non-concurrent case */
697710
heap_close(rel,NoLock);

‎src/backend/commands/tablecmds.c

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -966,7 +966,11 @@ RemoveRelations(DropStmt *drop)
966966
/* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */
967967
if (drop->concurrent)
968968
{
969-
flags |=PERFORM_DELETION_CONCURRENTLY;
969+
/*
970+
* Note that for temporary relations this lock may get upgraded
971+
* later on, but as no other session can access a temporary
972+
* relation, this is actually fine.
973+
*/
970974
lockmode=ShareUpdateExclusiveLock;
971975
Assert(drop->removeType==OBJECT_INDEX);
972976
if (list_length(drop->objects)!=1)
@@ -1058,6 +1062,18 @@ RemoveRelations(DropStmt *drop)
10581062
continue;
10591063
}
10601064

1065+
/*
1066+
* Decide if concurrent mode needs to be used here or not. The
1067+
* relation persistence cannot be known without its OID.
1068+
*/
1069+
if (drop->concurrent&&
1070+
get_rel_persistence(relOid)!=RELPERSISTENCE_TEMP)
1071+
{
1072+
Assert(list_length(drop->objects)==1&&
1073+
drop->removeType==OBJECT_INDEX);
1074+
flags |=PERFORM_DELETION_CONCURRENTLY;
1075+
}
1076+
10611077
/* OK, we're ready to delete this one */
10621078
obj.classId=RelationRelationId;
10631079
obj.objectId=relOid;

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2514,6 +2514,31 @@ Indexes:
25142514
"concur_index5" btree (f2) WHERE f1 = 'x'::text
25152515
"std_index" btree (f2)
25162516

2517+
-- Temporary tables with concurrent builds and on-commit actions
2518+
-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored.
2519+
-- PRESERVE ROWS, the default.
2520+
CREATE TEMP TABLE concur_temp (f1 int, f2 text)
2521+
ON COMMIT PRESERVE ROWS;
2522+
INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
2523+
CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);
2524+
DROP INDEX CONCURRENTLY concur_temp_ind;
2525+
DROP TABLE concur_temp;
2526+
-- ON COMMIT DROP
2527+
BEGIN;
2528+
CREATE TEMP TABLE concur_temp (f1 int, f2 text)
2529+
ON COMMIT DROP;
2530+
INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
2531+
-- Fails when running in a transaction.
2532+
CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);
2533+
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
2534+
COMMIT;
2535+
-- ON COMMIT DELETE ROWS
2536+
CREATE TEMP TABLE concur_temp (f1 int, f2 text)
2537+
ON COMMIT DELETE ROWS;
2538+
INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar');
2539+
CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1);
2540+
DROP INDEX CONCURRENTLY concur_temp_ind;
2541+
DROP TABLE concur_temp;
25172542
--
25182543
-- Try some concurrent index drops
25192544
--

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -795,6 +795,31 @@ VACUUM FULL concur_heap;
795795
REINDEX TABLE concur_heap;
796796
\d concur_heap
797797

798+
-- Temporary tables with concurrent builds and on-commit actions
799+
-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored.
800+
-- PRESERVE ROWS, the default.
801+
CREATE TEMP TABLE concur_temp (f1int, f2text)
802+
ONCOMMIT PRESERVE ROWS;
803+
INSERT INTO concur_tempVALUES (1,'foo'), (2,'bar');
804+
CREATEINDEXCONCURRENTLY concur_temp_indON concur_temp(f1);
805+
DROPINDEX CONCURRENTLY concur_temp_ind;
806+
DROPTABLE concur_temp;
807+
-- ON COMMIT DROP
808+
BEGIN;
809+
CREATE TEMP TABLE concur_temp (f1int, f2text)
810+
ONCOMMIT DROP;
811+
INSERT INTO concur_tempVALUES (1,'foo'), (2,'bar');
812+
-- Fails when running in a transaction.
813+
CREATEINDEXCONCURRENTLY concur_temp_indON concur_temp(f1);
814+
COMMIT;
815+
-- ON COMMIT DELETE ROWS
816+
CREATE TEMP TABLE concur_temp (f1int, f2text)
817+
ONCOMMITDELETE ROWS;
818+
INSERT INTO concur_tempVALUES (1,'foo'), (2,'bar');
819+
CREATEINDEXCONCURRENTLY concur_temp_indON concur_temp(f1);
820+
DROPINDEX CONCURRENTLY concur_temp_ind;
821+
DROPTABLE concur_temp;
822+
798823
--
799824
-- Try some concurrent index drops
800825
--

0 commit comments

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