From: Andres Freund <andres@anarazel.de>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Handle DROP DATABASE getting interrupted
Date: Thu, 13 Jul 2023 20:29:40 +0000
Message-ID: <E1qK2wW-000DmD-1M@gemulon.postgresql.org> (raw)
Handle DROP DATABASE getting interrupted
Until now, when DROP DATABASE got interrupted in the wrong moment, the removal
of the pg_database row would also roll back, even though some irreversible
steps have already been taken. E.g. DropDatabaseBuffers() might have thrown
out dirty buffers, or files could have been unlinked. But we continued to
allow connections to such a corrupted database.
To fix this, mark databases invalid with an in-place update, just before
starting to perform irreversible steps. As we can't add a new column in the
back branches, we use pg_database.datconnlimit = -2 for this purpose.
An invalid database cannot be connected to anymore, but can still be
dropped.
Unfortunately we can't easily add output to psql's \l to indicate that some
database is invalid, it doesn't fit in any of the existing columns.
Add tests verifying that a interrupted DROP DATABASE is handled correctly in
the 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.de
Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5mtug@awork3.anarazel.de
Backpatch: 11-, bug present in all supported versions
Branch
------
REL_13_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/81ce000067e3c7d5f08adb615806453567ee142f
Modified Files
--------------
doc/src/sgml/catalogs.sgml | 3 +-
src/backend/commands/dbcommands.c | 100 +++++++++++++++---
src/backend/commands/vacuum.c | 14 +++
src/backend/postmaster/autovacuum.c | 12 +++
src/backend/utils/init/postinit.c | 10 ++
src/bin/pg_dump/pg_dumpall.c | 4 +-
src/bin/pg_dump/t/002_pg_dump.pl | 21 +++-
src/bin/scripts/clusterdb.c | 4 +-
src/bin/scripts/reindexdb.c | 4 +-
src/bin/scripts/t/011_clusterdb_all.pl | 15 ++-
src/bin/scripts/t/050_dropdb.pl | 11 +-
src/bin/scripts/t/091_reindexdb_all.pl | 15 ++-
src/bin/scripts/t/101_vacuumdb_all.pl | 15 ++-
src/bin/scripts/vacuumdb.c | 2 +-
src/include/catalog/pg_database.h | 20 +++-
src/test/recovery/t/037_invalid_database.pl | 157 ++++++++++++++++++++++++++++
16 files changed, 380 insertions(+), 27 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: andres@anarazel.de, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Handle DROP DATABASE getting interrupted
In-Reply-To: <E1qK2wW-000DmD-1M@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox