On Thu, Nov 12, 2015 at 9:35 AM, David G. Johnston
<david.g.johns...@gmail.com> wrote:
> On Wed, Nov 11, 2015 at 7:01 AM, Michael Paquier <michael.paqu...@gmail.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:I am assuming you refer to this command in your analysis: "#1 Statement #1" being the first statement of the first -C, "#1 Statement #2" the second statement in the first -C switch, and "Statement Only" the statement of of a second -C switch. Or simply that: psql -C 'Statement1,Statement2' -C 'Statement' > 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; Yep, that's what it does by going though MainLoop(). > 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. > """ -c simply processes everything it has within libpq in one shot. This code path does not care about --single-transaction, and it relies on a backend check to be sure that nothing not allowed within a transaction block runs when multiple queries are passed though it. > 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; Correct. > 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. Yeah, that's what -c does actually by relying on the backend to check incompatibilities regarding stuff that should not run in transaction blocks. > 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. Don't you mean that actually: BEGIN; -C #1 Statement #1 -C #1 Statement #2 -C #2 COMMIT; By the way there is no much point to this option. It seems to me that it is already possible to do it with --set AUTOCOMMIT=off. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
