Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 5 Mar 2025 13:45:12 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Jacob Champion <jacob.champion@enterprisedb.com>
Cc: Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>,
	Noah Misch <noah@leadboat.com>,
	Andrew Dunstan <andrew@dunslane.net>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
	Euler Taveira <euler.taveira@enterprisedb.com>,
	Daniel Gustafsson <daniel@yesql.se>
Subject: Re: [PATCH] pg_stat_activity: make slow/hanging authentication more
 visible
Message-ID: <Z8fW2PcLkkXaK9L8@paquier.xyz>
References: <Z6xMe4m4yaCymTj3@paquier.xyz>
 <CAOYmi+n1_s0-pomOPEY4eAEuwr8fJZKDsgCFjfrsC8qUsi7Umg@mail.gmail.com>
 <Z66IPGFRaq28l39o@paquier.xyz>
 <CAOYmi+kNQQpT_dmB7vzVf3J+TUu6rwiHXbVTMc67+mh_x9DqXg@mail.gmail.com>
 <CAOYmi+k=-ZdzfmaQwpRKodDHChoyNN-uxbD_G7zY_5cWz+yvhA@mail.gmail.com>
 <Z8U854O5RayMMgoc@paquier.xyz>
 <Z8a_BePM8FCj1GQe@paquier.xyz>
 <soexrl7oeyku24bj3czupxmv27ow35u6edymp5y3oyoysbe2kb@r3tgoos2xp2x>
 <CAOYmi+kA2VNne4AofaMv-C7vYSmdcAXaFWh3W4Nw11c0Rc+QgA@mail.gmail.com>
 <CAOYmi+=tkATXFmvvAzwLTwq52YrezFopeoPrkW3pXv6LDFYT6A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="IqKghAUJG4NQOj8y"
Content-Disposition: inline
In-Reply-To: <CAOYmi+=tkATXFmvvAzwLTwq52YrezFopeoPrkW3pXv6LDFYT6A@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Z8fW2PcLkkXaK9L8%40paquier.xyz>
Precedence: bulk


--IqKghAUJG4NQOj8y
Content-Type: multipart/mixed; boundary="/gQizRf4znsGxEBN"
Content-Disposition: inline


--/gQizRf4znsGxEBN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Mar 04, 2025 at 04:53:14PM -0800, Jacob Champion wrote:
> I'll work on a fix, but it probably won't be fast since I need to
> learn more about the injection points architecture. The test may need
> to be disabled, or the patch backed out, depending on how painful the
> flake is for everybody.

Oops, missed these failures..  So we have a race condition where we
are trying to wake up a point that's not waiting yet, because there is
a small window between the moment when the backend entry is marked as
"starting" and the injection point wait.

What this is telling us is that we should change the query scanning
pg_stat_activity for a PID of a backend in 'starting' state so as we
also check the wait_event init-pre-auth, as this is reported when
using injection point waits.  The attached should be enough to take
care of this race condition.
--
Michael

--/gQizRf4znsGxEBN
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0001-Fix-race-condition-in-pre-auth-test.patch"
Content-Transfer-Encoding: quoted-printable

=46rom d5c976dc995bf09c4868e0b07d4652dabcaec888 Mon Sep 17 00:00:00 2001
=46rom: Michael Paquier <michael@paquier.xyz>
Date: Wed, 5 Mar 2025 13:30:43 +0900
Subject: [PATCH] Fix race condition in pre-auth test

---
 src/test/authentication/t/007_pre_auth.pl | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

diff --git a/src/test/authentication/t/007_pre_auth.pl b/src/test/authentic=
ation/t/007_pre_auth.pl
index a638226dbaf1..90aaea4b5a64 100644
--- a/src/test/authentication/t/007_pre_auth.pl
+++ b/src/test/authentication/t/007_pre_auth.pl
@@ -43,12 +43,14 @@ $psql->query_safe("SELECT injection_points_attach('init=
-pre-auth', 'wait')");
 # authentication. Use the $psql connection handle for server interaction.
 my $conn =3D $node->background_psql('postgres', wait =3D> 0);
=20
-# Wait for the connection to show up.
+# Wait for the connection to show up in pg_stat_activity, with the wait_ev=
ent
+# of the injection point.
 my $pid;
 while (1)
 {
 	$pid =3D $psql->query(
-		"SELECT pid FROM pg_stat_activity WHERE state =3D 'starting';");
+		qq{SELECT pid FROM pg_stat_activity
+  WHERE state =3D 'starting' and wait_event =3D 'init-pre-auth';});
 	last if $pid ne "";
=20
 	usleep(100_000);
--=20
2.47.2


--/gQizRf4znsGxEBN--

--IqKghAUJG4NQOj8y
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmfH1tgACgkQnvQgOdby
QH3nLBAAg0llom63A9aeG12fAEHTWy6AIkPL0OCSxaEni/OQas11VDpfKN82zkHm
Jo5iVbcCBT7vFO5UO4+/JCMmpsUjNcjy87cT3HYwzyhlDtr/IeDzvenh/ginmeLz
tbK+9gcEUEBJNMC+bXBW2vHhZpAoRV1ady/raXSh1jY8pKVYIHtO/enAHcv4PBT6
GdlgmUSKAgZZphsFHiiFbriQTYsezf9esVv+vHAcmnrorA125F8jIijQew9dAJC7
BzI7g+iGn9xO/cL7TVEezFLYRj/C6KoE3ZLNN3IGAqdAXTauufrgolPMtres5YdZ
+pzTAU0VhE/3LAuvnPB8VwYbEK+rkCndB2iTkyus6BJ84b2d1xanIBvEHpMs7RMG
paZgY1TNwpoeIhS/ZbF5bh+zuvBdcAaTQcM+Kqmz7KzniVWt7+Jwum8Sfwi22fih
C91ZYsewHfDimlCtFAxOPNOL45oWBGOhwYQdK+rDIYpZfNrjmlEfS95j1b1sjojl
yoem/m8fal8uIx3JZ0txF8s4ZS6eZg1EnAuwjDjtJXs3MQR/qtEtzPorPFzuH7cI
VZlXFvIB92z2mikENZVksbAv2s/FylmUsFZbMNKFv8bj31pU3YFXeX6FGJ5iC49x
G8ZxhyqkvR3xpgYIWAg8FiFvqZ3Pwt15MHgtwzuy5RpATyAUHqg=
=cMys
-----END PGP SIGNATURE-----

--IqKghAUJG4NQOj8y--