Re: proposal: multiple psql option -c

On Wed, Nov 11, 2015 at 7:01 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> 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.

David J.