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?
