Re: proposal: multiple psql option -c

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(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 06:39:55
Message-ID: CAB7nPqR7t_MvT4nJDPckyyPdn0XX4HiesRSUEdouNM6TH=s51g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Nov 12, 2015 at 9:35 AM, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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:

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2015-11-12 07:10:09 Re: Foreign join pushdown vs EvalPlanQual
Previous Message Kouhei Kaigai 2015-11-12 06:30:07 Re: Foreign join pushdown vs EvalPlanQual