Re: [PATCH] Make "psql -1 < file.sql" work as with "-f"

On Fri, May 10, 2013 at 9:50 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> On 8/9/12 9:08 AM, Robert Haas wrote:
> > On Wed, Aug 8, 2012 at 6:50 PM, David Fetter <david(at)fetter(dot)org> wrote:
> >>> I'm wondering if perhaps -- in addition to what you've done here -- we
> >>> should make "psql -1" error out if reading from a terminal.
> >>
> >> +1 for this.
> >
> > OK, done.
> >
> > I had to revise the original patch pretty heavily before committing;
>
> My first use of 9.3beta1 in development failed because of changes
> introduced by this patch, specifically because of the newly introduced
> error
>
> psql: -1 is incompatible with -c and -l
>
> I'm not convinced this is correct. -c and -l are single-transaction
> actions almost by definition.
>
> This particular aspect of the change wasn't really brought up in the
> original thread. What was your thinking?
>

FYI, I noticed this issue when building one of our applications against
HEAD;
I'm not sure I agree with you vis-a-vis the -c option, as it is certainly
plausible/meaningful
to do:
psql -c "begin; update [something]; insert [something]; delete
[something]; commit;"
and for that to be different from
psql -c "update [something]; insert [something]; delete [something];"

Consider it stipulated that it's pretty plausible to expect things to break
down if, in that
series of requests, the UPDATE fails, and it isn't nearly safe to assume
that the INSERT
and/or DELETE statements would succeed after all that.

I'd be pretty happy for
psql -1 -c "update [something]; insert [something]; delete [something];"
to implicitly augment the query to:
psql -c "begin; update [something]; insert [something]; delete
[something]; commit;"

It's a bit annoying (it bit me, giving me a complication, without any
evident benefit) for
"psql -1 -c" to refuse to run.

I'd rather that it behave similarly to "psql -1 -f", and wrap the queries
in a transaction.
For it to behave badly if I try to induce transaction control (e.g. -
embedding BEGIN/END
inside the queries) would not come as a surprise; that would be much the
same as how
"psql -1 -f" works, where the extra BEGIN is warned as redundant and the
extra COMMIT
is considered an error.

As for "psql -1 -l", it seems like a regression for that to fail. Listing
the databases is
pretty much already a single transaction; adding "-1" is perhaps
overspecifying things,
but it doesn't seem wrong.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"