Re: proposal: multiple psql option -c - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: proposal: multiple psql option -c |
| Date | |
| Msg-id | CAFj8pRBauuZEn_Y1+7K_V9291aV1n2RQvuExa6k9eKXoCPDrqQ@mail.gmail.com Whole thread Raw |
| In response to | Re: proposal: multiple psql option -c ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: proposal: multiple psql option -c
|
| List | pgsql-hackers |
2015-11-12 1:35 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:
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 -Ccan, with defaults, be described thusly:BEGIN:-C #1 Statement #1COMMIT;BEGIN;-C #1 Statement #2COMMIT;BEGIN;-C #2 Statement OnlyCOMMIT;Basically the explicit representation of Auto-Commit "on" ModeI 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;
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 #2COMMIT; -- auto-committed;BEGIN;-C #2COMMIT;Additionally, if the variable AUTOCOMMIT is "off" then the implicit script should look like:BEGIN;-C #1 Statement #1-C #2 Statement #2-C #2COMMIT;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 #2COMMIT;BEGIN;-C #2COMMIT;Which is redundant with specifying only "--single-transaction". Each -C still commits otherwise you would just use the default.David J.
pgsql-hackers by date: