Re: proposal: multiple psql option -c

On Fri, Nov 13, 2015 at 1:54 PM, Catalin Iacob <iacobcatalin(at)gmail(dot)com>
wrote:

> So I promised I'd try to document this. I had a look at the proposed
> semantics of -C and I think in the patch they're too complicated which
> makes explaining them hard.
>
> My assumptions about behaviour without this patch, from reading the
> docs and some experimenting, correct me if I'm wrong:
>
> 1. psql normally splits its input by ; let's call each piece of the
> split a statement
>
> 2. for every statement resulting after 1, if it's a \ command it's
> interpreted internally, else a query with it is sent to the server,
> the result is displayed
>
> 3. 1. and 2. happen when the input comes from a file (-f) or from stdin
>
> 4. autocommit off changes behaviour in that it sends a BEGIN before
> any of the statements after the split in 1 (except for \ commands,
> BEGIN or things like VACUUM which don't work within transactions)
>
> 5. --single-transaction changes behaviour in that it puts a BEGIN
> before the whole input (not around each statement) and a COMMIT after
>
> 6. all of the above DON'T apply for -c which very different things: it
> doesn't split and instead it sends everything, in one query to the
> backend. The backend can execute such a thing (it splits itself by ;)
> except in some cases like SELECT + VACUUM. Since the single query is
> effectively a single transaction for the backend -c ignores
> --single-transaction and autocommit off. Even more, when executing
> such a multiple statement the backend only returns results for the
> last statement of the query.
>
> From the above it seems -c is a different thing altogether while other
> behaviour allows 1 input with multiple commands, multiple results and
> works the same on stdin and a file.
>
> So my proposal is: allow a *single* argument for -C and treat its
> content *exactly* like the input from stdin or from a file.
>
> This answers all the questions about interactions with
> --single-transaction and autocommit naturally: it behaves exactly like
> stdin and -f behave today. And having a single parameter is similar to
> having a single file or single stdin. Having multiple -C is also
> confusing since it seems the statements in one -C are grouped somehow
> and the ones in the next -C are another group so this starts feeling
> like there's maybe a transaction per -C group etc.
>
> Am I missing something or is it that simple?
>

​While not in patch form here is some food for thought.

Tweaks to -c to link it with -C

​6c6
< Specifies that <application>psql</application> is to execute one
---
> Specifies that <application>psql</application> is to execute the
12d11
< <para>
32a32,36
> Furthermore, only a single instance of this parameter is accepted.
> Attempting to provide multiple instances will result in the entire
> shell command failing.
> </para>
> <para>
34,35c38,41
< the <option>-c</option> string often has unexpected results. It's
< better to feed multiple commands to
<application>psql</application>'s
---
> the <option>-c</option> string often has unexpected results. Two
> better options are available to execute multiple commands in a
> controlled manner. You may use the -C option, described next, or
> choose to feed multiple commands to
<application>psql</application>'s

​Draft -C thoughts

​ <term><option>-C <replaceable
class="parameter">command(s)</replaceable></></term>
<term><option>--multi-command=<replaceable
class="parameter">command(s)</replaceable></></term>
<listitem>
<para>
Specifies that <application>psql</application> is to execute one or
more command strings, <replaceable
class="parameter">commands</replaceable>,
and then exit. This differs from -c in that multiple instances may
be present
on the same shell command.
</para>
<para>
Also unlike -c, individual <option>-C</option> commands and
statements are executed
in auto-commit mode. The following pseudo-code example describe the
script
that is effectively created.
</para>
<programlisting>
psql -C 'SELECT 1;SELECT 2' -C 'SELECT 3;SELECT4'
psql &lt;&lt;EOF
BEGIN;
SELECT 1;
COMMIT;
BEGIN;
SELECT 2;
COMMIT;
BEGIN;
SELECT 3;
COMMIT;
BEGIN;
SELECT 4;
COMMIT;
EOF
</programlisting>
<para>
Alternatively the option <option>--single-transaction</option> makes
the entire multi-command execute
within a single transaction. There is no option to have entire
<option>-C</option> commands commit
independently of each other; you have to issue separate psql shell
commands.
</para>
<para>
Output from the <option>-C</option> command behaves more script-like
than <option>-c</option> as each
statement within each command is output.
</para>
<para>
As with <option>-c</option> the Start-up files
(<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>)
are ignored if this option is present on the command-line.
</para>
<para>
One particular motivation for introducing <option>-C</option> is the
first command below fails if executed
using <option>-c</option> but now there are two equivalent command
lines that work.
<programlisting>
psql -Atq -C "VACUUM FULL foo; SELECT pg_relation_size('foo')"
psql -Atq -C "VACUUM FULL foo" -C "SELECT pg_relation_size('foo')"
</programlisting>
</para>