Debian Codegraph @

src/test/recovery/t/037_invalid_database.pl

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