worker_spi: Fix another stability issue with BGWORKER_BYPASS_ALLOWCONN

michaelpq · michaelpq · commit f483b209056b · 2023-10-10T09:04:28.000+09:00
worker_spi_launch() may report that a worker stopped when it fails to connect on a database that does not allow connections if the worker exits before the SQL function checks for the current status of the worker. The test is switched to use Cluster::psql instead of safe_psql() so as it does not fail hard when this query errors. While on it, this removes a query that looks at pg_stat_activity to simplify the test, as a check on the contents of the server logs achieves the same when the worker cannot connect to the database without datallowconn. Per buildfarm members kestrel, mamba and serinus. Bonus thanks to Tom Lane for providing the logs of the failure from mamba that the buildfarm was not able to show up. Note that I have reproduced the failure with a hardcoded stop point. Discussion: https://postgr.es/m/3365937.1696801735@sss.pgh.pa.us
diff --git a/src/test/modules/worker_spi/t/001_worker_spi.pl b/src/test/modules/worker_spi/t/001_worker_spi.pl
@@ -105,30 +105,30 @@
 ) or die "Timed out while waiting for dynamic bgworkers to be launched";
 
 # Check BGWORKER_BYPASS_ALLOWCONN.
-$node->safe_psql('postgres', q(ALTER DATABASE mydb ALLOW_CONNECTIONS false;));
+$node->safe_psql('postgres',
+	q(CREATE DATABASE noconndb ALLOW_CONNECTIONS false;));
+my $noconndb_id = $node->safe_psql('mydb',
+	"SELECT oid FROM pg_database where datname = 'noconndb';");
 my $log_offset = -s $node->logfile;
 
-# bgworker cannot be launched with connection restriction.
-my $worker3_pid = $node->safe_psql('postgres',
-	qq[SELECT worker_spi_launch(12, $mydb_id, $myrole_id);]);
+# worker_spi_launch() may be able to detect that the worker has been
+# stopped, so do not rely on psql_safe().
+$node->psql('postgres',
+	qq[SELECT worker_spi_launch(12, $noconndb_id, $myrole_id);]);
 $node->wait_for_log(
-	qr/database "mydb" is not currently accepting connections/, $log_offset);
-
-$result = $node->safe_psql('postgres',
-	"SELECT count(*) FROM pg_stat_activity WHERE pid = $worker3_pid;");
-is($result, '0', 'dynamic bgworker without BYPASS_ALLOWCONN not started');
+	qr/database "noconndb" is not currently accepting connections/,
+	$log_offset);
 
 # bgworker bypasses the connection check, and can be launched.
 my $worker4_pid = $node->safe_psql('postgres',
-	qq[SELECT worker_spi_launch(12, $mydb_id, $myrole_id, '{"ALLOWCONN"}');]);
+	qq[SELECT worker_spi_launch(12, $noconndb_id, $myrole_id, '{"ALLOWCONN"}');]
+);
 ok( $node->poll_query_until(
 		'postgres',
 		qq[SELECT datname, usename, wait_event FROM pg_stat_activity
             WHERE backend_type = 'worker_spi dynamic' AND
             pid IN ($worker4_pid) ORDER BY datname;],
-		qq[mydb|myrole|WorkerSpiMain]),
+		qq[noconndb|myrole|WorkerSpiMain]),
 	'dynamic bgworker with BYPASS_ALLOWCONN started');
 
-$node->safe_psql('postgres', q(ALTER DATABASE mydb ALLOW_CONNECTIONS true;));
-
 done_testing();
