Improve psql's ability to select pager mode accurately.

We try to use the pager only when more than a screenful's worth of
data is to be printed.  However, the code in print.c that's concerned
with counting the number of lines that will be needed missed a lot of
edge cases:

* While plain aligned mode accounted for embedded newlines in column
headers and table cells, unaligned and vertical output modes did not.

* In particular, since vertical mode repeats the headers for each
record, we need to account for embedded newlines in the headers for
each record.

* Multi-line table titles were not accounted for.

* tuples_only mode (where headers aren't printed) wasn't accounted
for.

* Footers were accounted for as one line per footer, again missing
the possibility of multi-line footers.  (In some cases such as
"\d+" on a view, there can be many lines in a footer.)  Also,
we failed to account for the default footer.

To fix, move the entire responsibility for counting lines into
IsPagerNeeded (or actually, into a new subroutine count_table_lines),
and then expand the logic as appropriate.  Also restructure to make it
perhaps a bit easier to follow.  It's still only completely accurate
for ALIGNED/WRAPPED/UNALIGNED formats, but the other formats are not
typically used with interactive output.

Arrange to not run count_table_lines at all unless we will use
its result, and teach it to quit early as soon as it's proven
that the output is long enough to require use of the pager.
When dealing with large tables this should save a noticeable
amount of time, since pg_wcssize() isn't exactly cheap.

In passing, move the "flog" output step to the bottom of printTable(),
rather than running it when we've already opened the pager in some
modes.  In principle it shouldn't interfere with the pager because
flog should always point to a non-interactive file; but it seems silly
to risk any interference, especially when the existing positioning
seems to have been chosen with the aid of a dartboard.

Also add a TAP test to exercise pager mode.  Up to now, we have had
zero test coverage of these code paths, because they aren't reached
unless isatty(stdout).  We do have the test infrastructure to improve
that situation, though.  Following the lead of 010_tab_completion.pl,
set up an interactive psql and feed it some test cases.  To detect
whether it really did invoke the pager, point PSQL_PAGER to "wc -l".
The test is skipped if that utility isn't available.

Author: Erik Wienhold <ewie@ewie.name>
Test-authored-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/2dd2430f-dd20-4c89-97fd-242616a3d768@ewie.name

2 files changed, 105 insertions, 0 deletions

diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build
index f795ff28271..d344053c23b 100644
--- a/src/bin/psql/meson.build
+++ b/src/bin/psql/meson.build
@@ -77,6 +77,7 @@ tests += {
       't/001_basic.pl',
       't/010_tab_completion.pl',
       't/020_cancel.pl',
+      't/030_pager.pl',
     ],
   },
 }
diff --git a/src/bin/psql/t/030_pager.pl b/src/bin/psql/t/030_pager.pl
new file mode 100644
index 00000000000..dd2be340e55
--- /dev/null
+++ b/src/bin/psql/t/030_pager.pl
@@ -0,0 +1,104 @@
+
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Data::Dumper;
+
+# If we don't have IO::Pty, forget it, because IPC::Run depends on that
+# to support pty connections
+eval { require IO::Pty; };
+if ($@)
+{
+	plan skip_all => 'IO::Pty is needed to run this test';
+}
+
+# Check that "wc -l" does what we expect, else forget it
+my $wcstdin = "foo bar\nbaz\n";
+my ($wcstdout, $wcstderr);
+my $result = IPC::Run::run [ 'wc', '-l' ],
+  '<' => \$wcstdin,
+  '>' => \$wcstdout,
+  '2>' => \$wcstderr;
+chomp $wcstdout;
+if ($wcstdout ne '2' || $wcstderr ne '')
+{
+	plan skip_all => '"wc -l" is needed to run this test';
+}
+
+# We set up "wc -l" as the pager so we can tell whether psql used the pager
+$ENV{PSQL_PAGER} = "wc -l";
+
+# start a new server
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+# fire up an interactive psql session
+my $h = $node->interactive_psql('postgres');
+
+# set the pty's window size to known values
+# (requires undesirable chumminess with the innards of IPC::Run)
+for my $pty (values %{ $h->{run}->{PTYS} })
+{
+	$pty->set_winsize(24, 80);
+}
+
+# Simple test case: type something and see if psql responds as expected
+sub do_command
+{
+	my ($send, $pattern, $annotation) = @_;
+
+	# report test failures from caller location
+	local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+	# restart per-command timer
+	$h->{timeout}->start($PostgreSQL::Test::Utils::timeout_default);
+
+	# send the data to be sent and wait for its result
+	my $out = $h->query_until($pattern, $send);
+	my $okay = ($out =~ $pattern && !$h->{timeout}->is_expired);
+	ok($okay, $annotation);
+	# for debugging, log actual output if it didn't match
+	local $Data::Dumper::Terse = 1;
+	local $Data::Dumper::Useqq = 1;
+	diag 'Actual output was ' . Dumper($out) . "Did not match \"$pattern\"\n"
+	  if !$okay;
+	return;
+}
+
+# Test invocation of the pager
+#
+# Note that interactive_psql starts psql with --no-align --tuples-only,
+# and that the output string will include psql's prompts and command echo.
+
+do_command(
+	"SELECT 'test' AS t FROM generate_series(1,23);\n",
+	qr/^test\r?$/m,
+	"execute SELECT query that needs no pagination");
+
+do_command(
+	"SELECT 'test' AS t FROM generate_series(1,24);\n",
+	qr/^24\r?$/m,
+	"execute SELECT query that needs pagination");
+
+do_command(
+	"\\pset expanded\nSELECT generate_series(1,20) as g;\n",
+	qr/^39\r?$/m,
+	"execute SELECT query that needs pagination in expanded mode");
+
+do_command(
+	"\\pset tuples_only off\n\\d+ information_schema.referential_constraints\n",
+	qr/^\d+\r?$/m,
+	"execute command with footer that needs pagination");
+
+# send psql an explicit \q to shut it down, else pty won't close properly
+$h->quit or die "psql returned $?";
+
+# done
+$node->stop;
+done_testing();