pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- REL_16_STABLE Details --- https://git.postgresql.org/pg/commitdiff/a4b4cc1d60f7e8ccfcc8ff8cb80c28ee411ad9a9 Modified Files -- doc/src/sgml/catalogs.sgml | 3 +- src/backend/commands/dbcommands.c | 110 ++- src/backend/commands/vacuum.c | 14 +++ src/backend/postmaster/autovacuum.c | 12 +++ src/backend/utils/init/postinit.c | 10 +++ src/bin/pg_amcheck/pg_amcheck.c | 2 +- src/bin/pg_amcheck/t/002_nonesuch.pl| 34 +++ src/bin/pg_dump/pg_dumpall.c| 4 +- src/bin/pg_dump/t/002_pg_dump.pl| 19 src/bin/pg_upgrade/t/002_pg_upgrade.pl | 26 ++ src/bin/scripts/clusterdb.c | 4 +- src/bin/scripts/reindexdb.c | 4 +- src/bin/scripts/t/011_clusterdb_all.pl | 14 +++ src/bin/scripts/t/050_dropdb.pl | 9 ++ src/bin/scripts/t/091_reindexdb_all.pl | 14 +++ src/bin/scripts/t/101_vacuumdb_all.pl | 14 +++ src/bin/scripts/vacuumdb.c | 2 +- src/include/catalog/pg_database.h | 20 - src/test/recovery/meson.build | 1 + src/test/recovery/t/037_invalid_database.pl | 133 20 files changed, 421 insertions(+), 28 deletions(-)
pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- master Details --- https://git.postgresql.org/pg/commitdiff/c66a7d75e652801043ece99b6a8f89fd9513eaaa Modified Files -- doc/src/sgml/catalogs.sgml | 3 +- src/backend/commands/dbcommands.c | 110 ++- src/backend/commands/vacuum.c | 14 +++ src/backend/postmaster/autovacuum.c | 12 +++ src/backend/utils/init/postinit.c | 10 +++ src/bin/pg_amcheck/pg_amcheck.c | 2 +- src/bin/pg_amcheck/t/002_nonesuch.pl| 34 +++ src/bin/pg_dump/pg_dumpall.c| 4 +- src/bin/pg_dump/t/002_pg_dump.pl| 19 src/bin/pg_upgrade/t/002_pg_upgrade.pl | 26 ++ src/bin/scripts/clusterdb.c | 4 +- src/bin/scripts/reindexdb.c | 4 +- src/bin/scripts/t/011_clusterdb_all.pl | 14 +++ src/bin/scripts/t/050_dropdb.pl | 9 ++ src/bin/scripts/t/091_reindexdb_all.pl | 14 +++ src/bin/scripts/t/101_vacuumdb_all.pl | 14 +++ src/bin/scripts/vacuumdb.c | 2 +- src/include/catalog/pg_database.h | 20 - src/test/recovery/meson.build | 1 + src/test/recovery/t/037_invalid_database.pl | 133 20 files changed, 421 insertions(+), 28 deletions(-)
pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- REL_12_STABLE Details --- https://git.postgresql.org/pg/commitdiff/034a9fcd2bb8e8ac49165e983a44e8805c8a7c63 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(-)
pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- REL_13_STABLE Details --- https://git.postgresql.org/pg/commitdiff/81ce67e3c7d5f08adb615806453567ee142f 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(-)
pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- REL_14_STABLE Details --- https://git.postgresql.org/pg/commitdiff/d11efe83038538a7b5169c679ceee457f5753877 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_amcheck/pg_amcheck.c | 2 +- src/bin/pg_amcheck/t/002_nonesuch.pl| 36 ++- 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 18 files changed, 416 insertions(+), 29 deletions(-)
pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- REL_11_STABLE Details --- https://git.postgresql.org/pg/commitdiff/1c38e7ae17b68a78dcef6a81be9fcf05ba91b374 Modified Files -- doc/src/sgml/catalogs.sgml | 3 +- src/backend/commands/dbcommands.c | 102 +++--- 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 | 21 +++- src/test/recovery/t/037_invalid_database.pl | 157 16 files changed, 383 insertions(+), 27 deletions(-)
pgsql: Handle DROP DATABASE getting interrupted
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 Author: Andres Freund Reviewed-by: Daniel Gustafsson Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/20230509004637.cgvmfwrbht7xm...@awork3.anarazel.de Discussion: https://postgr.es/m/20230314174521.74jl6ffqsee5m...@awork3.anarazel.de Backpatch: 11-, bug present in all supported versions Branch -- REL_15_STABLE Details --- https://git.postgresql.org/pg/commitdiff/f66403749df744be60f45d20cace48cbc030b804 Modified Files -- doc/src/sgml/catalogs.sgml | 3 +- src/backend/commands/dbcommands.c | 110 +++ src/backend/commands/vacuum.c | 14 +++ src/backend/postmaster/autovacuum.c | 12 +++ src/backend/utils/init/postinit.c | 10 ++ src/bin/pg_amcheck/pg_amcheck.c | 2 +- src/bin/pg_amcheck/t/002_nonesuch.pl| 34 ++ src/bin/pg_dump/pg_dumpall.c| 4 +- src/bin/pg_dump/t/002_pg_dump.pl| 19 src/bin/pg_upgrade/t/002_pg_upgrade.pl | 26 + src/bin/scripts/clusterdb.c | 4 +- src/bin/scripts/reindexdb.c | 4 +- src/bin/scripts/t/011_clusterdb_all.pl | 14 +++ src/bin/scripts/t/050_dropdb.pl | 9 ++ src/bin/scripts/t/091_reindexdb_all.pl | 14 +++ src/bin/scripts/t/101_vacuumdb_all.pl | 14 +++ src/bin/scripts/vacuumdb.c | 2 +- src/include/catalog/pg_database.h | 20 +++- src/test/recovery/t/037_invalid_database.pl | 157 19 files changed, 444 insertions(+), 28 deletions(-)
