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

Commitd11efe8

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 parente246fd4 commitd11efe8

File tree

18 files changed

+416
-29
lines changed

18 files changed

+416
-29
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2978,7 +2978,8 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
29782978
</para>
29792979
<para>
29802980
Sets maximum number of concurrent connections that can be made
2981-
to this database. -1 means no limit.
2981+
to this database. -1 means no limit, -2 indicates the database is
2982+
invalid.
29822983
</para></entry>
29832984
</row>
29842985

‎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
@@ -1697,6 +1697,20 @@ vac_truncate_clog(TransactionId frozenXID,
16971697
Assert(TransactionIdIsNormal(datfrozenxid));
16981698
Assert(MultiXactIdIsValid(datminmxid));
16991699

1700+
/*
1701+
* If database is in the process of getting dropped, or has been
1702+
* interrupted while doing so, no connections to it are possible
1703+
* anymore. Therefore we don't need to take it into account here.
1704+
* Which is good, because it can't be processed by autovacuum either.
1705+
*/
1706+
if (database_is_invalid_form((Form_pg_database)dbform))
1707+
{
1708+
elog(DEBUG2,
1709+
"skipping invalid database \"%s\" while computing relfrozenxid",
1710+
NameStr(dbform->datname));
1711+
continue;
1712+
}
1713+
17001714
/*
17011715
* If things are working properly, no database should have a
17021716
* 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
@@ -1918,6 +1918,18 @@ get_database_list(void)
19181918
avw_dbase*avdb;
19191919
MemoryContextoldcxt;
19201920

1921+
/*
1922+
* If database has partially been dropped, we can't, nor need to,
1923+
* vacuum it.
1924+
*/
1925+
if (database_is_invalid_form(pgdatabase))
1926+
{
1927+
elog(DEBUG2,
1928+
"autovacuum: skipping invalid database \"%s\"",
1929+
NameStr(pgdatabase->datname));
1930+
continue;
1931+
}
1932+
19211933
/*
19221934
* Allocate our results in the caller's context, not the
19231935
* 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
@@ -982,6 +982,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
982982
if (!bootstrap)
983983
{
984984
HeapTupletuple;
985+
Form_pg_databasedatform;
985986

986987
tuple=GetDatabaseTuple(dbname);
987988
if (!HeapTupleIsValid(tuple)||
@@ -991,6 +992,15 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
991992
(errcode(ERRCODE_UNDEFINED_DATABASE),
992993
errmsg("database \"%s\" does not exist",dbname),
993994
errdetail("It seems to have just been dropped or renamed.")));
995+
996+
datform= (Form_pg_database)GETSTRUCT(tuple);
997+
if (database_is_invalid_form(datform))
998+
{
999+
ereport(FATAL,
1000+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
1001+
errmsg("cannot connect to invalid database \"%s\"",dbname),
1002+
errhint("Use DROP DATABASE to drop invalid databases."));
1003+
}
9941004
}
9951005

9961006
/*

‎src/bin/pg_amcheck/pg_amcheck.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1623,7 +1623,7 @@ compile_database_list(PGconn *conn, SimplePtrList *databases,
16231623
"FROM pg_catalog.pg_database d "
16241624
"LEFT OUTER JOIN exclude_raw e "
16251625
"ON d.datname ~ e.rgx "
1626-
"\nWHERE d.datallowconn "
1626+
"\nWHERE d.datallowconnAND datconnlimit != -2"
16271627
"AND e.pattern_id IS NULL"
16281628
"),"
16291629

‎src/bin/pg_amcheck/t/002_nonesuch.pl

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66

77
use PostgresNode;
88
use TestLib;
9-
use Test::Moretests=>100;
9+
use Test::Moretests=>106;
1010

1111
# Test set-up
1212
my ($node,$port);
@@ -288,6 +288,40 @@
288288
'many unmatched patterns and one matched pattern under --no-strict-names'
289289
);
290290

291+
292+
#########################################
293+
# Test that an invalid / partially dropped database won't be targeted
294+
295+
$node->safe_psql(
296+
'postgres',q(
297+
CREATE DATABASE regression_invalid;
298+
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid';
299+
));
300+
301+
$node->command_checks_all(
302+
[
303+
'pg_amcheck','-d','regression_invalid'
304+
],
305+
1,
306+
[qr/^$/],
307+
[
308+
qr/pg_amcheck: error: no connectable databases to check matching "regression_invalid"/,
309+
],
310+
'checking handling of invalid database');
311+
312+
$node->command_checks_all(
313+
[
314+
'pg_amcheck','-d','postgres',
315+
'-t','regression_invalid.public.foo',
316+
],
317+
1,
318+
[qr/^$/],
319+
[
320+
qr/pg_amcheck: error: no connectable databases to check matching "regression_invalid.public.foo"/,
321+
],
322+
'checking handling of object in invalid database');
323+
324+
291325
#########################################
292326
# Test checking otherwise existent objects but in databases where they do not exist
293327

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1326,7 +1326,7 @@ dropDBs(PGconn *conn)
13261326
res=executeQuery(conn,
13271327
"SELECT datname "
13281328
"FROM pg_database d "
1329-
"WHERE datallowconn "
1329+
"WHERE datallowconnAND datconnlimit != -2"
13301330
"ORDER BY datname");
13311331

13321332
if (PQntuples(res)>0)
@@ -1490,7 +1490,7 @@ dumpDatabases(PGconn *conn)
14901490
res=executeQuery(conn,
14911491
"SELECT datname "
14921492
"FROM pg_database d "
1493-
"WHERE datallowconn "
1493+
"WHERE datallowconnAND datconnlimit != -2"
14941494
"ORDER BY (datname <> 'template1'), datname");
14951495

14961496
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
@@ -1449,6 +1449,17 @@
14491449
},
14501450
},
14511451

1452+
'CREATE DATABASE regression_invalid...'=> {
1453+
create_order=> 1,
1454+
create_sql=>q(
1455+
CREATE DATABASE regression_invalid;
1456+
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid'),
1457+
regexp=>qr/^CREATE DATABASE regression_invalid/m,
1458+
not_like=> {
1459+
pg_dumpall_dbprivs=> 1,
1460+
},
1461+
},
1462+
14521463
'CREATE ACCESS METHOD gist2'=> {
14531464
create_order=> 52,
14541465
create_sql=>
@@ -3642,7 +3653,7 @@
36423653
36433654
# Start with number of command_fails_like()*2 tests below (each
36443655
# command_fails_like is actually 2 tests) + number of command_ok()*3
3645-
my$num_tests =33;
3656+
my$num_tests =35;
36463657
36473658
foreach my$run (sort keys%pgdump_runs)
36483659
{
@@ -3784,6 +3795,14 @@
37843795
qr/pg_dump: error: connection to server .* failed: FATAL: database "qqq" does not exist/,
37853796
'connecting to a non-existent database');
37863797
3798+
#########################################
3799+
# Test connecting to an invalid database
3800+
3801+
command_fails_like(
3802+
[ 'pg_dump', '-p', "$port", '-d', 'regression_invalid' ],
3803+
qr/pg_dump: error: connection to server .* failed: FATAL: cannot connect to invalid database "regression_invalid"/,
3804+
'connecting to an invalid database');
3805+
37873806
#########################################
37883807
# Test connecting with an unprivileged user
37893808

‎src/bin/scripts/clusterdb.c

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

241241
conn=connectMaintenanceDatabase(cparams,progname,echo);
242-
result=executeQuery(conn,"SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",echo);
242+
result=executeQuery(conn,
243+
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
244+
echo);
243245
PQfinish(conn);
244246

245247
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
@@ -762,7 +762,9 @@ reindex_all_databases(ConnParams *cparams,
762762
inti;
763763

764764
conn=connectMaintenanceDatabase(cparams,progname,echo);
765-
result=executeQuery(conn,"SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",echo);
765+
result=executeQuery(conn,
766+
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
767+
echo);
766768
PQfinish(conn);
767769

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

0 commit comments

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