Re: Tests for psql \g and \o
On Wed, Nov 30, 2022 at 12:33:59PM -0600, Justin Pryzby wrote: > I think you could do that with a perl 0-liner. Right. And this could do something similar to 025_stuck_on_old_timeline.pl in terms of finding the binary for perl. -- Michael signature.asc Description: PGP signature
Re: Tests for psql \g and \o
On Wed, Nov 30, 2022 at 02:50:16PM +0900, Michael Paquier wrote: > On Wed, Nov 23, 2022 at 09:18:57PM +0100, Daniel Verite wrote: > > PFA a new patch addressing these issues. > > Thanks, the tests part of the main regression test suite look good to > me, so I have applied them after fixing a few typos and tweaking the > style of the test. Regarding the tests with pipes, I had cold feet > with the dependencies on cat for non-WIN32 or findstr for WIN32. I think you could do that with a perl 0-liner. $ echo foo |perl -pe '' foo -- Justin
Re: Tests for psql \g and \o
Michael Paquier wrote: > Thanks, the tests part of the main regression test suite look good to > me, so I have applied them after fixing a few typos and tweaking the > style of the test. Thanks! > Regarding the tests with pipes, I had cold feet with the > dependencies on cat for non-WIN32 or findstr for WIN32. OK. If the issue is that these programs might be missing, I guess we could check that beforehand with IPC::Run and skip the corresponding psql tests if they're not available or not working as expected. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
Re: Tests for psql \g and \o
On Wed, Nov 23, 2022 at 09:18:57PM +0100, Daniel Verite wrote: > PFA a new patch addressing these issues. Thanks, the tests part of the main regression test suite look good to me, so I have applied them after fixing a few typos and tweaking the style of the test. Regarding the tests with pipes, I had cold feet with the dependencies on cat for non-WIN32 or findstr for WIN32. cat is used in the kerberos and ldap tests, though I am wondering whether we shouldn't take an approach similar to other tests where the command may not exist, and where we should check if there is something in the environment.. -- Michael signature.asc Description: PGP signature
Re: Tests for psql \g and \o
Michael Paquier wrote:
> +psql_like($node, "SELECT 'one' \\g | cat >$g_file", qr//, "one command
> \\g");
> +my $c1 = slurp_file($g_file);
> +like($c1, qr/one/);
>
> Windows may not have an equivalent for "cat", no? Note that psql's
> 001_basic.pl has no restriction in place for Windows. Perhaps you
> could use the same trick as basebackup_to_shell, where GZIP is used to
> write some arbitrary data.. Anyway, this has some quoting issues
> especially if the file's path has whitespaces? This is located in
> File::Temp::tempdir, still it does not sound like a good thing to rely
> on this assumption on portability grounds.
PFA a new patch addressing these issues.
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index f447845717..6fcbdeff7d 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,24 @@ is($row_count, '10',
'client-side error commits transaction, no ON_ERROR_STOP and multiple
-c switches'
);
+# Test \g output piped into a program
+my $g_file = "$tempdir/g_file_1.out";
+# on Windows, findstr "^" with any text on stdin will copy it to stdout
+my $pipe_cmd = $windows_os ? "findstr \"^\" > \"$g_file\"" : "cat >
\"$g_file\"";
+
+psql_like($node, "SELECT 'one' \\g | $pipe_cmd", qr//, "one command \\g");
+my $c1 = slurp_file($g_file);
+like($c1, qr/one/);
+
+psql_like($node, "SELECT 'two' \\; SELECT 'three' \\g | $pipe_cmd", qr//, "two
commands \\g");
+my $c2 = slurp_file($g_file);
+like($c2, qr/two.*three/s);
+
+
+psql_like($node, "\\set SHOW_ALL_RESULTS 0\nSELECT 'four' \\; SELECT 'five'
\\g | $pipe_cmd", qr//,
+ "two commands \\g with only last result");
+my $c3 = slurp_file($g_file);
+like($c3, qr/five/);
+unlike($c3, qr/four/);
+
done_testing();
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index 5bdae290dc..29bc07c1b2 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5433,6 +5433,136 @@ CONTEXT: PL/pgSQL function warn(text) line 2 at RAISE
\set SHOW_ALL_RESULTS on
DROP FUNCTION warn(TEXT);
--
+-- \g file
+--
+\getenv abs_builddir PG_ABS_BUILDDIR
+\set outfile1 :abs_builddir '/results/psql-output1'
+-- this table is used to load back the output data from files
+CREATE TEMPORARY TABLE reload_output(
+ lineno int not null generated always as identity,
+ line text
+);
+SELECT 1 AS a \g :outfile1
+COPY reload_output(line) FROM :'outfile1';
+SELECT 2 AS b\; SELECT 3 AS c\; SELECT 4 AS d \g :outfile1
+COPY reload_output(line) FROM :'outfile1';
+COPY (select 'foo') to stdout \; COPY (select 'bar') to stdout \g :outfile1
+COPY reload_output(line) FROM :'outfile1';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+-
+ a
+ ---
+ 1
+ (1 row)
+
+ b
+ ---
+ 2
+ (1 row)
+
+ c
+ ---
+ 3
+ (1 row)
+
+ d
+ ---
+ 4
+ (1 row)
+
+ foo
+ bar
+(22 rows)
+
+TRUNCATE TABLE reload_output;
+--
+-- \o file
+--
+\set outfile2 :abs_builddir '/results/psql-output2'
+\o :outfile2
+select max(unique1) from onek;
+SELECT 1 AS a\; SELECT 2 AS b\; SELECT 3 AS c;
+-- COPY TO file
+-- the data goes into :outfile1 and the command status into :outfile2
+\set QUIET false
+COPY (select unique1 from onek order by unique1 limit 10) TO :'outfile1';
+-- DML command status
+UPDATE onek SET unique1=unique1 WHERE false;
+\set QUIET true
+\o
+COPY reload_output(line) FROM :'outfile1';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+(10 rows)
+
+TRUNCATE TABLE reload_output;
+COPY reload_output(line) FROM :'outfile2';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ max
+ -
+ 999
+ (1 row)
+
+ a
+ ---
+ 1
+ (1 row)
+
+ b
+ ---
+ 2
+ (1 row)
+
+ c
+ ---
+ 3
+ (1 row)
+
+ COPY 10
+ UPDATE 0
+(22 rows)
+
+TRUNCATE TABLE reload_output;
+\o :outfile2
+-- multiple COPY TO stdout
+-- the data go into :outfile2 and the status is not output
+COPY (select 'foo1') to stdout \; COPY (select 'bar1') to stdout;
+-- combine \o and \g file with multiple COPY queries
+COPY (select 'foo2') to stdout \; COPY (select 'bar2') to stdout \g :outfile1
+\o
+COPY reload_output(line) FROM :'outfile1';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ foo2
+ bar2
+(2 rows)
+
+TRUNCATE TABLE reload_output;
+COPY reload_output(line) FROM :'outfile2';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ foo1
+ bar1
+(2 rows)
+
+TRUNCATE TABLE reload_output;
+--
-- AUTOCOMMIT and combined queries
--
\set AUTOCOMMIT off
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 8732017e51..ab1f71aa77 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1379,6 +1379,72 @@ SELECT 1 AS one \; SELECT warn('1.5') \; SELECT 2 AS two
;
\set SHOW_ALL_RESULTS on
DROP FUNCTION warn(TEXT);
+--
+-- \g file
Re: Tests for psql \g and \o
On Tue, Nov 01, 2022 at 12:42:47PM +0100, Daniel Verite wrote: > It's a follow up to the discussion at [1]. Since this discussion > already has a slot in the CF [2] with a committed patch, let's start a > new separate thread. +psql_like($node, "SELECT 'one' \\g | cat >$g_file", qr//, "one command \\g"); +my $c1 = slurp_file($g_file); +like($c1, qr/one/); Windows may not have an equivalent for "cat", no? Note that psql's 001_basic.pl has no restriction in place for Windows. Perhaps you could use the same trick as basebackup_to_shell, where GZIP is used to write some arbitrary data.. Anyway, this has some quoting issues especially if the file's path has whitespaces? This is located in File::Temp::tempdir, still it does not sound like a good thing to rely on this assumption on portability grounds. -- Michael signature.asc Description: PGP signature
Tests for psql \g and \o
Hi,
Here's a patch adding regression tests for \g and \o, and TAP tests
for \g | program,
It's a follow up to the discussion at [1]. Since this discussion
already has a slot in the CF [2] with a committed patch, let's start a
new separate thread.
[1]
https://www.postgresql.org/message-id/4333844c-2244-4d6e-a49a-1d483fbe3...@manitou-mail.org
[2] https://commitfest.postgresql.org/40/3923/
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index f447845717..2621f55a17 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,24 @@ is($row_count, '10',
'client-side error commits transaction, no ON_ERROR_STOP and multiple
-c switches'
);
+# Test \g output piped into a program
+my $g_file = "$tempdir/g_file_1.out";
+# on Windows, findstr "^" with any text on stdin will copy it to stdout
+my $pipe_cmd = $windows_os ? "findstr \"^\" > $g_file" : "cat > $g_file";
+
+psql_like($node, "SELECT 'one' \\g | cat >$g_file", qr//, "one command \\g");
+my $c1 = slurp_file($g_file);
+like($c1, qr/one/);
+
+psql_like($node, "SELECT 'two' \\; SELECT 'three' \\g | cat >$g_file", qr//,
"two commands \\g");
+my $c2 = slurp_file($g_file);
+like($c2, qr/two.*three/s);
+
+
+psql_like($node, "\\set SHOW_ALL_RESULTS 0\nSELECT 'four' \\; SELECT 'five'
\\g | cat >$g_file", qr//,
+ "two commands \\g with only last result");
+my $c3 = slurp_file($g_file);
+like($c3, qr/five/);
+unlike($c3, qr/four/);
+
done_testing();
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index a7f5700edc..90beedde58 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5402,6 +5402,136 @@ CONTEXT: PL/pgSQL function warn(text) line 2 at RAISE
\set SHOW_ALL_RESULTS on
DROP FUNCTION warn(TEXT);
--
+-- \g file
+--
+\getenv abs_builddir PG_ABS_BUILDDIR
+\set outfile1 :abs_builddir '/results/psql-output1'
+-- this table is used to load back the output data from files
+CREATE TEMPORARY TABLE reload_output(
+ lineno int not null generated always as identity,
+ line text
+);
+SELECT 1 AS a \g :outfile1
+COPY reload_output(line) FROM :'outfile1';
+SELECT 2 AS b\; SELECT 3 AS c\; SELECT 4 AS d \g :outfile1
+COPY reload_output(line) FROM :'outfile1';
+COPY (select 'foo') to stdout \; COPY (select 'bar') to stdout \g :outfile1
+COPY reload_output(line) FROM :'outfile1';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+-
+ a
+ ---
+ 1
+ (1 row)
+
+ b
+ ---
+ 2
+ (1 row)
+
+ c
+ ---
+ 3
+ (1 row)
+
+ d
+ ---
+ 4
+ (1 row)
+
+ foo
+ bar
+(22 rows)
+
+TRUNCATE TABLE reload_output;
+--
+-- \o file
+--
+\set outfile2 :abs_builddir '/results/psql-output2'
+\o :outfile2
+select max(unique1) from onek;
+SELECT 1 AS a\; SELECT 2 AS b\; SELECT 3 AS c;
+-- COPY TO file
+-- the data goes into :outfile1 and the command status into :outfile2
+\set QUIET false
+COPY (select unique1 from onek order by unique1 limit 10) TO :'outfile1';
+-- DML command status
+UPDATE onek SET unique1=unique1 WHERE false;
+\set QUIET true
+\o
+COPY reload_output(line) FROM :'outfile1';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+(10 rows)
+
+TRUNCATE TABLE reload_output;
+COPY reload_output(line) FROM :'outfile2';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ max
+ -
+ 999
+ (1 row)
+
+ a
+ ---
+ 1
+ (1 row)
+
+ b
+ ---
+ 2
+ (1 row)
+
+ c
+ ---
+ 3
+ (1 row)
+
+ COPY 10
+ UPDATE 0
+(22 rows)
+
+TRUNCATE TABLE reload_output;
+\o :outfile2
+-- multiple COPY TO stdout
+-- the data go into :outfile2 and the status is not output
+COPY (select 'foo1') to stdout \; COPY (select 'bar1') to stdout;
+-- combine \o and \g file with multiple COPY queries
+COPY (select 'foo2') to stdout \; COPY (select 'bar2') to stdout \g :outfile1
+\o
+COPY reload_output(line) FROM :'outfile1';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ foo2
+ bar2
+(2 rows)
+
+TRUNCATE TABLE reload_output;
+COPY reload_output(line) FROM :'outfile2';
+SELECT line FROM reload_output ORDER BY lineno;
+ line
+--
+ foo1
+ bar1
+(2 rows)
+
+TRUNCATE TABLE reload_output;
+--
-- AUTOCOMMIT and combined queries
--
\set AUTOCOMMIT off
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1149c6a839..c3ae21633d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1365,6 +1365,72 @@ SELECT 1 AS one \; SELECT warn('1.5') \; SELECT 2 AS two
;
\set SHOW_ALL_RESULTS on
DROP FUNCTION warn(TEXT);
+--
+-- \g file
+--
+\getenv abs_builddir PG_ABS_BUILDDIR
+\set outfile1 :abs_builddir '/results/psql-output1'
+
+-- this table is used to load back the output data from files
+CREATE TEMPORARY TABLE reload_output(
+ lineno int not null generated always as identity,
+ line text
+);
+
+SELECT 1 AS a \g
