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

Commit4abd7b4

Browse files
committed
Improve CREATE/DROP/RENAME DATABASE so that when failing because the source
or target database is being accessed by other users, it tells you whetherthe "other users" are live sessions or uncommitted prepared transactions.(Indeed, it tells you exactly how many of each, but that's mostly justbecause it was easy to do so.) This should help forestall the gotcha ofnot realizing that a prepared transaction is what's blocking the command.Per discussion.
1 parentec73b56 commit4abd7b4

File tree

3 files changed

+71
-36
lines changed

3 files changed

+71
-36
lines changed

‎src/backend/commands/dbcommands.c

Lines changed: 40 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.209 2008/05/12 00:00:47 alvherre Exp $
16+
* $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.210 2008/08/04 18:03:46 tgl Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -73,6 +73,7 @@ static bool get_db_info(const char *name, LOCKMODE lockmode,
7373
staticboolhave_createdb_privilege(void);
7474
staticvoidremove_dbtablespaces(Oiddb_id);
7575
staticboolcheck_db_file_conflict(Oiddb_id);
76+
staticinterrdetail_busy_db(intnotherbackends,intnpreparedxacts);
7677

7778

7879
/*
@@ -110,6 +111,8 @@ createdb(const CreatedbStmt *stmt)
110111
intencoding=-1;
111112
intdbconnlimit=-1;
112113
intctype_encoding;
114+
intnotherbackends;
115+
intnpreparedxacts;
113116
createdb_failure_paramsfparms;
114117

115118
/* Extract options from the statement node tree */
@@ -385,11 +388,12 @@ createdb(const CreatedbStmt *stmt)
385388
* potential waiting; we may as well throw an error first if we're gonna
386389
* throw one.
387390
*/
388-
if (CheckOtherDBBackends(src_dboid))
391+
if (CountOtherDBBackends(src_dboid,&notherbackends,&npreparedxacts))
389392
ereport(ERROR,
390393
(errcode(ERRCODE_OBJECT_IN_USE),
391394
errmsg("source database \"%s\" is being accessed by other users",
392-
dbtemplate)));
395+
dbtemplate),
396+
errdetail_busy_db(notherbackends,npreparedxacts)));
393397

394398
/*
395399
* Select an OID for the new database, checking that it doesn't have a
@@ -612,6 +616,8 @@ dropdb(const char *dbname, bool missing_ok)
612616
booldb_istemplate;
613617
Relationpgdbrel;
614618
HeapTupletup;
619+
intnotherbackends;
620+
intnpreparedxacts;
615621

616622
/*
617623
* Look up the target database's OID, and get exclusive lock on it. We
@@ -671,11 +677,12 @@ dropdb(const char *dbname, bool missing_ok)
671677
*
672678
* As in CREATE DATABASE, check this after other error conditions.
673679
*/
674-
if (CheckOtherDBBackends(db_id))
680+
if (CountOtherDBBackends(db_id,&notherbackends,&npreparedxacts))
675681
ereport(ERROR,
676682
(errcode(ERRCODE_OBJECT_IN_USE),
677683
errmsg("database \"%s\" is being accessed by other users",
678-
dbname)));
684+
dbname),
685+
errdetail_busy_db(notherbackends,npreparedxacts)));
679686

680687
/*
681688
* Remove the database's tuple from pg_database.
@@ -764,6 +771,8 @@ RenameDatabase(const char *oldname, const char *newname)
764771
Oiddb_id;
765772
HeapTuplenewtup;
766773
Relationrel;
774+
intnotherbackends;
775+
intnpreparedxacts;
767776

768777
/*
769778
* Look up the target database's OID, and get exclusive lock on it. We
@@ -814,11 +823,12 @@ RenameDatabase(const char *oldname, const char *newname)
814823
*
815824
* As in CREATE DATABASE, check this after other error conditions.
816825
*/
817-
if (CheckOtherDBBackends(db_id))
826+
if (CountOtherDBBackends(db_id,&notherbackends,&npreparedxacts))
818827
ereport(ERROR,
819828
(errcode(ERRCODE_OBJECT_IN_USE),
820829
errmsg("database \"%s\" is being accessed by other users",
821-
oldname)));
830+
oldname),
831+
errdetail_busy_db(notherbackends,npreparedxacts)));
822832

823833
/* rename */
824834
newtup=SearchSysCacheCopy(DATABASEOID,
@@ -1400,6 +1410,29 @@ check_db_file_conflict(Oid db_id)
14001410
returnresult;
14011411
}
14021412

1413+
/*
1414+
* Issue a suitable errdetail message for a busy database
1415+
*/
1416+
staticint
1417+
errdetail_busy_db(intnotherbackends,intnpreparedxacts)
1418+
{
1419+
/*
1420+
* We don't worry about singular versus plural here, since the English
1421+
* rules for that don't translate very well. But we can at least avoid
1422+
* the case of zero items.
1423+
*/
1424+
if (notherbackends>0&&npreparedxacts>0)
1425+
errdetail("There are %d other session(s) and %d prepared transaction(s) using the database.",
1426+
notherbackends,npreparedxacts);
1427+
elseif (notherbackends>0)
1428+
errdetail("There are %d other session(s) using the database.",
1429+
notherbackends);
1430+
else
1431+
errdetail("There are %d prepared transaction(s) using the database.",
1432+
npreparedxacts);
1433+
return0;/* just to keep ereport macro happy */
1434+
}
1435+
14031436
/*
14041437
* get_database_oid - given a database name, look up the OID
14051438
*

‎src/backend/storage/ipc/procarray.c

Lines changed: 28 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@
2323
*
2424
*
2525
* IDENTIFICATION
26-
* $PostgreSQL: pgsql/src/backend/storage/ipc/procarray.c,v 1.45 2008/07/11 02:10:13 alvherre Exp $
26+
* $PostgreSQL: pgsql/src/backend/storage/ipc/procarray.c,v 1.46 2008/08/04 18:03:46 tgl Exp $
2727
*
2828
*-------------------------------------------------------------------------
2929
*/
@@ -1177,7 +1177,7 @@ CountUserBackends(Oid roleid)
11771177
}
11781178

11791179
/*
1180-
*CheckOtherDBBackends -- check for other backends running in the given DB
1180+
*CountOtherDBBackends -- check for other backends running in the given DB
11811181
*
11821182
* If there are other backends in the DB, we will wait a maximum of 5 seconds
11831183
* for them to exit. Autovacuum backends are encouraged to exit early by
@@ -1187,6 +1187,8 @@ CountUserBackends(Oid roleid)
11871187
* check whether the current backend uses the given DB, if it's important.
11881188
*
11891189
* Returns TRUE if there are (still) other backends in the DB, FALSE if not.
1190+
* Also, *nbackends and *nprepared are set to the number of other backends
1191+
* and prepared transactions in the DB, respectively.
11901192
*
11911193
* This function is used to interlock DROP DATABASE and related commands
11921194
* against there being any active backends in the target DB --- dropping the
@@ -1198,19 +1200,24 @@ CountUserBackends(Oid roleid)
11981200
* indefinitely.
11991201
*/
12001202
bool
1201-
CheckOtherDBBackends(OiddatabaseId)
1203+
CountOtherDBBackends(OiddatabaseId,int*nbackends,int*nprepared)
12021204
{
12031205
ProcArrayStruct*arrayP=procArray;
1206+
#defineMAXAUTOVACPIDS 10/* max autovacs to SIGTERM per iteration */
1207+
intautovac_pids[MAXAUTOVACPIDS];
12041208
inttries;
12051209

12061210
/* 50 tries with 100ms sleep between tries makes 5 sec total wait */
12071211
for (tries=0;tries<50;tries++)
12081212
{
1213+
intnautovacs=0;
12091214
boolfound= false;
12101215
intindex;
12111216

12121217
CHECK_FOR_INTERRUPTS();
12131218

1219+
*nbackends=*nprepared=0;
1220+
12141221
LWLockAcquire(ProcArrayLock,LW_SHARED);
12151222

12161223
for (index=0;index<arrayP->numProcs;index++)
@@ -1224,38 +1231,32 @@ CheckOtherDBBackends(Oid databaseId)
12241231

12251232
found= true;
12261233

1227-
if (proc->vacuumFlags&PROC_IS_AUTOVACUUM)
1228-
{
1229-
/* an autovacuum --- send it SIGTERM before sleeping */
1230-
intautopid=proc->pid;
1231-
1232-
/*
1233-
* It's a bit awkward to release ProcArrayLock within the
1234-
* loop, but we'd probably better do so before issuing kill().
1235-
* We have no idea what might block kill() inside the
1236-
* kernel...
1237-
*/
1238-
LWLockRelease(ProcArrayLock);
1239-
1240-
(void)kill(autopid,SIGTERM);/* ignore any error */
1241-
1242-
break;
1243-
}
1234+
if (proc->pid==0)
1235+
(*nprepared)++;
12441236
else
12451237
{
1246-
LWLockRelease(ProcArrayLock);
1247-
break;
1238+
(*nbackends)++;
1239+
if ((proc->vacuumFlags&PROC_IS_AUTOVACUUM)&&
1240+
nautovacs<MAXAUTOVACPIDS)
1241+
autovac_pids[nautovacs++]=proc->pid;
12481242
}
12491243
}
12501244

1251-
/* if found is set, we released the lock within the loop body */
1245+
LWLockRelease(ProcArrayLock);
1246+
12521247
if (!found)
1253-
{
1254-
LWLockRelease(ProcArrayLock);
12551248
return false;/* no conflicting backends, so done */
1256-
}
12571249

1258-
/* else sleep and try again */
1250+
/*
1251+
* Send SIGTERM to any conflicting autovacuums before sleeping.
1252+
* We postpone this step until after the loop because we don't
1253+
* want to hold ProcArrayLock while issuing kill().
1254+
* We have no idea what might block kill() inside the kernel...
1255+
*/
1256+
for (index=0;index<nautovacs;index++)
1257+
(void)kill(autovac_pids[index],SIGTERM);/* ignore any error */
1258+
1259+
/* sleep, then try again */
12591260
pg_usleep(100*1000L);/* 100ms */
12601261
}
12611262

‎src/include/storage/procarray.h

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/storage/procarray.h,v 1.22 2008/05/12 20:02:02 alvherre Exp $
10+
* $PostgreSQL: pgsql/src/include/storage/procarray.h,v 1.23 2008/08/04 18:03:46 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -44,7 +44,8 @@ extern VirtualTransactionId *GetCurrentVirtualXIDs(TransactionId limitXmin,
4444
externintCountActiveBackends(void);
4545
externintCountDBBackends(Oiddatabaseid);
4646
externintCountUserBackends(Oidroleid);
47-
externboolCheckOtherDBBackends(OiddatabaseId);
47+
externboolCountOtherDBBackends(OiddatabaseId,
48+
int*nbackends,int*nprepared);
4849

4950
externvoidXidCacheRemoveRunningXids(TransactionIdxid,
5051
intnxids,constTransactionId*xids,

0 commit comments

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