pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Handle DROP DATABASE getting interrupted
7+ messages / 1 participants
[nested] [flat]

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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
------
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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* pgsql: Handle DROP DATABASE getting interrupted
@ 2023-07-13 20:29 Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Andres Freund @ 2023-07-13 20:29 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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_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(-)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-07-13 20:29 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>
2023-07-13 20:29 pgsql: Handle DROP DATABASE getting interrupted Andres Freund <andres@anarazel.de>

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