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

Commit1379fd5

Browse files
author
Amit Kapila
committed
Introduce the 'force' option for the Drop Database command.
This new option terminates the other sessions connected to the targetdatabase and then drop it. To terminate other sessions, the current usermust have desired permissions (same as pg_terminate_backend()). We don'tallow to terminate the sessions if prepared transactions, active logicalreplication slots or subscriptions are present in the target database.Author: Pavel Stehule with changes by meReviewed-by: Dilip Kumar, Vignesh C, Ibrar Ahmed, Anthony Nowocien,Ryan Lambert and Amit KapilaDiscussion:https://postgr.es/m/CAP_rwwmLJJbn70vLOZFpxGw3XD7nLB_7+NKz46H5EOO2k5H7OQ@mail.gmail.com
1 parent112caf9 commit1379fd5

File tree

13 files changed

+248
-14
lines changed

13 files changed

+248
-14
lines changed

‎doc/src/sgml/ref/drop_database.sgml‎

Lines changed: 29 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,11 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
24+
DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
25+
26+
<phrase>where <replaceable class="parameter">option</replaceable> can be:</phrase>
27+
28+
FORCE
2529
</synopsis>
2630
</refsynopsisdiv>
2731

@@ -32,9 +36,11 @@ DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3236
<command>DROP DATABASE</command> drops a database. It removes the
3337
catalog entries for the database and deletes the directory
3438
containing the data. It can only be executed by the database owner.
35-
Also, it cannot be executed while you or anyone else are connected
36-
to the target database. (Connect to <literal>postgres</literal> or any
37-
other database to issue this command.)
39+
It cannot be executed while you are connected to the target database.
40+
(Connect to <literal>postgres</literal> or any other database to issue this
41+
command.)
42+
Also, if anyone else is connected to the target database, this command will
43+
fail unless you use the <literal>FORCE</literal> option described below.
3844
</para>
3945

4046
<para>
@@ -64,6 +70,25 @@ DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
6470
</para>
6571
</listitem>
6672
</varlistentry>
73+
74+
<varlistentry>
75+
<term><literal>FORCE</literal></term>
76+
<listitem>
77+
<para>
78+
Attempt to terminate all existing connections to the target database.
79+
It doesn't terminate if prepared transactions, active logical replication
80+
slots or subscriptions are present in the target database.
81+
</para>
82+
<para>
83+
This will fail if the current user has no permissions to terminate other
84+
connections. Required permissions are the same as with
85+
<literal>pg_terminate_backend</literal>, described in
86+
<xref linkend="functions-admin-signal"/>. This will also fail if we
87+
are not able to terminate connections.
88+
</para>
89+
</listitem>
90+
</varlistentry>
91+
6792
</variablelist>
6893
</refsect1>
6994

‎src/backend/commands/dbcommands.c‎

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -810,7 +810,7 @@ createdb_failure_callback(int code, Datum arg)
810810
* DROP DATABASE
811811
*/
812812
void
813-
dropdb(constchar*dbname,boolmissing_ok)
813+
dropdb(constchar*dbname,boolmissing_ok,boolforce)
814814
{
815815
Oiddb_id;
816816
booldb_istemplate;
@@ -910,6 +910,14 @@ dropdb(const char *dbname, bool missing_ok)
910910
"There are %d subscriptions.",
911911
nsubscriptions,nsubscriptions)));
912912

913+
914+
/*
915+
* Attempt to terminate all existing connections to the target database if
916+
* the user has requested to do so.
917+
*/
918+
if (force)
919+
TerminateOtherDBBackends(db_id);
920+
913921
/*
914922
* Check for other backends in the target database. (Because we hold the
915923
* database lock, no new ones can start after this.)
@@ -1430,6 +1438,30 @@ movedb_failure_callback(int code, Datum arg)
14301438
(void)rmtree(dstpath, true);
14311439
}
14321440

1441+
/*
1442+
* Process options and call dropdb function.
1443+
*/
1444+
void
1445+
DropDatabase(ParseState*pstate,DropdbStmt*stmt)
1446+
{
1447+
boolforce= false;
1448+
ListCell*lc;
1449+
1450+
foreach(lc,stmt->options)
1451+
{
1452+
DefElem*opt= (DefElem*)lfirst(lc);
1453+
1454+
if (strcmp(opt->defname,"force")==0)
1455+
force= true;
1456+
else
1457+
ereport(ERROR,
1458+
(errcode(ERRCODE_SYNTAX_ERROR),
1459+
errmsg("unrecognized DROP DATABASE option \"%s\"",opt->defname),
1460+
parser_errposition(pstate,opt->location)));
1461+
}
1462+
1463+
dropdb(stmt->dbname,stmt->missing_ok,force);
1464+
}
14331465

14341466
/*
14351467
* ALTER DATABASE name ...

‎src/backend/nodes/copyfuncs.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3868,6 +3868,7 @@ _copyDropdbStmt(const DropdbStmt *from)
38683868

38693869
COPY_STRING_FIELD(dbname);
38703870
COPY_SCALAR_FIELD(missing_ok);
3871+
COPY_NODE_FIELD(options);
38713872

38723873
returnnewnode;
38733874
}

‎src/backend/nodes/equalfuncs.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1676,6 +1676,7 @@ _equalDropdbStmt(const DropdbStmt *a, const DropdbStmt *b)
16761676
{
16771677
COMPARE_STRING_FIELD(dbname);
16781678
COMPARE_SCALAR_FIELD(missing_ok);
1679+
COMPARE_NODE_FIELD(options);
16791680

16801681
return true;
16811682
}

‎src/backend/parser/gram.y‎

Lines changed: 42 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -310,6 +310,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
310310
%type<defelt>vac_analyze_option_elem
311311
%type<list>vac_analyze_option_list
312312
%type<node>vac_analyze_option_arg
313+
%type<defelt>drop_option
313314
%type<boolean>opt_or_replace
314315
opt_grant_grant_optionopt_grant_admin_option
315316
opt_nowaitopt_if_existsopt_with_data
@@ -406,6 +407,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
406407
TriggerTransitionsTriggerReferencing
407408
publication_name_list
408409
vacuum_relation_listopt_vacuum_relation_list
410+
drop_option_list
409411

410412
%type<list>group_by_list
411413
%type<node>group_by_itemempty_grouping_setrollup_clausecube_clause
@@ -10213,7 +10215,7 @@ AlterDatabaseSetStmt:
1021310215

1021410216
/*****************************************************************************
1021510217
*
10216-
*DROP DATABASE [ IF EXISTS ]
10218+
*DROP DATABASE [ IF EXISTS ] dbname [ [ WITH ] ( options ) ]
1021710219
*
1021810220
* This is implicitly CASCADE, no need for drop behavior
1021910221
*****************************************************************************/
@@ -10223,17 +10225,56 @@ DropdbStmt: DROP DATABASE database_name
1022310225
DropdbStmt *n = makeNode(DropdbStmt);
1022410226
n->dbname =$3;
1022510227
n->missing_ok =false;
10228+
n->options =NULL;
1022610229
$$ = (Node *)n;
1022710230
}
1022810231
|DROPDATABASEIF_PEXISTSdatabase_name
1022910232
{
1023010233
DropdbStmt *n = makeNode(DropdbStmt);
1023110234
n->dbname =$5;
1023210235
n->missing_ok =true;
10236+
n->options =NULL;
1023310237
$$ = (Node *)n;
1023410238
}
10239+
|DROPDATABASEdatabase_nameopt_with'('drop_option_list')'
10240+
{
10241+
DropdbStmt *n = makeNode(DropdbStmt);
10242+
n->dbname =$3;
10243+
n->missing_ok =false;
10244+
n->options =$6;
10245+
$$ = (Node *)n;
10246+
}
10247+
|DROPDATABASEIF_PEXISTSdatabase_nameopt_with'('drop_option_list')'
10248+
{
10249+
DropdbStmt *n = makeNode(DropdbStmt);
10250+
n->dbname =$5;
10251+
n->missing_ok =true;
10252+
n->options =$8;
10253+
$$ = (Node *)n;
10254+
}
10255+
;
10256+
10257+
drop_option_list:
10258+
drop_option
10259+
{
10260+
$$ = list_make1((Node *)$1);
10261+
}
10262+
|drop_option_list','drop_option
10263+
{
10264+
$$ = lappend($1, (Node *)$3);
10265+
}
1023510266
;
1023610267

10268+
/*
10269+
* Currently only the FORCE option is supported, but the syntax is designed
10270+
* to be extensible so that we can add more options in the future if required.
10271+
*/
10272+
drop_option:
10273+
FORCE
10274+
{
10275+
$$ = makeDefElem("force",NULL,@1);
10276+
}
10277+
;
1023710278

1023810279
/*****************************************************************************
1023910280
*

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

Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,8 @@
5252
#include"access/xact.h"
5353
#include"access/xlog.h"
5454
#include"catalog/catalog.h"
55+
#include"catalog/pg_authid.h"
56+
#include"commands/dbcommands.h"
5557
#include"miscadmin.h"
5658
#include"pgstat.h"
5759
#include"storage/proc.h"
@@ -2970,6 +2972,118 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
29702972
return true;/* timed out, still conflicts */
29712973
}
29722974

2975+
/*
2976+
* Terminate existing connections to the specified database. This routine
2977+
* is used by the DROP DATABASE command when user has asked to forcefully
2978+
* drop the database.
2979+
*
2980+
* The current backend is always ignored; it is caller's responsibility to
2981+
* check whether the current backend uses the given DB, if it's important.
2982+
*
2983+
* It doesn't allow to terminate the connections even if there is a one
2984+
* backend with the prepared transaction in the target database.
2985+
*/
2986+
void
2987+
TerminateOtherDBBackends(OiddatabaseId)
2988+
{
2989+
ProcArrayStruct*arrayP=procArray;
2990+
List*pids=NIL;
2991+
intnprepared=0;
2992+
inti;
2993+
2994+
LWLockAcquire(ProcArrayLock,LW_SHARED);
2995+
2996+
for (i=0;i<procArray->numProcs;i++)
2997+
{
2998+
intpgprocno=arrayP->pgprocnos[i];
2999+
PGPROC*proc=&allProcs[pgprocno];
3000+
3001+
if (proc->databaseId!=databaseId)
3002+
continue;
3003+
if (proc==MyProc)
3004+
continue;
3005+
3006+
if (proc->pid!=0)
3007+
pids=lappend_int(pids,proc->pid);
3008+
else
3009+
nprepared++;
3010+
}
3011+
3012+
LWLockRelease(ProcArrayLock);
3013+
3014+
if (nprepared>0)
3015+
ereport(ERROR,
3016+
(errcode(ERRCODE_OBJECT_IN_USE),
3017+
errmsg("database \"%s\" is being used by prepared transaction",
3018+
get_database_name(databaseId)),
3019+
errdetail_plural("There is %d prepared transaction using the database.",
3020+
"There are %d prepared transactions using the database.",
3021+
nprepared,
3022+
nprepared)));
3023+
3024+
if (pids)
3025+
{
3026+
ListCell*lc;
3027+
3028+
/*
3029+
* Check whether we have the necessary rights to terminate other
3030+
* sessions. We don't terminate any session untill we ensure that we
3031+
* have rights on all the sessions to be terminated. These checks are
3032+
* the same as we do in pg_terminate_backend.
3033+
*
3034+
* In this case we don't raise some warnings - like "PID %d is not a
3035+
* PostgreSQL server process", because for us already finished session
3036+
* is not a problem.
3037+
*/
3038+
foreach(lc,pids)
3039+
{
3040+
intpid=lfirst_int(lc);
3041+
PGPROC*proc=BackendPidGetProc(pid);
3042+
3043+
if (proc!=NULL)
3044+
{
3045+
/* Only allow superusers to signal superuser-owned backends. */
3046+
if (superuser_arg(proc->roleId)&& !superuser())
3047+
ereport(ERROR,
3048+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
3049+
(errmsg("must be a superuser to terminate superuser process"))));
3050+
3051+
/* Users can signal backends they have role membership in. */
3052+
if (!has_privs_of_role(GetUserId(),proc->roleId)&&
3053+
!has_privs_of_role(GetUserId(),DEFAULT_ROLE_SIGNAL_BACKENDID))
3054+
ereport(ERROR,
3055+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
3056+
(errmsg("must be a member of the role whose process is being terminated or member of pg_signal_backend"))));
3057+
}
3058+
}
3059+
3060+
/*
3061+
* There's a race condition here: once we release the ProcArrayLock,
3062+
* it's possible for the session to exit before we issue kill. That
3063+
* race condition possibility seems too unlikely to worry about. See
3064+
* pg_signal_backend.
3065+
*/
3066+
foreach(lc,pids)
3067+
{
3068+
intpid=lfirst_int(lc);
3069+
PGPROC*proc=BackendPidGetProc(pid);
3070+
3071+
if (proc!=NULL)
3072+
{
3073+
/*
3074+
* If we have setsid(), signal the backend's whole process
3075+
* group
3076+
*/
3077+
#ifdefHAVE_SETSID
3078+
(void)kill(-pid,SIGTERM);
3079+
#else
3080+
(void)kill(pid,SIGTERM);
3081+
#endif
3082+
}
3083+
}
3084+
}
3085+
}
3086+
29733087
/*
29743088
* ProcArraySetReplicationSlotXmin
29753089
*

‎src/backend/tcop/utility.c‎

Lines changed: 3 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -595,13 +595,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
595595
break;
596596

597597
caseT_DropdbStmt:
598-
{
599-
DropdbStmt*stmt= (DropdbStmt*)parsetree;
600-
601-
/* no event triggers for global objects */
602-
PreventInTransactionBlock(isTopLevel,"DROP DATABASE");
603-
dropdb(stmt->dbname,stmt->missing_ok);
604-
}
598+
/* no event triggers for global objects */
599+
PreventInTransactionBlock(isTopLevel,"DROP DATABASE");
600+
DropDatabase(pstate, (DropdbStmt*)parsetree);
605601
break;
606602

607603
/* Query-level asynchronous notification */

‎src/bin/psql/tab-complete.c‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2844,6 +2844,10 @@ psql_completion(const char *text, int start, int end)
28442844
COMPLETE_WITH_FUNCTION_ARG(prev2_wd);
28452845
elseif (Matches("DROP","FOREIGN"))
28462846
COMPLETE_WITH("DATA WRAPPER","TABLE");
2847+
elseif (Matches("DROP","DATABASE",MatchAny))
2848+
COMPLETE_WITH("WITH (");
2849+
elseif (HeadMatches("DROP","DATABASE")&& (ends_with(prev_wd,'(')))
2850+
COMPLETE_WITH("FORCE");
28472851

28482852
/* DROP INDEX */
28492853
elseif (Matches("DROP","INDEX"))

‎src/include/commands/dbcommands.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,8 @@
2020
#include"nodes/parsenodes.h"
2121

2222
externOidcreatedb(ParseState*pstate,constCreatedbStmt*stmt);
23-
externvoiddropdb(constchar*dbname,boolmissing_ok);
23+
externvoiddropdb(constchar*dbname,boolmissing_ok,boolforce);
24+
externvoidDropDatabase(ParseState*pstate,DropdbStmt*stmt);
2425
externObjectAddressRenameDatabase(constchar*oldname,constchar*newname);
2526
externOidAlterDatabase(ParseState*pstate,AlterDatabaseStmt*stmt,boolisTopLevel);
2627
externOidAlterDatabaseSet(AlterDatabaseSetStmt*stmt);

‎src/include/nodes/parsenodes.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3145,6 +3145,7 @@ typedef struct DropdbStmt
31453145
NodeTagtype;
31463146
char*dbname;/* database to drop */
31473147
boolmissing_ok;/* skip error if db is missing? */
3148+
List*options;/* currently only FORCE is supported */
31483149
}DropdbStmt;
31493150

31503151
/* ----------------------

0 commit comments

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