Re: proposal: multiple psql option -c

2015-11-12 1:35 GMT+01:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

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

This is relative difficult to implement - and from my view, it isn't
necessary

The implementation of "-c" is relative simple and then the options
"--single-transaction" or active autocommit has not effect. The string with
commands is pushed to server in one packet and it is processed as one
multicommand on server side. The implementation of "-C" is much more close
to interactive work - by default it is working in autocommit on mode and
following statements will be executed:

psql -C "cmd1;cmd2" -C "cmd3;cmd4"

executed statements:
cmd1;
cmd2;
cmd3;
cmd4;

or if you are thinking without implicit transactions:

BEGIN; cmd1; COMMIT;
BEGIN; cmd2; COMMIT;
BEGIN; cmd3; COMMIT;
BEGIN; cmd4; COMMIT;

when I use "--single-transaction", then the sequence of commands looks like:

BEGIN;
cmd1;
cmd2;
cmd3;
cmd4;
COMMIT;

I wouldn't to attach --single-transaction" option with individual "-C"
option, because the I feeling "--single-transaction" as global option.
More, partial transactions can be simply ensured by explicit transactions.
So I would to allow BEGIN,COMMIT in "-C" statements:

if I allow 'psql -C "BEGIN; cmd1; cmd2; COMMIT" -C "BEGIN; cmd3;cmd4;
COMMIT"

I am not big fan of some implicit transaction mechanisms and I prefer
simple joining implementation of "-C" with minimum design differences
against interactive work. This design looks simply.

The autocommit off mode is partially different, and I didn't though about
it. It requires explicit COMMIT (if it has to have some sense)

so if I run 'psql -C "cmd1;cmd2" -C"cmd3;cmd4"' in autocommit off mode,
then the result will be

BEGIN
cmd1;
cmd2;
cmd3;
cmd4;
-------- missing transaction end --- effective ROLLBACK -- it can good for
some "dry run" work.

but this mode can to allow

psql -C "cmd1;cmd2;COMMIT" -C "cmd3;cmd4; COMMIT"

It looks little bit obscure, but why not.

Using autocommit off and "--single-transaction" together is equivalent to
"--single-transaction" - but only in this case.

BEGIN; BEGIN; COMMIT; COMMIT isn't error

Regards

Pavel

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