| 1 |
# Copyright (c) 2023-2025, PostgreSQL Global Development Group |
| 2 |
# |
| 3 |
# Test we handle interrupted DROP DATABASE correctly. |
| 4 |
|
| 5 |
use strict; |
| 6 |
use warnings FATAL => 'all'; |
| 7 |
use PostgreSQL::Test::Cluster; |
| 8 |
use PostgreSQL::Test::Utils; |
| 9 |
use Test::More; |
| 10 |
|
| 11 |
my $node = PostgreSQL::Test::Cluster->new('node'); |
| 12 |
$node->init; |
| 13 |
$node->append_conf( |
| 14 |
"postgresql.conf", qq( |
| 15 |
autovacuum = off |
| 16 |
max_prepared_transactions=5 |
| 17 |
log_min_duration_statement=0 |
| 18 |
log_connections=receipt |
| 19 |
log_disconnections=on |
| 20 |
)); |
| 21 |
|
| 22 |
$node->start; |
| 23 |
|
| 24 |
|
| 25 |
# First verify that we can't connect to or ALTER an invalid database. Just |
| 26 |
# mark the database as invalid ourselves, that's more reliable than hitting the |
| 27 |
# required race conditions (see testing further down)... |
| 28 |
|
| 29 |
$node->safe_psql( |
| 30 |
"postgres", qq( |
| 31 |
CREATE DATABASE regression_invalid; |
| 32 |
UPDATE pg_database SET datconnlimit = -2 WHERE datname = 'regression_invalid'; |
| 33 |
)); |
| 34 |
|
| 35 |
my $psql_stdout = ''; |
| 36 |
my $psql_stderr = ''; |
| 37 |
|
| 38 |
is($node->psql('regression_invalid', '', stderr => \$psql_stderr), |
| 39 |
2, "can't connect to invalid database - error code"); |
| 40 |
like( |
| 41 |
$psql_stderr, |
| 42 |
qr/FATAL:\s+cannot connect to invalid database "regression_invalid"/, |
| 43 |
"can't connect to invalid database - error message"); |
| 44 |
|
| 45 |
is( $node->psql( |
| 46 |
'postgres', 'ALTER DATABASE regression_invalid CONNECTION LIMIT 10'), |
| 47 |
2, |
| 48 |
"can't ALTER invalid database"); |
| 49 |
|
| 50 |
# check invalid database can't be used as a template |
| 51 |
is( $node->psql( |
| 52 |
'postgres', |
| 53 |
'CREATE DATABASE copy_invalid TEMPLATE regression_invalid'), |
| 54 |
3, |
| 55 |
"can't use invalid database as template"); |
| 56 |
|
| 57 |
|
| 58 |
# Verify that VACUUM ignores an invalid database when computing how much of |
| 59 |
# the clog is needed (vac_truncate_clog()). For that we modify the pg_database |
| 60 |
# row of the invalid database to have an outdated datfrozenxid. |
| 61 |
$psql_stderr = ''; |
| 62 |
$node->psql( |
| 63 |
'postgres', |
| 64 |
qq( |
| 65 |
UPDATE pg_database SET datfrozenxid = '123456' WHERE datname = 'regression_invalid'; |
| 66 |
DROP TABLE IF EXISTS foo_tbl; CREATE TABLE foo_tbl(); |
| 67 |
VACUUM FREEZE;), |
| 68 |
stderr => \$psql_stderr); |
| 69 |
unlike( |
| 70 |
$psql_stderr, |
| 71 |
qr/some databases have not been vacuumed in over 2 billion transactions/, |
| 72 |
"invalid databases are ignored by vac_truncate_clog"); |
| 73 |
|
| 74 |
|
| 75 |
# But we need to be able to drop an invalid database. |
| 76 |
is( $node->psql( |
| 77 |
'postgres', 'DROP DATABASE regression_invalid', |
| 78 |
stdout => \$psql_stdout, |
| 79 |
stderr => \$psql_stderr), |
| 80 |
0, |
| 81 |
"can DROP invalid database"); |
| 82 |
|
| 83 |
# Ensure database is gone |
| 84 |
is($node->psql('postgres', 'DROP DATABASE regression_invalid'), |
| 85 |
3, "can't drop already dropped database"); |
| 86 |
|
| 87 |
|
| 88 |
# Test that interruption of DROP DATABASE is handled properly. To ensure the |
| 89 |
# interruption happens at the appropriate moment, we lock pg_tablespace. DROP |
| 90 |
# DATABASE scans pg_tablespace once it has reached the "irreversible" part of |
| 91 |
# dropping the database, making it a suitable point to wait. Since relcache |
| 92 |
# init reads pg_tablespace, establish each connection before locking. This |
| 93 |
# avoids a connection-time hang with debug_discard_caches. |
| 94 |
my $cancel = $node->background_psql('postgres', on_error_stop => 1); |
| 95 |
my $bgpsql = $node->background_psql('postgres', on_error_stop => 0); |
| 96 |
my $pid = $bgpsql->query('SELECT pg_backend_pid()'); |
| 97 |
|
| 98 |
# create the database, prevent drop database via lock held by a 2PC transaction |
| 99 |
$bgpsql->query_safe( |
| 100 |
qq( |
| 101 |
CREATE DATABASE regression_invalid_interrupt; |
| 102 |
BEGIN; |
| 103 |
LOCK pg_tablespace; |
| 104 |
PREPARE TRANSACTION 'lock_tblspc';)); |
| 105 |
|
| 106 |
# Try to drop. This will wait due to the still held lock. |
| 107 |
$bgpsql->query_until(qr//, "DROP DATABASE regression_invalid_interrupt;\n"); |
| 108 |
|
| 109 |
|
| 110 |
# Once the DROP DATABASE is waiting for the lock, interrupt it. |
| 111 |
ok( $cancel->query_safe( |
| 112 |
qq( |
| 113 |
DO \$\$ |
| 114 |
BEGIN |
| 115 |
WHILE NOT EXISTS(SELECT * FROM pg_locks WHERE NOT granted AND relation = 'pg_tablespace'::regclass AND mode = 'AccessShareLock') LOOP |
| 116 |
PERFORM pg_sleep(.1); |
| 117 |
END LOOP; |
| 118 |
END\$\$; |
| 119 |
SELECT pg_cancel_backend($pid);)), |
| 120 |
"canceling DROP DATABASE"); |
| 121 |
$cancel->quit(); |
| 122 |
|
| 123 |
# wait for cancellation to be processed |
| 124 |
ok( pump_until( |
| 125 |
$bgpsql->{run}, $bgpsql->{timeout}, |
| 126 |
\$bgpsql->{stderr}, qr/canceling statement due to user request/), |
| 127 |
"cancel processed"); |
| 128 |
$bgpsql->{stderr} = ''; |
| 129 |
|
| 130 |
# Verify that connections to the database aren't allowed. The backend checks |
| 131 |
# this before relcache init, so the lock won't interfere. |
| 132 |
is($node->psql('regression_invalid_interrupt', ''), |
| 133 |
2, "can't connect to invalid_interrupt database"); |
| 134 |
|
| 135 |
# To properly drop the database, we need to release the lock previously preventing |
| 136 |
# doing so. |
| 137 |
$bgpsql->query_safe(qq(ROLLBACK PREPARED 'lock_tblspc')); |
| 138 |
$bgpsql->query_safe(qq(DROP DATABASE regression_invalid_interrupt)); |
| 139 |
|
| 140 |
$bgpsql->quit(); |
| 141 |
|
| 142 |
done_testing(); |