postgres.git / summary / log / commit / refs
commit a00e43ac32d4f7367567791a68b2cf815f8c4d3f
Author: Fujii Masao <fujii@postgresql.org>
Date: Thu Jul 16 15:49:26 2026 +0000
First released in: 18.6
postgres_fdw: stabilize terminated-connection regression tests
The regression test for postgres_fdw_get_connections(true) assumed that
a terminated remote connection would still remain visible in the FDW
connection cache long enough to be reported as closed with a nonzero
remote_backend_pid.
That assumption is not always valid. postgres_fdw_get_connections()
reports only entries that are still present in ConnectionHash, while
pgfdw_inval_callback() may immediately discard an idle cached connection
(xact_depth == 0) when a relevant invalidation arrives. In CI, that can
happen between terminating the remote backend and querying
postgres_fdw_get_connections(true), causing the function to return no
rows.
Adjust the idle-connection test to accept either outcome: if the cache
entry is still present, verify that it reports the expected server name,
closed status, and nonzero remote backend PID; otherwise treat zero rows
as a legitimate result.
To preserve coverage of the terminated-backend reporting path, add a
separate check inside an explicit transaction. In that case, concurrent
invalidation may mark the connection invalid but cannot discard it
before transaction end, so postgres_fdw_get_connections(true) should
still report the terminated connection as in-use, closed, and associated
with a nonzero remote backend PID.
Backpatch to v18, where the affected postgres_fdw_get_connections(true)
test was introduced.
Reported-by: Robert Haas <robertmhaas@gmail.com>
Author: Fujii Masao <masao.fujii@gmail.com>
Reviewed-by: Robert Haas <robertmhaas@gmail.com>
Reviewed-by: Daniel Gustafsson <daniel@yesql.se>
Discussion: https://postgr.es/m/CA+Tgmoax3cHXHsm9OidN4F-xiu16y8q2W8T5dTNFic1Zoo2cOw@mail.gmail.com
Backpatch-through: 18
contrib/postgres_fdw/expected/postgres_fdw.out | 60 ++++++++++++++++++++++----
contrib/postgres_fdw/sql/postgres_fdw.sql | 40 ++++++++++++++---
2 files changed, 87 insertions(+), 13 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 3cc3c6dc3c7..a65f81d6eb6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12758,23 +12758,67 @@ SELECT server_name,
loopback | f | t
(1 row)
--- After terminating the remote backend, since the connection is closed,
--- "closed" should be TRUE, or NULL if the connection status check
--- is not available. Despite the termination, remote_backend_pid should
--- still show the non-zero PID of the terminated remote backend.
+-- After terminating the remote backend, if the connection entry is still in
+-- the cache, "closed" should be TRUE, or NULL if the connection status check
+-- is not available, and remote_backend_pid should still show the non-zero PID
+-- of the terminated remote backend. Concurrent invalidation can remove the
+-- idle cached connection before the next statement, in which case
+-- postgres_fdw_get_connections(true) can legitimately return no rows.
DO $$ BEGIN
PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
WHERE application_name = 'fdw_conn_check';
END $$;
-SELECT server_name,
+WITH terminated_conn AS (
+ SELECT server_name,
+ CASE WHEN closed IS NOT false THEN true ELSE false END AS closed,
+ remote_backend_pid <> 0 AS remote_backend_pid
+ FROM postgres_fdw_get_connections(true)
+)
+SELECT CASE
+ WHEN count(*) = 0 THEN true
+ WHEN count(*) = 1 THEN bool_and(server_name = 'loopback'
+ AND closed
+ AND remote_backend_pid)
+ ELSE false
+END AS ok
+FROM terminated_conn;
+ ok
+----
+ t
+(1 row)
+
+-- In an explicit transaction, concurrent invalidation may mark the
+-- connection invalid but cannot discard it before transaction end, so the
+-- terminated connection should remain visible in the cache.
+SELECT 1 FROM postgres_fdw_disconnect_all();
+ ?column?
+----------
+ 1
+(1 row)
+
+SET client_min_messages = 'ERROR';
+BEGIN;
+SELECT 1 FROM ft1 LIMIT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+DO $$ BEGIN
+PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'fdw_conn_check';
+END $$;
+SELECT server_name, used_in_xact,
CASE WHEN closed IS NOT false THEN true ELSE false END AS closed,
remote_backend_pid <> 0 AS remote_backend_pid
FROM postgres_fdw_get_connections(true);
- server_name | closed | remote_backend_pid
--------------+--------+--------------------
- loopback | t | t
+ server_name | used_in_xact | closed | remote_backend_pid
+-------------+--------------+--------+--------------------
+ loopback | t | t | t
(1 row)
+ABORT;
+RESET client_min_messages;
-- Clean up
\set VERBOSITY default
RESET debug_discard_caches;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 22e24bb024f..2f7b3399198 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4451,18 +4451,48 @@ SELECT server_name,
WHERE application_name = 'fdw_conn_check') AS remote_backend_pid
FROM postgres_fdw_get_connections(true);
--- After terminating the remote backend, since the connection is closed,
--- "closed" should be TRUE, or NULL if the connection status check
--- is not available. Despite the termination, remote_backend_pid should
--- still show the non-zero PID of the terminated remote backend.
+-- After terminating the remote backend, if the connection entry is still in
+-- the cache, "closed" should be TRUE, or NULL if the connection status check
+-- is not available, and remote_backend_pid should still show the non-zero PID
+-- of the terminated remote backend. Concurrent invalidation can remove the
+-- idle cached connection before the next statement, in which case
+-- postgres_fdw_get_connections(true) can legitimately return no rows.
DO $$ BEGIN
PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
WHERE application_name = 'fdw_conn_check';
END $$;
-SELECT server_name,
+WITH terminated_conn AS (
+ SELECT server_name,
+ CASE WHEN closed IS NOT false THEN true ELSE false END AS closed,
+ remote_backend_pid <> 0 AS remote_backend_pid
+ FROM postgres_fdw_get_connections(true)
+)
+SELECT CASE
+ WHEN count(*) = 0 THEN true
+ WHEN count(*) = 1 THEN bool_and(server_name = 'loopback'
+ AND closed
+ AND remote_backend_pid)
+ ELSE false
+END AS ok
+FROM terminated_conn;
+
+-- In an explicit transaction, concurrent invalidation may mark the
+-- connection invalid but cannot discard it before transaction end, so the
+-- terminated connection should remain visible in the cache.
+SELECT 1 FROM postgres_fdw_disconnect_all();
+SET client_min_messages = 'ERROR';
+BEGIN;
+SELECT 1 FROM ft1 LIMIT 1;
+DO $$ BEGIN
+PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'fdw_conn_check';
+END $$;
+SELECT server_name, used_in_xact,
CASE WHEN closed IS NOT false THEN true ELSE false END AS closed,
remote_backend_pid <> 0 AS remote_backend_pid
FROM postgres_fdw_get_connections(true);
+ABORT;
+RESET client_min_messages;
-- Clean up
\set VERBOSITY default
[parent: 30c0442eb308]