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

Commit8cb5365

Browse files
Add DROP INDEX CONCURRENTLY [IF EXISTS], uses ShareUpdateExclusiveLock
1 parent21cc529 commit8cb5365

File tree

14 files changed

+337
-26
lines changed

14 files changed

+337
-26
lines changed

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

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
DROP INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
24+
DROP INDEX [CONCURRENTLY ] [IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
2525
</synopsis>
2626
</refsynopsisdiv>
2727

@@ -49,6 +49,29 @@ DROP INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ..
4949
</listitem>
5050
</varlistentry>
5151

52+
<varlistentry>
53+
<term><literal>CONCURRENTLY</literal></term>
54+
<listitem>
55+
<para>
56+
When this option is used, <productname>PostgreSQL</> will drop the
57+
index without taking any locks that prevent concurrent selects, inserts,
58+
updates, or deletes on the table; whereas a standard index drop
59+
waits for a lock that locks out everything on the table until it's done.
60+
Concurrent drop index is a two stage process. First, we mark the index
61+
both invalid and not ready then commit the change. Next we wait until
62+
there are no users locking the table who can see the index.
63+
</para>
64+
<para>
65+
There are several caveats to be aware of when using this option.
66+
Only one index name can be specified if the <literal>CONCURRENTLY</literal>
67+
parameter is specified. Regular <command>DROP INDEX</> command can be
68+
performed within a transaction block, but
69+
<command>DROP INDEX CONCURRENTLY</> cannot.
70+
The CASCADE option is not supported when dropping an index concurrently.
71+
</para>
72+
</listitem>
73+
</varlistentry>
74+
5275
<varlistentry>
5376
<term><replaceable class="PARAMETER">name</replaceable></term>
5477
<listitem>

‎src/backend/catalog/dependency.c

Lines changed: 32 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -174,8 +174,8 @@ static void reportDependentObjects(const ObjectAddresses *targetObjects,
174174
constObjectAddress*origObject);
175175
staticvoiddeleteOneObject(constObjectAddress*object,
176176
RelationdepRel,int32flags);
177-
staticvoiddoDeletion(constObjectAddress*object);
178-
staticvoidAcquireDeletionLock(constObjectAddress*object);
177+
staticvoiddoDeletion(constObjectAddress*object,intflags);
178+
staticvoidAcquireDeletionLock(constObjectAddress*object,intflags);
179179
staticvoidReleaseDeletionLock(constObjectAddress*object);
180180
staticboolfind_expr_references_walker(Node*node,
181181
find_expr_references_context*context);
@@ -233,7 +233,7 @@ performDeletion(const ObjectAddress *object,
233233
* Acquire deletion lock on the target object.(Ideally the caller has
234234
* done this already, but many places are sloppy about it.)
235235
*/
236-
AcquireDeletionLock(object);
236+
AcquireDeletionLock(object,0);
237237

238238
/*
239239
* Construct a list of objects to delete (ie, the given object plus
@@ -317,7 +317,7 @@ performMultipleDeletions(const ObjectAddresses *objects,
317317
* Acquire deletion lock on each target object. (Ideally the caller
318318
* has done this already, but many places are sloppy about it.)
319319
*/
320-
AcquireDeletionLock(thisobj);
320+
AcquireDeletionLock(thisobj,flags);
321321

322322
findDependentObjects(thisobj,
323323
DEPFLAG_ORIGINAL,
@@ -351,7 +351,11 @@ performMultipleDeletions(const ObjectAddresses *objects,
351351
/* And clean up */
352352
free_object_addresses(targetObjects);
353353

354-
heap_close(depRel,RowExclusiveLock);
354+
/*
355+
* We closed depRel earlier in deleteOneObject if doing a drop concurrently
356+
*/
357+
if ((flags&PERFORM_DELETION_CONCURRENTLY)!=PERFORM_DELETION_CONCURRENTLY)
358+
heap_close(depRel,RowExclusiveLock);
355359
}
356360

357361
/*
@@ -381,7 +385,7 @@ deleteWhatDependsOn(const ObjectAddress *object,
381385
* Acquire deletion lock on the target object.(Ideally the caller has
382386
* done this already, but many places are sloppy about it.)
383387
*/
384-
AcquireDeletionLock(object);
388+
AcquireDeletionLock(object,0);
385389

386390
/*
387391
* Construct a list of objects to delete (ie, the given object plus
@@ -631,7 +635,7 @@ findDependentObjects(const ObjectAddress *object,
631635
* deletion of the owning object.)
632636
*/
633637
ReleaseDeletionLock(object);
634-
AcquireDeletionLock(&otherObject);
638+
AcquireDeletionLock(&otherObject,0);
635639

636640
/*
637641
* The owning object might have been deleted while we waited
@@ -726,7 +730,7 @@ findDependentObjects(const ObjectAddress *object,
726730
/*
727731
* Must lock the dependent object before recursing to it.
728732
*/
729-
AcquireDeletionLock(&otherObject);
733+
AcquireDeletionLock(&otherObject,0);
730734

731735
/*
732736
* The dependent object might have been deleted while we waited to
@@ -1044,10 +1048,17 @@ deleteOneObject(const ObjectAddress *object, Relation depRel, int flags)
10441048
deleteSharedDependencyRecordsFor(object->classId,object->objectId,
10451049
object->objectSubId);
10461050

1051+
/*
1052+
* Close depRel if we are doing a drop concurrently because it
1053+
* commits the transaction, so we don't want dangling references.
1054+
*/
1055+
if ((flags&PERFORM_DELETION_CONCURRENTLY)==PERFORM_DELETION_CONCURRENTLY)
1056+
heap_close(depRel,RowExclusiveLock);
1057+
10471058
/*
10481059
* Now delete the object itself, in an object-type-dependent way.
10491060
*/
1050-
doDeletion(object);
1061+
doDeletion(object,flags);
10511062

10521063
/*
10531064
* Delete any comments or security labels associated with this object.
@@ -1072,7 +1083,7 @@ deleteOneObject(const ObjectAddress *object, Relation depRel, int flags)
10721083
* doDeletion: actually delete a single object
10731084
*/
10741085
staticvoid
1075-
doDeletion(constObjectAddress*object)
1086+
doDeletion(constObjectAddress*object,intflags)
10761087
{
10771088
switch (getObjectClass(object))
10781089
{
@@ -1082,8 +1093,11 @@ doDeletion(const ObjectAddress *object)
10821093

10831094
if (relKind==RELKIND_INDEX)
10841095
{
1096+
boolconcurrent= ((flags&PERFORM_DELETION_CONCURRENTLY)
1097+
==PERFORM_DELETION_CONCURRENTLY);
1098+
10851099
Assert(object->objectSubId==0);
1086-
index_drop(object->objectId);
1100+
index_drop(object->objectId,concurrent);
10871101
}
10881102
else
10891103
{
@@ -1219,10 +1233,15 @@ doDeletion(const ObjectAddress *object)
12191233
* shared-across-databases object, so we have no need for LockSharedObject.
12201234
*/
12211235
staticvoid
1222-
AcquireDeletionLock(constObjectAddress*object)
1236+
AcquireDeletionLock(constObjectAddress*object,intflags)
12231237
{
12241238
if (object->classId==RelationRelationId)
1225-
LockRelationOid(object->objectId,AccessExclusiveLock);
1239+
{
1240+
if ((flags&PERFORM_DELETION_CONCURRENTLY)==PERFORM_DELETION_CONCURRENTLY)
1241+
LockRelationOid(object->objectId,ShareUpdateExclusiveLock);
1242+
else
1243+
LockRelationOid(object->objectId,AccessExclusiveLock);
1244+
}
12261245
else
12271246
/* assume we should lock the whole object not a sub-object */
12281247
LockDatabaseObject(object->classId,object->objectId,0,

‎src/backend/catalog/index.c

Lines changed: 133 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1282,14 +1282,20 @@ index_constraint_create(Relation heapRelation,
12821282
* else associated dependencies won't be cleaned up.
12831283
*/
12841284
void
1285-
index_drop(OidindexId)
1285+
index_drop(OidindexId,boolconcurrent)
12861286
{
12871287
OidheapId;
12881288
RelationuserHeapRelation;
12891289
RelationuserIndexRelation;
12901290
RelationindexRelation;
12911291
HeapTupletuple;
12921292
boolhasexprs;
1293+
LockRelIdheaprelid,
1294+
indexrelid;
1295+
LOCKTAGheaplocktag,
1296+
indexlocktag;
1297+
VirtualTransactionId*old_lockholders;
1298+
Form_pg_indexindexForm;
12931299

12941300
/*
12951301
* To drop an index safely, we must grab exclusive lock on its parent
@@ -1302,16 +1308,128 @@ index_drop(Oid indexId)
13021308
* that will make them update their index lists.
13031309
*/
13041310
heapId=IndexGetRelation(indexId, false);
1305-
userHeapRelation=heap_open(heapId,AccessExclusiveLock);
1306-
1307-
userIndexRelation=index_open(indexId,AccessExclusiveLock);
1311+
if (concurrent)
1312+
{
1313+
userHeapRelation=heap_open(heapId,ShareUpdateExclusiveLock);
1314+
userIndexRelation=index_open(indexId,ShareUpdateExclusiveLock);
1315+
}
1316+
else
1317+
{
1318+
userHeapRelation=heap_open(heapId,AccessExclusiveLock);
1319+
userIndexRelation=index_open(indexId,AccessExclusiveLock);
1320+
}
13081321

13091322
/*
1310-
* There can no longer be anyone *else* touching the index, but we might
1311-
* still have open queries using it in our own session.
1323+
* We might still have open queries using it in our own session.
13121324
*/
13131325
CheckTableNotInUse(userIndexRelation,"DROP INDEX");
13141326

1327+
/*
1328+
* Drop Index concurrently is similar in many ways to creating an
1329+
* index concurrently, so some actions are similar to DefineIndex()
1330+
*/
1331+
if (concurrent)
1332+
{
1333+
/*
1334+
* Mark index invalid by updating its pg_index entry
1335+
*
1336+
* Don't Assert(indexForm->indisvalid) because we may be trying to
1337+
* clear up after an error when trying to create an index which left
1338+
* the index invalid
1339+
*/
1340+
indexRelation=heap_open(IndexRelationId,RowExclusiveLock);
1341+
1342+
tuple=SearchSysCacheCopy1(INDEXRELID,
1343+
ObjectIdGetDatum(indexId));
1344+
if (!HeapTupleIsValid(tuple))
1345+
elog(ERROR,"cache lookup failed for index %u",indexId);
1346+
indexForm= (Form_pg_index)GETSTRUCT(tuple);
1347+
1348+
indexForm->indisvalid= false;/* make unusable for queries */
1349+
indexForm->indisready= false;/* make invisible to changes */
1350+
1351+
simple_heap_update(indexRelation,&tuple->t_self,tuple);
1352+
CatalogUpdateIndexes(indexRelation,tuple);
1353+
1354+
heap_close(indexRelation,RowExclusiveLock);
1355+
1356+
/*
1357+
* Invalidate the relcache for the table, so that after this
1358+
* transaction we will refresh the index list. Forgetting just the
1359+
* index is not enough.
1360+
*/
1361+
CacheInvalidateRelcache(userHeapRelation);
1362+
1363+
/* save lockrelid and locktag for below, then close but keep locks */
1364+
heaprelid=userHeapRelation->rd_lockInfo.lockRelId;
1365+
SET_LOCKTAG_RELATION(heaplocktag,heaprelid.dbId,heaprelid.relId);
1366+
heap_close(userHeapRelation,NoLock);
1367+
1368+
indexrelid=userIndexRelation->rd_lockInfo.lockRelId;
1369+
SET_LOCKTAG_RELATION(indexlocktag,indexrelid.dbId,indexrelid.relId);
1370+
index_close(userIndexRelation,NoLock);
1371+
1372+
/*
1373+
* For a concurrent drop, it's important to make the catalog entries
1374+
* visible to other transactions before we drop the index. The index
1375+
* will be marked not indisvalid, so that no one else tries to either
1376+
* insert into it or use it for queries.
1377+
*
1378+
* We must commit our current transaction so that the index update becomes
1379+
* visible; then start another. Note that all the data structures we just
1380+
* built are lost in the commit. The only data we keep past here are the
1381+
* relation IDs.
1382+
*
1383+
* Before committing, get a session-level lock on the table, to ensure
1384+
* that neither it nor the index can be dropped before we finish. This
1385+
* cannot block, even if someone else is waiting for access, because we
1386+
* already have the same lock within our transaction.
1387+
*/
1388+
LockRelationIdForSession(&heaprelid,ShareUpdateExclusiveLock);
1389+
LockRelationIdForSession(&indexrelid,ShareUpdateExclusiveLock);
1390+
1391+
PopActiveSnapshot();
1392+
CommitTransactionCommand();
1393+
StartTransactionCommand();
1394+
1395+
/*
1396+
* Now we must wait until no running transaction could have the table open
1397+
* with the old list of indexes. To do this, inquire which xacts
1398+
* currently would conflict with AccessExclusiveLock on the table -- ie,
1399+
* which ones have a lock of any kind on the table.Then wait for each of
1400+
* these xacts to commit or abort.Note we do not need to worry about
1401+
* xacts that open the table for writing after this point; they will see
1402+
* the index as invalid when they open the relation.
1403+
*
1404+
* Note: the reason we use actual lock acquisition here, rather than just
1405+
* checking the ProcArray and sleeping, is that deadlock is possible if
1406+
* one of the transactions in question is blocked trying to acquire an
1407+
* exclusive lock on our table. The lock code will detect deadlock and
1408+
* error out properly.
1409+
*
1410+
* Note: GetLockConflicts() never reports our own xid, hence we need not
1411+
* check for that.Also, prepared xacts are not reported, which is fine
1412+
* since they certainly aren't going to do anything more.
1413+
*/
1414+
old_lockholders=GetLockConflicts(&heaplocktag,AccessExclusiveLock);
1415+
1416+
while (VirtualTransactionIdIsValid(*old_lockholders))
1417+
{
1418+
VirtualXactLock(*old_lockholders, true);
1419+
old_lockholders++;
1420+
}
1421+
1422+
/*
1423+
* Re-open relations to allow us to complete our actions.
1424+
*
1425+
* At this point, nothing should be accessing the index, but lets
1426+
* leave nothing to chance and grab AccessExclusiveLock on the index
1427+
* before the physical deletion.
1428+
*/
1429+
userHeapRelation=heap_open(heapId,ShareUpdateExclusiveLock);
1430+
userIndexRelation=index_open(indexId,AccessExclusiveLock);
1431+
}
1432+
13151433
/*
13161434
* All predicate locks on the index are about to be made invalid. Promote
13171435
* them to relation locks on the heap.
@@ -1378,6 +1496,15 @@ index_drop(Oid indexId)
13781496
* Close owning rel, but keep lock
13791497
*/
13801498
heap_close(userHeapRelation,NoLock);
1499+
1500+
/*
1501+
* Release the session locks before we go.
1502+
*/
1503+
if (concurrent)
1504+
{
1505+
UnlockRelationIdForSession(&heaprelid,ShareUpdateExclusiveLock);
1506+
UnlockRelationIdForSession(&indexrelid,ShareUpdateExclusiveLock);
1507+
}
13811508
}
13821509

13831510
/* ----------------------------------------------------------------

0 commit comments

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