Revert pg_wal_replay_wait() stored procedure

This commit reverts 3c5db1d6b0, and subsequent improvements and fixes
including 8036d73ae3, 867d396ccd, 3ac3ec580c, 0868d7ae70, 85b98b8d5a,
2520226c95, 014f9f34d2, e658038772, e1555645d7, 5035172e4a, 6cfebfe88b,
73da6b8d1b, and e546989a26.

The reason for reverting is a set of remaining issues.  Most notably, the
stored procedure appears to need more effort than the utility statement
to turn the backend into a "snapshot-less" state.  This makes an approach
to use stored procedures questionable.

Catversion is bumped.

Discussion: https://postgr.es/m/Zyhj2anOPRKtb0xW%40paquier.xyz

2 files changed, 0 insertions, 226 deletions

diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 712924c2fad..b1eb77b1ec1 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -51,7 +51,6 @@ tests += {
       't/040_standby_failover_slots_sync.pl',
       't/041_checkpoint_at_promote.pl',
       't/042_low_level_backup.pl',
-      't/043_wal_replay_wait.pl',
     ],
   },
 }
diff --git a/src/test/recovery/t/043_wal_replay_wait.pl b/src/test/recovery/t/043_wal_replay_wait.pl
deleted file mode 100644
index 5857b943711..00000000000
--- a/src/test/recovery/t/043_wal_replay_wait.pl
+++ /dev/null
@@ -1,225 +0,0 @@
-# Checks waiting for the lsn replay on standby using
-# pg_wal_replay_wait() procedure.
-use strict;
-use warnings FATAL => 'all';
-
-use PostgreSQL::Test::Cluster;
-use PostgreSQL::Test::Utils;
-use Test::More;
-
-# Initialize primary node
-my $node_primary = PostgreSQL::Test::Cluster->new('primary');
-$node_primary->init(allows_streaming => 1);
-$node_primary->start;
-
-# And some content and take a backup
-$node_primary->safe_psql('postgres',
-	"CREATE TABLE wait_test AS SELECT generate_series(1,10) AS a");
-my $backup_name = 'my_backup';
-$node_primary->backup($backup_name);
-
-# Create a streaming standby with a 1 second delay from the backup
-my $node_standby = PostgreSQL::Test::Cluster->new('standby');
-my $delay = 1;
-$node_standby->init_from_backup($node_primary, $backup_name,
-	has_streaming => 1);
-$node_standby->append_conf(
-	'postgresql.conf', qq[
-	recovery_min_apply_delay = '${delay}s'
-]);
-$node_standby->start;
-
-# 1. Make sure that pg_wal_replay_wait() works: add new content to
-# primary and memorize primary's insert LSN, then wait for that LSN to be
-# replayed on standby.
-$node_primary->safe_psql('postgres',
-	"INSERT INTO wait_test VALUES (generate_series(11, 20))");
-my $lsn1 =
-  $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
-my $output = $node_standby->safe_psql(
-	'postgres', qq[
-	CALL pg_wal_replay_wait('${lsn1}', 1000000);
-	SELECT pg_lsn_cmp(pg_last_wal_replay_lsn(), '${lsn1}'::pg_lsn);
-]);
-
-# Make sure the current LSN on standby is at least as big as the LSN we
-# observed on primary's before.
-ok($output >= 0,
-	"standby reached the same LSN as primary after pg_wal_replay_wait()");
-
-# 2. Check that new data is visible after calling pg_wal_replay_wait()
-$node_primary->safe_psql('postgres',
-	"INSERT INTO wait_test VALUES (generate_series(21, 30))");
-my $lsn2 =
-  $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
-$output = $node_standby->safe_psql(
-	'postgres', qq[
-	CALL pg_wal_replay_wait('${lsn2}');
-	SELECT count(*) FROM wait_test;
-]);
-
-# Make sure the count(*) on standby reflects the recent changes on primary
-ok($output eq 30, "standby reached the same LSN as primary");
-
-# 3. Check that waiting for unreachable LSN triggers the timeout.  The
-# unreachable LSN must be well in advance.  So WAL records issued by
-# the concurrent autovacuum could not affect that.
-my $lsn3 =
-  $node_primary->safe_psql('postgres',
-	"SELECT pg_current_wal_insert_lsn() + 10000000000");
-my $stderr;
-$node_standby->safe_psql('postgres',
-	"CALL pg_wal_replay_wait('${lsn2}', 10);");
-$node_standby->psql(
-	'postgres',
-	"CALL pg_wal_replay_wait('${lsn3}', 1000);",
-	stderr => \$stderr);
-ok( $stderr =~ /timed out while waiting for target LSN/,
-	"get timeout on waiting for unreachable LSN");
-
-$output = $node_standby->safe_psql(
-	'postgres', qq[
-	CALL pg_wal_replay_wait('${lsn2}', 10, true);
-	SELECT pg_wal_replay_wait_status();]);
-ok( $output eq "success",
-	"pg_wal_replay_wait_status() returns correct status after successful waiting"
-);
-$output = $node_standby->safe_psql(
-	'postgres', qq[
-	CALL pg_wal_replay_wait('${lsn3}', 10, true);
-	SELECT pg_wal_replay_wait_status();]);
-ok($output eq "timeout",
-	"pg_wal_replay_wait_status() returns correct status after timeout");
-
-# 4. Check that pg_wal_replay_wait() triggers an error if called on primary,
-# within another function, or inside a transaction with an isolation level
-# higher than READ COMMITTED.
-
-$node_primary->psql(
-	'postgres',
-	"CALL pg_wal_replay_wait('${lsn3}');",
-	stderr => \$stderr);
-ok( $stderr =~ /recovery is not in progress/,
-	"get an error when running on the primary");
-
-$node_standby->psql(
-	'postgres',
-	"BEGIN ISOLATION LEVEL REPEATABLE READ; CALL pg_wal_replay_wait('${lsn3}');",
-	stderr => \$stderr);
-ok( $stderr =~
-	  /pg_wal_replay_wait\(\) must be only called without an active or registered snapshot/,
-	"get an error when running in a transaction with an isolation level higher than REPEATABLE READ"
-);
-
-$node_primary->safe_psql(
-	'postgres', qq[
-CREATE FUNCTION pg_wal_replay_wait_wrap(target_lsn pg_lsn) RETURNS void AS \$\$
-  BEGIN
-    CALL pg_wal_replay_wait(target_lsn);
-  END
-\$\$
-LANGUAGE plpgsql;
-]);
-
-$node_primary->wait_for_catchup($node_standby);
-$node_standby->psql(
-	'postgres',
-	"SELECT pg_wal_replay_wait_wrap('${lsn3}');",
-	stderr => \$stderr);
-ok( $stderr =~
-	  /pg_wal_replay_wait\(\) must be only called without an active or registered snapshot/,
-	"get an error when running within another function");
-
-# 5. Also, check the scenario of multiple LSN waiters.  We make 5 background
-# psql sessions each waiting for a corresponding insertion.  When waiting is
-# finished, stored procedures logs if there are visible as many rows as
-# should be.
-$node_primary->safe_psql(
-	'postgres', qq[
-CREATE FUNCTION log_count(i int) RETURNS void AS \$\$
-  DECLARE
-    count int;
-  BEGIN
-    SELECT count(*) FROM wait_test INTO count;
-    IF count >= 31 + i THEN
-      RAISE LOG 'count %', i;
-    END IF;
-  END
-\$\$
-LANGUAGE plpgsql;
-]);
-$node_standby->safe_psql('postgres', "SELECT pg_wal_replay_pause();");
-my @psql_sessions;
-for (my $i = 0; $i < 5; $i++)
-{
-	$node_primary->safe_psql('postgres',
-		"INSERT INTO wait_test VALUES (${i});");
-	my $lsn =
-	  $node_primary->safe_psql('postgres',
-		"SELECT pg_current_wal_insert_lsn()");
-	$psql_sessions[$i] = $node_standby->background_psql('postgres');
-	$psql_sessions[$i]->query_until(
-		qr/start/, qq[
-		\\echo start
-		CALL pg_wal_replay_wait('${lsn}');
-		SELECT log_count(${i});
-	]);
-}
-my $log_offset = -s $node_standby->logfile;
-$node_standby->safe_psql('postgres', "SELECT pg_wal_replay_resume();");
-for (my $i = 0; $i < 5; $i++)
-{
-	$node_standby->wait_for_log("count ${i}", $log_offset);
-	$psql_sessions[$i]->quit;
-}
-
-ok(1, 'multiple LSN waiters reported consistent data');
-
-# 6. Check that the standby promotion terminates the wait on LSN.  Start
-# waiting for an unreachable LSN then promote.  Check the log for the relevant
-# error message.  Also, check that waiting for already replayed LSN doesn't
-# cause an error even after promotion.
-my $lsn4 =
-  $node_primary->safe_psql('postgres',
-	"SELECT pg_current_wal_insert_lsn() + 10000000000");
-my $lsn5 =
-  $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
-my $psql_session = $node_standby->background_psql('postgres');
-$psql_session->query_until(
-	qr/start/, qq[
-	\\echo start
-	CALL pg_wal_replay_wait('${lsn4}');
-]);
-
-# Make sure standby will be promoted at least at the primary insert LSN we
-# have just observed.  Use pg_switch_wal() to force the insert LSN to be
-# written then wait for standby to catchup.
-$node_primary->safe_psql('postgres', 'SELECT pg_switch_wal();');
-$node_primary->wait_for_catchup($node_standby);
-
-$log_offset = -s $node_standby->logfile;
-$node_standby->promote;
-$node_standby->wait_for_log('recovery is not in progress', $log_offset);
-
-ok(1, 'got error after standby promote');
-
-$node_standby->safe_psql('postgres', "CALL pg_wal_replay_wait('${lsn5}');");
-
-ok(1, 'wait for already replayed LSN exits immediately even after promotion');
-
-$output = $node_standby->safe_psql(
-	'postgres', qq[
-	CALL pg_wal_replay_wait('${lsn4}', 10, true);
-	SELECT pg_wal_replay_wait_status();]);
-ok( $output eq "not in recovery",
-	"pg_wal_replay_wait_status() returns correct status after standby promotion"
-);
-
-$node_standby->stop;
-$node_primary->stop;
-
-# If we send \q with $psql_session->quit the command can be sent to the session
-# already closed. So \q is in initial script, here we only finish IPC::Run.
-$psql_session->{run}->finish;
-
-done_testing();