Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit a3ff08e

Browse filesBrowse files
michaelpqmichaelpq
committed
Tweak behavior of psql --single-transaction depending on ON_ERROR_STOP
This commit, in completion of 157f873, forces a ROLLBACK for --single-transaction only when ON_ERROR_STOP is used when one of the steps defined by -f/-c fails. Hence, COMMIT is always used when ON_ERROR_STOP is not set, ignoring the status code of the last action taken in the set of switches specified by -c/-f (previously ROLLBACK would have been issued even without ON_ERROR_STOP if the last step failed, while COMMIT was issued if a step in-between failed as long as the last step succeeded, leading to more inconsistency). While on it, this adds much more test coverage in this area when not using ON_ERROR_STOP with multiple switch patterns involving -c and -f for query files, single queries and slash commands. The behavior of ON_ERROR_STOP is arguably a bug, but there was no much support for a backpatch to force a ROLLBACK on a step failure, so this change is done only on HEAD for now. Per discussion with Tom Lane and Kyotaro Horiguchi. Discussion: https://postgr.es/m/Yqbc8bAdwnP02na4@paquier.xyz
1 parent ba412c9 commit a3ff08e
Copy full SHA for a3ff08e

File tree

Expand file treeCollapse file tree

3 files changed

+73
-11
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+73
-11
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/psql-ref.sgml
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -584,7 +584,8 @@ EOF
584584
<application>psql</application> to issue a <command>BEGIN</command> command
585585
before the first such option and a <command>COMMIT</command> command after
586586
the last one, thereby wrapping all the commands into a single
587-
transaction. If any of the commands fails, a
587+
transaction. If any of the commands fails and the variable
588+
<varname>ON_ERROR_STOP</varname> was set, a
588589
<command>ROLLBACK</command> command is sent instead. This ensures that
589590
either all the commands complete successfully, or no changes are
590591
applied.

‎src/bin/psql/startup.c

Copy file name to clipboardExpand all lines: src/bin/psql/startup.c
+7-2Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -426,8 +426,13 @@ main(int argc, char *argv[])
426426

427427
if (options.single_txn)
428428
{
429-
res = PSQLexec((successResult == EXIT_SUCCESS) ?
430-
"COMMIT" : "ROLLBACK");
429+
/*
430+
* Rollback the contents of the single transaction if the caller
431+
* has set ON_ERROR_STOP and one of the steps has failed. This
432+
* check needs to match the one done a couple of lines above.
433+
*/
434+
res = PSQLexec((successResult != EXIT_SUCCESS && pset.on_error_stop) ?
435+
"ROLLBACK" : "COMMIT");
431436
if (res == NULL)
432437
{
433438
if (pset.on_error_stop)

‎src/bin/psql/t/001_basic.pl

Copy file name to clipboardExpand all lines: src/bin/psql/t/001_basic.pl
+64-8Lines changed: 64 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -204,6 +204,8 @@ sub psql_fails_like
204204
# query result.
205205
my $tempdir = PostgreSQL::Test::Utils::tempdir;
206206
$node->safe_psql('postgres', "CREATE TABLE tab_psql_single (a int);");
207+
208+
# Tests with ON_ERROR_STOP.
207209
$node->command_ok(
208210
[
209211
'psql', '-X',
@@ -212,11 +214,12 @@ sub psql_fails_like
212214
'INSERT INTO tab_psql_single VALUES (1)', '-c',
213215
'INSERT INTO tab_psql_single VALUES (2)'
214216
],
215-
'--single-transaction and multiple -c switches');
217+
'ON_ERROR_STOP, --single-transaction and multiple -c switches');
216218
my $row_count =
217219
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
218220
is($row_count, '2',
219-
'--single-transaction commits transaction, multiple -c switches');
221+
'--single-transaction commits transaction, ON_ERROR_STOP and multiple -c switches'
222+
);
220223

221224
$node->command_fails(
222225
[
@@ -226,11 +229,12 @@ sub psql_fails_like
226229
'INSERT INTO tab_psql_single VALUES (3)', '-c',
227230
"\\copy tab_psql_single FROM '$tempdir/nonexistent'"
228231
],
229-
'--single-transaction and multiple -c switches, error');
232+
'ON_ERROR_STOP, --single-transaction and multiple -c switches, error');
230233
$row_count =
231234
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
232235
is($row_count, '2',
233-
'client-side error rolls back transaction, multiple -c switches');
236+
'client-side error rolls back transaction, ON_ERROR_STOP and multiple -c switches'
237+
);
234238

235239
# Tests mixing files and commands.
236240
my $copy_sql_file = "$tempdir/tab_copy.sql";
@@ -244,22 +248,74 @@ sub psql_fails_like
244248
'ON_ERROR_STOP=1', '-f', $insert_sql_file, '-f',
245249
$insert_sql_file
246250
],
247-
'--single-transaction and multiple -f switches');
251+
'ON_ERROR_STOP, --single-transaction and multiple -f switches');
248252
$row_count =
249253
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
250254
is($row_count, '4',
251-
'--single-transaction commits transaction, multiple -f switches');
255+
'--single-transaction commits transaction, ON_ERROR_STOP and multiple -f switches'
256+
);
252257

253258
$node->command_fails(
254259
[
255260
'psql', '-X', '--single-transaction', '-v',
256261
'ON_ERROR_STOP=1', '-f', $insert_sql_file, '-f',
257262
$copy_sql_file
258263
],
259-
'--single-transaction and multiple -f switches, error');
264+
'ON_ERROR_STOP, --single-transaction and multiple -f switches, error');
260265
$row_count =
261266
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
262267
is($row_count, '4',
263-
'client-side error rolls back transaction, multiple -f switches');
268+
'client-side error rolls back transaction, ON_ERROR_STOP and multiple -f switches'
269+
);
270+
271+
# Tests without ON_ERROR_STOP.
272+
# The last switch fails on \copy. The command returns a failure and the
273+
# transaction commits.
274+
$node->command_fails(
275+
[
276+
'psql', '-X',
277+
'--single-transaction', '-f',
278+
$insert_sql_file, '-f',
279+
$insert_sql_file, '-c',
280+
"\\copy tab_psql_single FROM '$tempdir/nonexistent'"
281+
],
282+
'no ON_ERROR_STOP, --single-transaction and multiple -f/-c switches');
283+
$row_count =
284+
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
285+
is($row_count, '6',
286+
'client-side error commits transaction, no ON_ERROR_STOP and multiple -f/-c switches'
287+
);
288+
289+
# The last switch fails on \copy coming from an input file. The command
290+
# returns a success and the transaction commits.
291+
$node->command_ok(
292+
[
293+
'psql', '-X', '--single-transaction', '-f',
294+
$insert_sql_file, '-f', $insert_sql_file, '-f',
295+
$copy_sql_file
296+
],
297+
'no ON_ERROR_STOP, --single-transaction and multiple -f switches');
298+
$row_count =
299+
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
300+
is($row_count, '8',
301+
'client-side error commits transaction, no ON_ERROR_STOP and multiple -f switches'
302+
);
303+
304+
# The last switch makes the command return a success, and the contents of
305+
# the transaction commit even if there is a failure in-between.
306+
$node->command_ok(
307+
[
308+
'psql', '-X',
309+
'--single-transaction', '-c',
310+
'INSERT INTO tab_psql_single VALUES (5)', '-f',
311+
$copy_sql_file, '-c',
312+
'INSERT INTO tab_psql_single VALUES (6)'
313+
],
314+
'no ON_ERROR_STOP, --single-transaction and multiple -c switches');
315+
$row_count =
316+
$node->safe_psql('postgres', 'SELECT count(*) FROM tab_psql_single');
317+
is($row_count, '10',
318+
'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
319+
);
264320

265321
done_testing();

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.