Re: proposal: multiple psql option -c - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: proposal: multiple psql option -c |
| Date | |
| Msg-id | CAKFQuwaAuBizGJQ_JYvfH61jx1GT_XU0p6rdoxeTxn=fjUyPTQ@mail.gmail.com Whole thread Raw |
| In response to | Re: proposal: multiple psql option -c (Catalin Iacob <iacobcatalin@gmail.com>) |
| List | pgsql-hackers |
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 <<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>
pgsql-hackers by date: