On 22.07.21 16:58, Fabien COELHO wrote:
Ok. I noticed. The patch got significantly broken by the watch pager
commit. I also have to enhance the added tests (per Peter request).
I wrote a test to check psql query cancel support. I checked that it
fails against the patch that was reverted. Maybe this is useful.
Here is the updated version (v8? I'm not sure what the right count is),
which works for me and for "make check", including some tests added for
uncovered paths.
I was looking at adding test coverage for the issue complained about in
[0]. That message said that the autocommit logic was broken, but
actually the issue was with the ON_ERROR_ROLLBACK logic. However, it
turned out that neither feature had any test coverage, and they are
easily testable using the pg_regress setup, so I wrote tests for both
and another little thing I found nearby.
It turns out that your v8 patch still has the issue complained about in
[0]. The issue is that after COMMIT AND CHAIN, the internal savepoint
is gone, but the patched psql still thinks it should be there and tries
to release it, which leads to errors.
[0]: https://www.postgresql.org/message-id/2671235.1618154...@sss.pgh.pa.us
>From c6042975c9cb802c5e6017c5f2452cea21beac1e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 24 Sep 2021 14:27:35 +0200
Subject: [PATCH] psql: Add various tests
Add tests for psql features
- AUTOCOMMIT
- ON_ERROR_ROLLBACK
- ECHO errors
---
src/test/regress/expected/psql.out | 99 ++++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 63 +++++++++++++++++++
2 files changed, 162 insertions(+)
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index 1b2f6bc418..930ce8597a 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5179,3 +5179,102 @@ List of access methods
pg_catalog | && | anyarray | anyarray | boolean | overlaps
(1 row)
+-- AUTOCOMMIT
+CREATE TABLE ac_test (a int);
+\set AUTOCOMMIT off
+INSERT INTO ac_test VALUES (1);
+COMMIT;
+SELECT * FROM ac_test;
+ a
+---
+ 1
+(1 row)
+
+COMMIT;
+INSERT INTO ac_test VALUES (2);
+ROLLBACK;
+SELECT * FROM ac_test;
+ a
+---
+ 1
+(1 row)
+
+COMMIT;
+BEGIN;
+INSERT INTO ac_test VALUES (3);
+COMMIT;
+SELECT * FROM ac_test;
+ a
+---
+ 1
+ 3
+(2 rows)
+
+COMMIT;
+BEGIN;
+INSERT INTO ac_test VALUES (4);
+ROLLBACK;
+SELECT * FROM ac_test;
+ a
+---
+ 1
+ 3
+(2 rows)
+
+COMMIT;
+\set AUTOCOMMIT on
+DROP TABLE ac_test;
+SELECT * FROM ac_test; -- should be gone now
+ERROR: relation "ac_test" does not exist
+LINE 1: SELECT * FROM ac_test;
+ ^
+-- ON_ERROR_ROLLBACK
+\set ON_ERROR_ROLLBACK on
+CREATE TABLE oer_test (a int);
+BEGIN;
+INSERT INTO oer_test VALUES (1);
+INSERT INTO oer_test VALUES ('foo');
+ERROR: invalid input syntax for type integer: "foo"
+LINE 1: INSERT INTO oer_test VALUES ('foo');
+ ^
+INSERT INTO oer_test VALUES (3);
+COMMIT;
+SELECT * FROM oer_test;
+ a
+---
+ 1
+ 3
+(2 rows)
+
+BEGIN;
+INSERT INTO oer_test VALUES (4);
+ROLLBACK;
+SELECT * FROM oer_test;
+ a
+---
+ 1
+ 3
+(2 rows)
+
+BEGIN;
+INSERT INTO oer_test VALUES (5);
+COMMIT AND CHAIN;
+INSERT INTO oer_test VALUES (6);
+COMMIT;
+SELECT * FROM oer_test;
+ a
+---
+ 1
+ 3
+ 5
+ 6
+(4 rows)
+
+DROP TABLE oer_test;
+\set ON_ERROR_ROLLBACK off
+-- ECHO errors
+\set ECHO errors
+ERROR: relation "notexists" does not exist
+LINE 1: SELECT * FROM notexists;
+ ^
+STATEMENT: SELECT * FROM notexists;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 68121d171c..e9d504baf2 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1241,3 +1241,66 @@ CREATE MATERIALIZED VIEW mat_view_heap_psql USING
heap_psql AS SELECT f1 from tb
\dfa bit* small*
\do - pg_catalog.int4
\do && anyarray *
+
+-- AUTOCOMMIT
+
+CREATE TABLE ac_test (a int);
+\set AUTOCOMMIT off
+
+INSERT INTO ac_test VALUES (1);
+COMMIT;
+SELECT * FROM ac_test;
+COMMIT;
+
+INSERT INTO ac_test VALUES (2);
+ROLLBACK;
+SELECT * FROM ac_test;
+COMMIT;
+
+BEGIN;
+INSERT INTO ac_test VALUES (3);
+COMMIT;
+SELECT * FROM ac_test;
+COMMIT;
+
+BEGIN;
+INSERT INTO ac_test VALUES (4);
+ROLLBACK;
+SELECT * FROM ac_test;
+COMMIT;
+
+\set AUTOCOMMIT on
+DROP TABLE ac_test;
+SELECT * FROM ac_test; -- should be gone now
+
+-- ON_ERROR_ROLLBACK
+
+\set ON_ERROR_ROLLBACK on
+CREATE TABLE oer_test (a int);
+
+BEGIN;
+INSERT INTO oer_test VALUES (1);
+INSERT INTO oer_test VALUES ('foo');
+INSERT INTO oer_test VALUES (3);
+COMMIT;
+SELECT * FROM oer_test;
+
+BEGIN;
+INSERT INTO oer_test VALUES (4);
+ROLLBACK;
+SELECT * FROM oer_test;
+
+BEGIN;
+INSERT INTO oer_test VALUES (5);
+COMMIT AND CHAIN;
+INSERT INTO oer_test VALUES (6);
+COMMIT;
+SELECT * FROM oer_test;
+
+DROP TABLE oer_test;
+\set ON_ERROR_ROLLBACK off
+
+-- ECHO errors
+\set ECHO errors
+SELECT * FROM notexists;
+\set ECHO none
--
2.33.0
