From: Michael Paquier <michael@paquier.xyz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: christoph.berg@credativ.de
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17504: psql --single-transaction -vON_ERROR_STOP=1 still commits after client-side error
Date: Mon, 13 Jun 2022 15:45:05 +0900
Message-ID: <Yqbc8bAdwnP02na4@paquier.xyz> (raw)
In-Reply-To: <2656555.1654877531@sss.pgh.pa.us>
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>
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:
>
> - if (successResult != EXIT_SUCCESS && pset.on_error_stop)
> + if (successResult != 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
Attachments:psql-on-error.patch (text/x-diff, 5.3K) downloaddiff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.cindex 574f49d4c9..7c2f555f15 100644--- a/src/bin/psql/startup.c+++ b/src/bin/psql/startup.c@@ -426,8 +426,13 @@ main(int argc, char *argv[])
if (options.single_txn)
{
- res = PSQLexec((successResult == 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 = PSQLexec((successResult != EXIT_SUCCESS && pset.on_error_stop) ?+ "ROLLBACK" : "COMMIT");
if (res == NULL)
{
if (pset.on_error_stop)
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.plindex 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 = 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 =
$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'+);
$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 =
$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'+);
# Tests mixing files and commands.
my $copy_sql_file = "$tempdir/tab_copy.sql";
@@ -244,11 +248,12 @@ $node->command_ok(
'ON_ERROR_STOP=1', '-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 =
$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'+);
$node->command_fails(
[
@@ -256,10 +261,44 @@ $node->command_fails(
'ON_ERROR_STOP=1', '-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 =
$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 =+ $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 =+ $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'+);
done_testing();
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgmlindex 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> command 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.
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: michael@paquier.xyz, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, 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
In-Reply-To: <Yqbc8bAdwnP02na4@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox