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

Commit81ce000

Browse files
committed
Handle DROP DATABASE getting interrupted
Until now, when DROP DATABASE got interrupted in the wrong moment, the removalof the pg_database row would also roll back, even though some irreversiblesteps have already been taken. E.g. DropDatabaseBuffers() might have thrownout dirty buffers, or files could have been unlinked. But we continued toallow connections to such a corrupted database.To fix this, mark databases invalid with an in-place update, just beforestarting to perform irreversible steps. As we can't add a new column in theback branches, we use pg_database.datconnlimit = -2 for this purpose.An invalid database cannot be connected to anymore, but can still bedropped.Unfortunately we can't easily add output to psql's \l to indicate that somedatabase is invalid, it doesn't fit in any of the existing columns.Add tests verifying that a interrupted DROP DATABASE is handled correctly inthe backend and in various tools.Reported-by: Evgeny Morozov <postgresql3@realityexists.net>Author: Andres Freund <andres@anarazel.de>Reviewed-by: Daniel Gustafsson <daniel@yesql.se>Reviewed-by: Thomas Munro <thomas.munro@gmail.com>Discussion:https://postgr.es/m/20230509004637.cgvmfwrbht7xm7p6@awork3.anarazel.deDiscussion:https://postgr.es/m/20230314174521.74jl6ffqsee5mtug@awork3.anarazel.deBackpatch: 11-, bug present in all supported versions
1 parent53336e8 commit81ce000

File tree

16 files changed

+380
-27
lines changed

16 files changed

+380
-27
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2951,7 +2951,8 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
29512951
</para>
29522952
<para>
29532953
Sets maximum number of concurrent connections that can be made
2954-
to this database. -1 means no limit.
2954+
to this database. -1 means no limit, -2 indicates the database is
2955+
invalid.
29552956
</para></entry>
29562957
</row>
29572958

‎src/backend/commands/dbcommands.c

Lines changed: 85 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -140,7 +140,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
140140
intencoding=-1;
141141
booldbistemplate= false;
142142
booldballowconnections= true;
143-
intdbconnlimit=-1;
143+
intdbconnlimit=DATCONNLIMIT_UNLIMITED;
144144
intnotherbackends;
145145
intnpreparedxacts;
146146
createdb_failure_paramsfparms;
@@ -309,7 +309,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
309309
if (dconnlimit&&dconnlimit->arg)
310310
{
311311
dbconnlimit=defGetInt32(dconnlimit);
312-
if (dbconnlimit<-1)
312+
if (dbconnlimit<DATCONNLIMIT_UNLIMITED)
313313
ereport(ERROR,
314314
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
315315
errmsg("invalid connection limit: %d",dbconnlimit)));
@@ -357,6 +357,16 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
357357
errmsg("template database \"%s\" does not exist",
358358
dbtemplate)));
359359

360+
/*
361+
* If the source database was in the process of being dropped, we can't
362+
* use it as a template.
363+
*/
364+
if (database_is_invalid_oid(src_dboid))
365+
ereport(ERROR,
366+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
367+
errmsg("cannot use invalid database \"%s\" as template",dbtemplate),
368+
errhint("Use DROP DATABASE to drop invalid databases."));
369+
360370
/*
361371
* Permission check: to copy a DB that's not marked datistemplate, you
362372
* must be superuser or the owner thereof.
@@ -817,6 +827,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
817827
booldb_istemplate;
818828
Relationpgdbrel;
819829
HeapTupletup;
830+
Form_pg_databasedatform;
820831
intnotherbackends;
821832
intnpreparedxacts;
822833
intnslots,
@@ -932,17 +943,6 @@ dropdb(const char *dbname, bool missing_ok, bool force)
932943
dbname),
933944
errdetail_busy_db(notherbackends,npreparedxacts)));
934945

935-
/*
936-
* Remove the database's tuple from pg_database.
937-
*/
938-
tup=SearchSysCache1(DATABASEOID,ObjectIdGetDatum(db_id));
939-
if (!HeapTupleIsValid(tup))
940-
elog(ERROR,"cache lookup failed for database %u",db_id);
941-
942-
CatalogTupleDelete(pgdbrel,&tup->t_self);
943-
944-
ReleaseSysCache(tup);
945-
946946
/*
947947
* Delete any comments or security labels associated with the database.
948948
*/
@@ -959,6 +959,32 @@ dropdb(const char *dbname, bool missing_ok, bool force)
959959
*/
960960
dropDatabaseDependencies(db_id);
961961

962+
tup=SearchSysCacheCopy1(DATABASEOID,ObjectIdGetDatum(db_id));
963+
if (!HeapTupleIsValid(tup))
964+
elog(ERROR,"cache lookup failed for database %u",db_id);
965+
datform= (Form_pg_database)GETSTRUCT(tup);
966+
967+
/*
968+
* Except for the deletion of the catalog row, subsequent actions are not
969+
* transactional (consider DropDatabaseBuffers() discarding modified
970+
* buffers). But we might crash or get interrupted below. To prevent
971+
* accesses to a database with invalid contents, mark the database as
972+
* invalid using an in-place update.
973+
*
974+
* We need to flush the WAL before continuing, to guarantee the
975+
* modification is durable before performing irreversible filesystem
976+
* operations.
977+
*/
978+
datform->datconnlimit=DATCONNLIMIT_INVALID_DB;
979+
heap_inplace_update(pgdbrel,tup);
980+
XLogFlush(XactLastRecEnd);
981+
982+
/*
983+
* Also delete the tuple - transactionally. If this transaction commits,
984+
* the row will be gone, but if we fail, dropdb() can be invoked again.
985+
*/
986+
CatalogTupleDelete(pgdbrel,&tup->t_self);
987+
962988
/*
963989
* Drop db-specific replication slots.
964990
*/
@@ -1481,7 +1507,7 @@ AlterDatabase(ParseState *pstate, AlterDatabaseStmt *stmt, bool isTopLevel)
14811507
ListCell*option;
14821508
booldbistemplate= false;
14831509
booldballowconnections= true;
1484-
intdbconnlimit=-1;
1510+
intdbconnlimit=DATCONNLIMIT_UNLIMITED;
14851511
DefElem*distemplate=NULL;
14861512
DefElem*dallowconnections=NULL;
14871513
DefElem*dconnlimit=NULL;
@@ -1564,7 +1590,7 @@ AlterDatabase(ParseState *pstate, AlterDatabaseStmt *stmt, bool isTopLevel)
15641590
if (dconnlimit&&dconnlimit->arg)
15651591
{
15661592
dbconnlimit=defGetInt32(dconnlimit);
1567-
if (dbconnlimit<-1)
1593+
if (dbconnlimit<DATCONNLIMIT_UNLIMITED)
15681594
ereport(ERROR,
15691595
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
15701596
errmsg("invalid connection limit: %d",dbconnlimit)));
@@ -1591,6 +1617,14 @@ AlterDatabase(ParseState *pstate, AlterDatabaseStmt *stmt, bool isTopLevel)
15911617
datform= (Form_pg_database)GETSTRUCT(tuple);
15921618
dboid=datform->oid;
15931619

1620+
if (database_is_invalid_form(datform))
1621+
{
1622+
ereport(FATAL,
1623+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1624+
errmsg("cannot alter invalid database \"%s\"",stmt->dbname),
1625+
errhint("Use DROP DATABASE to drop invalid databases."));
1626+
}
1627+
15941628
if (!pg_database_ownercheck(dboid,GetUserId()))
15951629
aclcheck_error(ACLCHECK_NOT_OWNER,OBJECT_DATABASE,
15961630
stmt->dbname);
@@ -2170,6 +2204,42 @@ get_database_name(Oid dbid)
21702204
returnresult;
21712205
}
21722206

2207+
2208+
/*
2209+
* While dropping a database the pg_database row is marked invalid, but the
2210+
* catalog contents still exist. Connections to such a database are not
2211+
* allowed.
2212+
*/
2213+
bool
2214+
database_is_invalid_form(Form_pg_databasedatform)
2215+
{
2216+
returndatform->datconnlimit==DATCONNLIMIT_INVALID_DB;
2217+
}
2218+
2219+
2220+
/*
2221+
* Convenience wrapper around database_is_invalid_form()
2222+
*/
2223+
bool
2224+
database_is_invalid_oid(Oiddboid)
2225+
{
2226+
HeapTupledbtup;
2227+
Form_pg_databasedbform;
2228+
boolinvalid;
2229+
2230+
dbtup=SearchSysCache1(DATABASEOID,ObjectIdGetDatum(dboid));
2231+
if (!HeapTupleIsValid(dbtup))
2232+
elog(ERROR,"cache lookup failed for database %u",dboid);
2233+
dbform= (Form_pg_database)GETSTRUCT(dbtup);
2234+
2235+
invalid=database_is_invalid_form(dbform);
2236+
2237+
ReleaseSysCache(dbtup);
2238+
2239+
returninvalid;
2240+
}
2241+
2242+
21732243
/*
21742244
* recovery_create_dbdir()
21752245
*

‎src/backend/commands/vacuum.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1596,6 +1596,20 @@ vac_truncate_clog(TransactionId frozenXID,
15961596
Assert(TransactionIdIsNormal(datfrozenxid));
15971597
Assert(MultiXactIdIsValid(datminmxid));
15981598

1599+
/*
1600+
* If database is in the process of getting dropped, or has been
1601+
* interrupted while doing so, no connections to it are possible
1602+
* anymore. Therefore we don't need to take it into account here.
1603+
* Which is good, because it can't be processed by autovacuum either.
1604+
*/
1605+
if (database_is_invalid_form((Form_pg_database)dbform))
1606+
{
1607+
elog(DEBUG2,
1608+
"skipping invalid database \"%s\" while computing relfrozenxid",
1609+
NameStr(dbform->datname));
1610+
continue;
1611+
}
1612+
15991613
/*
16001614
* If things are working properly, no database should have a
16011615
* datfrozenxid or datminmxid that is "in the future". However, such

‎src/backend/postmaster/autovacuum.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1890,6 +1890,18 @@ get_database_list(void)
18901890
avw_dbase*avdb;
18911891
MemoryContextoldcxt;
18921892

1893+
/*
1894+
* If database has partially been dropped, we can't, nor need to,
1895+
* vacuum it.
1896+
*/
1897+
if (database_is_invalid_form(pgdatabase))
1898+
{
1899+
elog(DEBUG2,
1900+
"autovacuum: skipping invalid database \"%s\"",
1901+
NameStr(pgdatabase->datname));
1902+
continue;
1903+
}
1904+
18931905
/*
18941906
* Allocate our results in the caller's context, not the
18951907
* transaction's. We do this inside the loop, and restore the original

‎src/backend/utils/init/postinit.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -972,6 +972,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
972972
if (!bootstrap)
973973
{
974974
HeapTupletuple;
975+
Form_pg_databasedatform;
975976

976977
tuple=GetDatabaseTuple(dbname);
977978
if (!HeapTupleIsValid(tuple)||
@@ -981,6 +982,15 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
981982
(errcode(ERRCODE_UNDEFINED_DATABASE),
982983
errmsg("database \"%s\" does not exist",dbname),
983984
errdetail("It seems to have just been dropped or renamed.")));
985+
986+
datform= (Form_pg_database)GETSTRUCT(tuple);
987+
if (database_is_invalid_form(datform))
988+
{
989+
ereport(FATAL,
990+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
991+
errmsg("cannot connect to invalid database \"%s\"",dbname),
992+
errhint("Use DROP DATABASE to drop invalid databases."));
993+
}
984994
}
985995

986996
/*

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1320,7 +1320,7 @@ dropDBs(PGconn *conn)
13201320
res=executeQuery(conn,
13211321
"SELECT datname "
13221322
"FROM pg_database d "
1323-
"WHERE datallowconn "
1323+
"WHERE datallowconnAND datconnlimit != -2"
13241324
"ORDER BY datname");
13251325

13261326
if (PQntuples(res)>0)
@@ -1473,7 +1473,7 @@ dumpDatabases(PGconn *conn)
14731473
res=executeQuery(conn,
14741474
"SELECT datname "
14751475
"FROM pg_database d "
1476-
"WHERE datallowconn "
1476+
"WHERE datallowconnAND datconnlimit != -2"
14771477
"ORDER BY (datname <> 'template1'), datname");
14781478

14791479
if (PQntuples(res)>0)

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1430,6 +1430,17 @@
14301430
},
14311431
},
14321432

1433+
'CREATE DATABASE regression_invalid...'=> {
1434+
create_order=> 1,
1435+
create_sql=>q(
1436+
CREATE DATABASE regression_invalid;
1437+
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid'),
1438+
regexp=>qr/^CREATE DATABASE regression_invalid/m,
1439+
not_like=> {
1440+
pg_dumpall_dbprivs=> 1,
1441+
},
1442+
},
1443+
14331444
'CREATE ACCESS METHOD gist2'=> {
14341445
create_order=> 52,
14351446
create_sql=>
@@ -3521,7 +3532,7 @@
35213532
35223533
# Start with number of command_fails_like()*2 tests below (each
35233534
# command_fails_like is actually 2 tests)
3524-
my$num_tests =12;
3535+
my$num_tests =14;
35253536
35263537
foreach my$run (sort keys%pgdump_runs)
35273538
{
@@ -3649,6 +3660,14 @@
36493660
qr/\Qpg_dump: error: connection to database "qqq" failed: FATAL: database "qqq" does not exist\E/,
36503661
'connecting to a non-existent database');
36513662
3663+
#########################################
3664+
# Test connecting to an invalid database
3665+
3666+
command_fails_like(
3667+
[ 'pg_dump', '-p', "$port", '-d', 'regression_invalid' ],
3668+
qr/pg_dump: error: connection to database .* failed: FATAL: cannot connect to invalid database "regression_invalid"/,
3669+
'connecting to an invalid database');
3670+
36523671
#########################################
36533672
# Test connecting with an unprivileged user
36543673

‎src/bin/scripts/clusterdb.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -237,7 +237,9 @@ cluster_all_databases(ConnParams *cparams, const char *progname,
237237
inti;
238238

239239
conn=connectMaintenanceDatabase(cparams,progname,echo);
240-
result=executeQuery(conn,"SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",echo);
240+
result=executeQuery(conn,
241+
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
242+
echo);
241243
PQfinish(conn);
242244

243245
for (i=0;i<PQntuples(result);i++)

‎src/bin/scripts/reindexdb.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -720,7 +720,9 @@ reindex_all_databases(ConnParams *cparams,
720720
inti;
721721

722722
conn=connectMaintenanceDatabase(cparams,progname,echo);
723-
result=executeQuery(conn,"SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",echo);
723+
result=executeQuery(conn,
724+
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
725+
echo);
724726
PQfinish(conn);
725727

726728
for (i=0;i<PQntuples(result);i++)

‎src/bin/scripts/t/011_clusterdb_all.pl

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

44
use PostgresNode;
55
use TestLib;
6-
use Test::Moretests=>2;
6+
use Test::Moretests=>4;
77

88
my$node = get_new_node('main');
99
$node->init;
@@ -17,3 +17,16 @@
1717
['clusterdb','-a' ],
1818
qr/statement: CLUSTER.*statement: CLUSTER/s,
1919
'cluster all databases');
20+
21+
$node->safe_psql(
22+
'postgres',q(
23+
CREATE DATABASE regression_invalid;
24+
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid';
25+
));
26+
$node->command_ok(['clusterdb','-a' ],
27+
'invalid database not targeted by clusterdb -a');
28+
29+
# Doesn't quite belong here, but don't want to waste time by creating an
30+
# invalid database in 010_clusterdb.pl as well.
31+
$node->command_fails(['clusterdb','-d','regression_invalid'],
32+
'clusterdb cannot target invalid database');

‎src/bin/scripts/t/050_dropdb.pl

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

44
use PostgresNode;
55
use TestLib;
6-
use Test::Moretests=>13;
6+
use Test::Moretests=>14;
77

88
program_help_ok('dropdb');
99
program_version_ok('dropdb');
@@ -27,3 +27,12 @@
2727

2828
$node->command_fails(['dropdb','nonexistent' ],
2929
'fails with nonexistent database');
30+
31+
# check that invalid database can be dropped with dropdb
32+
$node->safe_psql(
33+
'postgres',q(
34+
CREATE DATABASE regression_invalid;
35+
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid';
36+
));
37+
$node->command_ok(['dropdb','regression_invalid' ],
38+
'invalid database can be dropped');

‎src/bin/scripts/t/091_reindexdb_all.pl

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
use warnings;
33

44
use PostgresNode;
5-
use Test::Moretests=>2;
5+
use Test::Moretests=>4;
66

77
my$node = get_new_node('main');
88
$node->init;
@@ -14,3 +14,16 @@
1414
['reindexdb','-a' ],
1515
qr/statement: REINDEX.*statement: REINDEX/s,
1616
'reindex all databases');
17+
18+
$node->safe_psql(
19+
'postgres',q(
20+
CREATE DATABASE regression_invalid;
21+
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid';
22+
));
23+
$node->command_ok(['reindexdb','-a' ],
24+
'invalid database not targeted by reindexdb -a');
25+
26+
# Doesn't quite belong here, but don't want to waste time by creating an
27+
# invalid database in 090_reindexdb.pl as well.
28+
$node->command_fails(['reindexdb','-d','regression_invalid'],
29+
'reindexdb cannot target invalid database');

0 commit comments

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