It seems to me that the documentation should specify that when -C is used with -1 each individual series of commands is executed within a transaction block.
In summary:
Default (Not Single + Auto-Commit): One Transactions per parsed statement in all -Cs [<neither option specified>]
Single + Auto-Commit: One Transaction per -C [--single-transaction] {same as --no-auto-commit]
Not Single + Not Auto-Commit: One Transaction per -C [--no-auto-commit] {same as --single-transaction}
Single + Not Auto-Commit: One Transaction covering all -Cs [--no-auto-commit --single-transaction]
Explanation:
The transactional behavior of -C
can, with defaults, be described thusly:
BEGIN:
-C #1 Statement #1
COMMIT;
BEGIN;
-C #1 Statement #2
COMMIT;
BEGIN;
-C #2 Statement Only
COMMIT;
Basically the explicit representation of Auto-Commit "on" Mode
I don't understand how -c implements the promise of:
"""
If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions.
"""
But my gut (and Pavel) says that this is "legacy behavior" that should not be carried over to -C. I would suggest going further and disallowing transaction control statements within -C commands.
Now, in the presence of "--single-transaction" we would convert the transactional behavior from that shown above to:
BEGIN;
-C #1 Statement #1
-C #1 Statement #2
COMMIT; -- auto-committed;
BEGIN;
-C #2
COMMIT;
Additionally, if the variable AUTOCOMMIT is "off" then the implicit script should look like:
BEGIN;
-C #1 Statement #1
-C #2 Statement #2
-C #2
COMMIT;
So a "true" single transaction requires setting AUTOCOMMIT to off otherwise you only get each -C singly.
I would suggest adding an action "--no-auto-commit" option to complete the existence of the "--single-transaction" option. While the variable method works it doesn't feel as clean now that we are adding this option that (can) make direct use of it.
Specifying only --no-auto-commit results in:
BEGIN;
-C #1 Statement #1
-C #1 Statement #2
COMMIT;
BEGIN;
-C #2
COMMIT;
Which is redundant with specifying only "--single-transaction". Each -C still commits otherwise you would just use the default.
