v8-0002-Tweak-pg_walinspect-tests-to-be-consistent.patch

From d58a3e4a6eaaba39e658e20464f61c96b1a09890 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 14 Mar 2023 04:42:59 +0000
Subject: [PATCH v8] Tweak pg_walinspect tests to be consistent

---
 .../pg_walinspect/expected/oldextversions.out | 18 ++++----
 .../pg_walinspect/expected/pg_walinspect.out  | 41 +++++++++---------
 contrib/pg_walinspect/sql/oldextversions.sql  | 18 ++++----
 contrib/pg_walinspect/sql/pg_walinspect.sql   | 42 ++++++++++---------
 4 files changed, 60 insertions(+), 59 deletions(-)

diff --git a/contrib/pg_walinspect/expected/oldextversions.out b/contrib/pg_walinspect/expected/oldextversions.out
index 9195ab312f..026ca07108 100644
--- a/contrib/pg_walinspect/expected/oldextversions.out
+++ b/contrib/pg_walinspect/expected/oldextversions.out
@@ -1,8 +1,8 @@
--- test old extension version entry points
+-- Test old extension version entry points.
 CREATE EXTENSION pg_walinspect WITH VERSION '1.0';
--- Mask DETAIL messages as these could refer to current LSN positions
+-- Mask DETAIL messages as these could refer to current LSN positions.
 \set VERBOSITY terse
--- List what version 1.0 contains
+-- List what version 1.0 contains.
 \dx+ pg_walinspect
            Objects in extension "pg_walinspect"
                     Object description                     
@@ -14,7 +14,7 @@ CREATE EXTENSION pg_walinspect WITH VERSION '1.0';
  function pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean)
 (5 rows)
 
--- Make sure checkpoints don't interfere with the test
+-- Make sure checkpoints don't interfere with the test.
 SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot_old_ext_ver', true, false);
  ?column? 
 ----------
@@ -24,7 +24,7 @@ SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_sl
 CREATE TABLE sample_tbl(col1 int, col2 int);
 SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
 INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
--- Tests for the past functions
+-- Tests for the past functions.
 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1');
  ok 
 ----
@@ -37,14 +37,14 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1');
  t
 (1 row)
 
--- Failures with start LSNs
+-- Failure with highest possible start LSNs.
 SELECT * FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF');
 ERROR:  WAL start LSN must be less than current LSN
 SELECT * FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF');
 ERROR:  WAL start LSN must be less than current LSN
--- Move to new version 1.1
+-- Move to new version 1.1.
 ALTER EXTENSION pg_walinspect UPDATE TO '1.1';
--- List what version 1.1 contains
+-- List what version 1.1 contains.
 \dx+ pg_walinspect
        Objects in extension "pg_walinspect"
                 Object description                
@@ -55,7 +55,7 @@ ALTER EXTENSION pg_walinspect UPDATE TO '1.1';
  function pg_get_wal_stats(pg_lsn,pg_lsn,boolean)
 (4 rows)
 
--- Clean up
+-- Clean up.
 DROP EXTENSION pg_walinspect;
 SELECT pg_drop_replication_slot('regress_pg_walinspect_slot_old_ext_ver');
  pg_drop_replication_slot 
diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out
index 1d4397812a..710d1a2859 100644
--- a/contrib/pg_walinspect/expected/pg_walinspect.out
+++ b/contrib/pg_walinspect/expected/pg_walinspect.out
@@ -9,7 +9,6 @@ SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_sl
 (1 row)
 
 CREATE TABLE sample_tbl(col1 int, col2 int);
--- Save some LSNs for comparisons
 SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
 INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
 SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
@@ -34,57 +33,57 @@ SELECT * FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1');
 ERROR:  WAL start LSN must be less than end LSN
 SELECT * FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1');
 ERROR:  WAL start LSN must be less than end LSN
--- LSNs with the highest value possible.
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
+-- Failure with highest possible input LSN.
+SELECT  * FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
 ERROR:  WAL input LSN must be less than current LSN
--- successes with end LSNs
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
+-- Failures with highest possible start LSNs.
+SELECT * FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+ERROR:  WAL start LSN must be less than current LSN
+SELECT * FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+ERROR:  WAL start LSN must be less than current LSN
+SELECT * FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+ERROR:  WAL start LSN must be less than current LSN
+-- Successes with highest possible end LSNs.
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
  ok 
 ----
  t
 (1 row)
 
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
  ok 
 ----
  t
 (1 row)
 
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
  ok 
 ----
  t
 (1 row)
 
--- failures with start LSNs
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
-ERROR:  WAL start LSN must be less than current LSN
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
-ERROR:  WAL start LSN must be less than current LSN
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
-ERROR:  WAL start LSN must be less than current LSN
 -- ===================================================================
 -- Tests for all function executions
 -- ===================================================================
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
  ok 
 ----
  t
 (1 row)
 
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
  ok 
 ----
  t
 (1 row)
 
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
  ok 
 ----
  t
 (1 row)
 
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
  ok 
 ----
  t
@@ -115,7 +114,7 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
 -- ===================================================================
 -- Tests to get block information from WAL record
 -- ===================================================================
--- Update table to generate some block data
+-- Update table to generate some block data.
 SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
 UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 1;
 SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
@@ -172,7 +171,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
  f
 (1 row)
 
--- Functions accessible by users with role pg_read_server_files
+-- Functions accessible by users with role pg_read_server_files.
 GRANT pg_read_server_files TO regress_pg_walinspect;
 SELECT has_function_privilege('regress_pg_walinspect',
   'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
@@ -203,7 +202,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
 (1 row)
 
 REVOKE pg_read_server_files FROM regress_pg_walinspect;
--- Superuser can grant execute to other users
+-- Superuser can grant execute to other users.
 GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
   TO regress_pg_walinspect;
 GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
diff --git a/contrib/pg_walinspect/sql/oldextversions.sql b/contrib/pg_walinspect/sql/oldextversions.sql
index 3037a193de..70ee4516ad 100644
--- a/contrib/pg_walinspect/sql/oldextversions.sql
+++ b/contrib/pg_walinspect/sql/oldextversions.sql
@@ -1,35 +1,35 @@
--- test old extension version entry points
+-- Test old extension version entry points.
 
 CREATE EXTENSION pg_walinspect WITH VERSION '1.0';
 
--- Mask DETAIL messages as these could refer to current LSN positions
+-- Mask DETAIL messages as these could refer to current LSN positions.
 \set VERBOSITY terse
 
--- List what version 1.0 contains
+-- List what version 1.0 contains.
 \dx+ pg_walinspect
 
--- Make sure checkpoints don't interfere with the test
+-- Make sure checkpoints don't interfere with the test.
 SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot_old_ext_ver', true, false);
 
 CREATE TABLE sample_tbl(col1 int, col2 int);
 SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
 INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
 
--- Tests for the past functions
+-- Tests for the past functions.
 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1');
 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1');
 
--- Failures with start LSNs
+-- Failure with highest possible start LSNs.
 SELECT * FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF');
 SELECT * FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF');
 
--- Move to new version 1.1
+-- Move to new version 1.1.
 ALTER EXTENSION pg_walinspect UPDATE TO '1.1';
 
--- List what version 1.1 contains
+-- List what version 1.1 contains.
 \dx+ pg_walinspect
 
--- Clean up
+-- Clean up.
 DROP EXTENSION pg_walinspect;
 
 SELECT pg_drop_replication_slot('regress_pg_walinspect_slot_old_ext_ver');
diff --git a/contrib/pg_walinspect/sql/pg_walinspect.sql b/contrib/pg_walinspect/sql/pg_walinspect.sql
index 27ee1d90b5..863dc33710 100644
--- a/contrib/pg_walinspect/sql/pg_walinspect.sql
+++ b/contrib/pg_walinspect/sql/pg_walinspect.sql
@@ -7,8 +7,6 @@ CREATE EXTENSION pg_walinspect;
 SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false);
 
 CREATE TABLE sample_tbl(col1 int, col2 int);
-
--- Save some LSNs for comparisons
 SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
 INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
 SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
@@ -31,25 +29,27 @@ SELECT * FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1');
 SELECT * FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1');
 SELECT * FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1');
 
--- LSNs with the highest value possible.
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
--- successes with end LSNs
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
--- failures with start LSNs
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+-- Failure with highest possible input LSN.
+SELECT  * FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
+
+-- Failures with highest possible start LSNs.
+SELECT * FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+SELECT * FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+SELECT * FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
+
+-- Successes with highest possible end LSNs.
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
 
 -- ===================================================================
 -- Tests for all function executions
 -- ===================================================================
 
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
-SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
 
 -- ===================================================================
 -- Test for filtering out WAL records of a particular table
@@ -72,10 +72,11 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
 -- Tests to get block information from WAL record
 -- ===================================================================
 
--- Update table to generate some block data
+-- Update table to generate some block data.
 SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
 UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 1;
 SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
+
 -- Check if we get block data from WAL record.
 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn3', :'wal_lsn4')
   WHERE relfilenode = :'sample_tbl_oid' AND blockdata IS NOT NULL;
@@ -85,6 +86,7 @@ SELECT pg_current_wal_lsn() AS wal_lsn5 \gset
 CHECKPOINT;
 UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 2;
 SELECT pg_current_wal_lsn() AS wal_lsn6 \gset
+
 -- Check if we get FPI from WAL record.
 SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn5', :'wal_lsn6')
   WHERE relfilenode = :'sample_tbl_oid' AND fpi IS NOT NULL;
@@ -103,9 +105,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
 SELECT has_function_privilege('regress_pg_walinspect',
   'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
 
--- Functions accessible by users with role pg_read_server_files
-
+-- Functions accessible by users with role pg_read_server_files.
 GRANT pg_read_server_files TO regress_pg_walinspect;
+
 SELECT has_function_privilege('regress_pg_walinspect',
   'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
 SELECT has_function_privilege('regress_pg_walinspect',
@@ -117,7 +119,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
 
 REVOKE pg_read_server_files FROM regress_pg_walinspect;
 
--- Superuser can grant execute to other users
+-- Superuser can grant execute to other users.
 GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
   TO regress_pg_walinspect;
 GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
-- 
2.34.1