From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Catalin Iacob <iacobcatalin(at)gmail(dot)com>, Adam Brightwell <adam(dot)brightwell(at)crunchydatasolutions(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, dinesh kumar <dineshkumar02(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: proposal: multiple psql option -c |
Date: | 2015-11-12 07:52:18 |
Message-ID: | CAFj8pRBauuZEn_Y1+7K_V9291aV1n2RQvuExa6k9eKXoCPDrqQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2015-11-12 07:56:38 | Re: pglogical_output - a general purpose logical decoding output plugin |
Previous Message | Michael Paquier | 2015-11-12 07:36:44 | Re: pageinspect patch, for showing tuple data |