v3-0001-Simplify-backend-terminate-and-wait-logic-in-post.patch

From 89495d7f87132af5128fc3a4bc941a11bb558594 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddy@enterprisedb.com>
Date: Fri, 9 Apr 2021 16:23:46 +0530
Subject: [PATCH v3] Simplify backend terminate and wait logic in postgres_fdw
 test

With the recent commit aaf0432572 which introduced a waiting
capability for pg_teriminate_backend function, we can simply
backend terminate and wait logic in postgres_fdw.sql tests.
---
 .../postgres_fdw/expected/postgres_fdw.out    | 36 +++++++++----------
 contrib/postgres_fdw/sql/postgres_fdw.sql     | 29 +++++----------
 2 files changed, 26 insertions(+), 39 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7f69fa0054..e45d1a30f5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -9195,19 +9195,6 @@ WARNING:  there is no transaction in progress
 -- ===================================================================
 -- reestablish new connection
 -- ===================================================================
--- Terminate the backend having the specified application_name and wait for
--- the termination to complete.
-CREATE OR REPLACE PROCEDURE terminate_backend_and_wait(appname text) AS $$
-BEGIN
-    PERFORM pg_terminate_backend(pid) FROM pg_stat_activity
-    WHERE application_name = appname;
-    LOOP
-        PERFORM * FROM pg_stat_activity WHERE application_name = appname;
-        EXIT WHEN NOT FOUND;
-        PERFORM pg_sleep(1), pg_stat_clear_snapshot();
-    END LOOP;
-END;
-$$ LANGUAGE plpgsql;
 -- Change application_name of remote connection to special one
 -- so that we can easily terminate the connection later.
 ALTER SERVER loopback OPTIONS (application_name 'fdw_retry_check');
@@ -9217,8 +9204,15 @@ SELECT 1 FROM ft1 LIMIT 1;
         1
 (1 row)
 
--- Terminate the remote connection.
-CALL terminate_backend_and_wait('fdw_retry_check');
+-- Terminate the remote connection and wait for its exit from the system
+-- processes.
+SELECT pg_terminate_backend(pid, 3600000) FROM pg_stat_activity
+	WHERE application_name = 'fdw_retry_check';
+ pg_terminate_backend 
+----------------------
+ t
+(1 row)
+
 -- This query should detect the broken connection when starting new remote
 -- transaction, reestablish new connection, and then succeed.
 BEGIN;
@@ -9231,15 +9225,21 @@ SELECT 1 FROM ft1 LIMIT 1;
 -- If the query detects the broken connection when starting new remote
 -- subtransaction, it doesn't reestablish new connection and should fail.
 -- The text of the error might vary across platforms, so don't show it.
-CALL terminate_backend_and_wait('fdw_retry_check');
+-- Terminate the remote connection and wait for its exit from the system
+-- processes.
+SELECT pg_terminate_backend(pid, 3600000) FROM pg_stat_activity
+	WHERE application_name = 'fdw_retry_check';
+ pg_terminate_backend 
+----------------------
+ t
+(1 row)
+
 SAVEPOINT s;
 \set VERBOSITY sqlstate
 SELECT 1 FROM ft1 LIMIT 1;    -- should fail
 ERROR:  08006
 \set VERBOSITY default
 COMMIT;
--- Clean up
-DROP PROCEDURE terminate_backend_and_wait(text);
 -- =============================================================================
 -- test connection invalidation cases and postgres_fdw_get_connections function
 -- =============================================================================
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 7487096eac..c41f434c1f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2785,28 +2785,15 @@ ROLLBACK;
 -- ===================================================================
 -- reestablish new connection
 -- ===================================================================
-
--- Terminate the backend having the specified application_name and wait for
--- the termination to complete.
-CREATE OR REPLACE PROCEDURE terminate_backend_and_wait(appname text) AS $$
-BEGIN
-    PERFORM pg_terminate_backend(pid) FROM pg_stat_activity
-    WHERE application_name = appname;
-    LOOP
-        PERFORM * FROM pg_stat_activity WHERE application_name = appname;
-        EXIT WHEN NOT FOUND;
-        PERFORM pg_sleep(1), pg_stat_clear_snapshot();
-    END LOOP;
-END;
-$$ LANGUAGE plpgsql;
-
 -- Change application_name of remote connection to special one
 -- so that we can easily terminate the connection later.
 ALTER SERVER loopback OPTIONS (application_name 'fdw_retry_check');
 SELECT 1 FROM ft1 LIMIT 1;
 
--- Terminate the remote connection.
-CALL terminate_backend_and_wait('fdw_retry_check');
+-- Terminate the remote connection and wait for its exit from the system
+-- processes.
+SELECT pg_terminate_backend(pid, 3600000) FROM pg_stat_activity
+	WHERE application_name = 'fdw_retry_check';
 
 -- This query should detect the broken connection when starting new remote
 -- transaction, reestablish new connection, and then succeed.
@@ -2816,16 +2803,16 @@ SELECT 1 FROM ft1 LIMIT 1;
 -- If the query detects the broken connection when starting new remote
 -- subtransaction, it doesn't reestablish new connection and should fail.
 -- The text of the error might vary across platforms, so don't show it.
-CALL terminate_backend_and_wait('fdw_retry_check');
+-- Terminate the remote connection and wait for its exit from the system
+-- processes.
+SELECT pg_terminate_backend(pid, 3600000) FROM pg_stat_activity
+	WHERE application_name = 'fdw_retry_check';
 SAVEPOINT s;
 \set VERBOSITY sqlstate
 SELECT 1 FROM ft1 LIMIT 1;    -- should fail
 \set VERBOSITY default
 COMMIT;
 
--- Clean up
-DROP PROCEDURE terminate_backend_and_wait(text);
-
 -- =============================================================================
 -- test connection invalidation cases and postgres_fdw_get_connections function
 -- =============================================================================
-- 
2.25.1