Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 13 Jun 2022 15:45:05 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>,
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	christoph.berg@credativ.de, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17504: psql --single-transaction -vON_ERROR_STOP=1 still
 commits after client-side error
Message-ID: <Yqbc8bAdwnP02na4@paquier.xyz>
References: <YpTgRGknCLBopP8C@msg.df7cb.de>
 <20220531.113927.640026319524742851.horikyota.ntt@gmail.com>
 <YphNHbOkg22H7d52@paquier.xyz>
 <20220603.175603.682874096867945240.horikyota.ntt@gmail.com>
 <Yp1jUAVnyYfn80vu@paquier.xyz>
 <2649417.1654875709@sss.pgh.pa.us>
 <2656555.1654877531@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="dqInjKqejRLGEs+R"
Content-Disposition: inline
In-Reply-To: <2656555.1654877531@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/Yqbc8bAdwnP02na4%40paquier.xyz>
Precedence: bulk


--dqInjKqejRLGEs+R
Content-Type: multipart/mixed; boundary="efO4ZfgsfeOoD8n+"
Content-Disposition: inline


--efO4ZfgsfeOoD8n+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Jun 10, 2022 at 12:12:11PM -0400, Tom Lane wrote:
> Alternatively, one could argue that once we've decided to issue
> ROLLBACK, there's not really any reason to continue performing
> additional -c/-f actions, so that a sufficient fix would be to
> get rid of the loop's ON_ERROR_STOP conditionality:
>=20
> -            if (successResult !=3D EXIT_SUCCESS && pset.on_error_stop)
> +            if (successResult !=3D EXIT_SUCCESS)
>                  break;

This suggestion does not look right to me with --single-transaction.
If not using ON_ERROR_STOP, I think that we should still loop through
all the switches given by the caller and force a COMMIT all the time
because the intention is that we don't care about failures while
processing.  This gives me the attached as a result for HEAD, where we
would issue a ROLLBACK only when using ON_ERROR_STOP to stop
immediately if there is a backend-side error or a client-side error.
I have expanded the tests where not using ON_ERROR_STOP, with errors
triggered at the end of the set of switches.

Now, do we really want to introduce this new behavior on HEAD?  We are
post-beta so this does not me make me really comfortable if both
Robert and you don't like the change, even if the behavior of
--single-transaction/ON_ERROR_STOP on client-side failure is weird to
me and others from upthread.  If you'd prefer see the original logic
in place, I don't mind putting the code in its previous shape.
However, I would like to keep all those TAP tests because we have zero
coverage in this area, and we have the base file to be able to do so
now.
--
Michael

--efO4ZfgsfeOoD8n+
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="psql-on-error.patch"
Content-Transfer-Encoding: quoted-printable

diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 574f49d4c9..7c2f555f15 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -426,8 +426,13 @@ main(int argc, char *argv[])
=20
 		if (options.single_txn)
 		{
-			res =3D PSQLexec((successResult =3D=3D EXIT_SUCCESS) ?
-						   "COMMIT" : "ROLLBACK");
+			/*
+			 * Rollback the contents of the single transaction if the caller
+			 * has set ON_ERROR_STOP and one of the steps has failed.  This
+			 * check needs to match the one done a couple of lines above.
+			 */
+			res =3D PSQLexec((successResult !=3D EXIT_SUCCESS && pset.on_error_stop=
) ?
+						   "ROLLBACK" : "COMMIT");
 			if (res =3D=3D NULL)
 			{
 				if (pset.on_error_stop)
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index 44997467bf..8b5ef99c0e 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -204,6 +204,8 @@ like(
 # query result.
 my $tempdir =3D PostgreSQL::Test::Utils::tempdir;
 $node->safe_psql('postgres', "CREATE TABLE tab_psql_single (a int);");
+
+# Tests with ON_ERROR_STOP.
 $node->command_ok(
 	[
 		'psql',                                   '-X',
@@ -212,11 +214,12 @@ $node->command_ok(
 		'INSERT INTO tab_psql_single VALUES (1)', '-c',
 		'INSERT INTO tab_psql_single VALUES (2)'
 	],
-	'--single-transaction and multiple -c switches');
+	'ON_ERROR_STOP, --single-transaction and multiple -c switches');
 my $row_count =3D
   $node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
 is($row_count, '2',
-	'--single-transaction commits transaction, multiple -c switches');
+	'--single-transaction commits transaction, ON_ERROR_STOP and multiple -c =
switches'
+);
=20
 $node->command_fails(
 	[
@@ -226,11 +229,12 @@ $node->command_fails(
 		'INSERT INTO tab_psql_single VALUES (3)', '-c',
 		"\\copy tab_psql_single FROM '$tempdir/nonexistent'"
 	],
-	'--single-transaction and multiple -c switches, error');
+	'ON_ERROR_STOP, --single-transaction and multiple -c switches, error');
 $row_count =3D
   $node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
 is($row_count, '2',
-	'client-side error rolls back transaction, multiple -c switches');
+	'client-side error rolls back transaction, ON_ERROR_STOP and multiple -c =
switches'
+);
=20
 # Tests mixing files and commands.
 my $copy_sql_file   =3D "$tempdir/tab_copy.sql";
@@ -244,11 +248,12 @@ $node->command_ok(
 		'ON_ERROR_STOP=3D1', '-f', $insert_sql_file,       '-f',
 		$insert_sql_file
 	],
-	'--single-transaction and multiple -f switches');
+	'ON_ERROR_STOP, --single-transaction and multiple -f switches');
 $row_count =3D
   $node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
 is($row_count, '4',
-	'--single-transaction commits transaction, multiple -f switches');
+	'--single-transaction commits transaction, ON_ERROR_STOP and multiple -f =
switches'
+);
=20
 $node->command_fails(
 	[
@@ -256,10 +261,44 @@ $node->command_fails(
 		'ON_ERROR_STOP=3D1', '-f', $insert_sql_file,       '-f',
 		$copy_sql_file
 	],
-	'--single-transaction and multiple -f switches, error');
+	'ON_ERROR_STOP, --single-transaction and multiple -f switches, error');
 $row_count =3D
   $node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
 is($row_count, '4',
-	'client-side error rolls back transaction, multiple -f switches');
+	'client-side error rolls back transaction, ON_ERROR_STOP and multiple -f =
switches'
+);
+
+# Tests without ON_ERROR_STOP.
+# The last switch fails on \copy.  The command returns a success and the
+# transaction commits.
+$node->command_ok(
+	[
+		'psql',           '-X', '--single-transaction', '-f',
+		$insert_sql_file, '-f', $insert_sql_file,       '-f',
+		$copy_sql_file
+	],
+	'no ON_ERROR_STOP, --single-transaction and multiple -f switches');
+$row_count =3D
+  $node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
+is($row_count, '6',
+	'client-side error commits transaction, no ON_ERROR_STOP and multiple -f =
switches'
+);
+
+# The last switch makes the command return a success, and the contents of
+# the transaction commit even if there is a failure in-between.
+$node->command_ok(
+	[
+		'psql',                                   '-X',
+		'--single-transaction',                   '-c',
+		'INSERT INTO tab_psql_single VALUES (5)', '-f',
+		$copy_sql_file,                           '-c',
+		'INSERT INTO tab_psql_single VALUES (6)'
+	],
+	'no ON_ERROR_STOP, --single-transaction and multiple -c switches');
+$row_count =3D
+  $node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
+is($row_count, '8',
+	'client-side error commits transaction, no ON_ERROR_STOP and multiple -c =
switches'
+);
=20
 done_testing();
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ababd371df..65bb0a6a3f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -584,7 +584,8 @@ EOF
         <application>psql</application> to issue a <command>BEGIN</command=
> command
         before the first such option and a <command>COMMIT</command> comma=
nd after
         the last one, thereby wrapping all the commands into a single
-        transaction. If any of the commands fails, a
+        transaction. If any of the commands fails and the variable
+        <varname>ON_ERROR_STOP</varname> was set, a
         <command>ROLLBACK</command> command is sent instead. This ensures =
that
         either all the commands complete successfully, or no changes are
         applied.

--efO4ZfgsfeOoD8n+--

--dqInjKqejRLGEs+R
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmKm3PEACgkQnvQgOdby
QH12URAAl6JPqC3qTWCN3n7TWVhlsJfx1YmMQYQicHmUsypUV6bxGyHdrlfbT6aL
DLjMTZfm7M0hoS1ta74dJb44U8+PhA9rKZtfiyLIDcAtfDL/fMpC1BeeNzlsMTmQ
i+KOYtm5WUxD7yiiAbdzuXxkCKJgXR4JEaNz03YXXewecTk/XKb/La5vqlJuvZqb
/ihnsgHqLjbJci0deyObXE+CqfYrLTc72v11DoDbwnSZe/WKXdWZPUBiHeQqdXYt
BMhwoqQcy9RmMa2BfB2V4oJB7yb57f5jAYPNmGmfn8zMcTJVCQE9VM4pemF0LQwO
zUKQYQv+tssAfI9S//br2RlGd9GAriw9WQWUZe9vHyOSZ2n8YVyGsOTPGkAlRsrU
dtEco6bjKdCKnHCfPI4zez5PC7mUc+OB1fLsJvHJFLWRcIDp1uXgXk7SydIGSwKB
QMk7QHUGoValqz4aU9Z2QHn/85AhncuCDaOH/ZA9cVATKaN6jOhJ9gyu4xOYoz82
cWf+N+tMj5dfPuuYp8Awu20efK1yVjSmrQcE0KxLAQ8F4ET8N3d5BppaFLZVKHup
vWleIcOh/J60EdVxdIv4A1hfhCW5uZM7AQ0Vxr5nrBAO42hEx/UHHJUDMkmtLCrU
22shr/8i2VJnTgkrmpHWhVhXEdqazCxL0BiSwK1Cr/xIR/lQY1A=
=GNCg
-----END PGP SIGNATURE-----

--dqInjKqejRLGEs+R--