Thread: cmd-line interface

i have been using postgresql in preparation for supporting it with our
product.  one thing that i do a lot is run scripts from the cmd-line,
and i have noticed a few things that seem to be lacking from the psql
app.

first off though, i must say how much it rocks.  i absolutely hate
having to go back to using the db2 cmd-line app.  psql just does
everything cool, and i WISH i could only ever use it.  of all the
interfaces i've used so far ( quite a few ) psql is by far the most
capable.  congratulations.

but...  one thing i do like about the db2 app, is that on the cmd-line i
can specify two things:

1) stop on error
this seems to be doable through the env-var ON_ERROR_STOP, but i do wish
it was also parm i could pass on the cmd-line to psql.  its just so much
easier than setting the everytime i want the stop behaviour.  i guess i
could just set it and leave it.
2) wrap a file in an explicit transaction
with the db2 app, i can request the stop-on-fail as well as disabling
autocommit, all from the cmd-line.  this is also doable by manually
adding the sql at the beginning and end of the file, but that too kind
of sucks.  to me, the ideal behaviour would be to simply return to the
bash prompt without commiting or rolling back when the psql app ends,
then i can chose to do a commit or rollback manually.  however, this is
probably difficult as psql doesn't remember that much stuff about its
connection.  a very cool way of doing this would be to ask the user when
the psql app ended if they wished to commit.  only if this transaction
behaviour was requested would the question be posed, but this would
allow me to rollback something that had an error, without the hassle of
modiying the sql script.

is 2) possible right now?  i can't see anything in the docs, so i think
not.  anyway, having this sort of control would really help.  our
scripts need to run on various servers, and some things use different
syntax for transactions, db2 is 'rollback/commit work'.

one final question.  i presume that if a 'begin transaction' is used and
the ON_ERROR_STOP is set, then the transaction is rolled back?

cheers,

     CraigL->Thx();

From: "Craig Longman" <craigl@begeek.com>

> but...  one thing i do like about the db2 app, is that on the cmd-line i
> can specify two things:
>
> 1) stop on error
> this seems to be doable through the env-var ON_ERROR_STOP, but i do wish
> it was also parm i could pass on the cmd-line to psql.  its just so much
> easier than setting the everytime i want the stop behaviour.  i guess i
> could just set it and leave it.

Wrap psql in a 2-line shellscript which sets the env-var for you.

> 2) wrap a file in an explicit transaction
[snipped]
> is 2) possible right now?  i can't see anything in the docs, so i think
> not.  anyway, having this sort of control would really help.  our
> scripts need to run on various servers, and some things use different
> syntax for transactions, db2 is 'rollback/commit work'.

Bit trickier this one. I'd do it in perl (you can probably do it in shell,
but I'm not good enough). Steps would be:

1. parse command-line parameters
2. open pipe to psql with all parameters except "-f filename" (if there)
3. write "BEGIN;" to psql
4. WHILE not EOF read STDIN/file listed on command-line
5. write "COMMIT;" to psql

With the appropriate error-checking etc.

HTH

- Richard Huxton

> > 2) wrap a file in an explicit transaction
> [snipped]
> > is 2) possible right now?  i can't see anything in the docs, so i think
> > not.  anyway, having this sort of control would really help.  our
> > scripts need to run on various servers, and some things use different
> > syntax for transactions, db2 is 'rollback/commit work'.
>
> Bit trickier this one. I'd do it in perl (you can probably do it in shell,
> but I'm not good enough). Steps would be:
>
> 1. parse command-line parameters
> 2. open pipe to psql with all parameters except "-f filename" (if there)
> 3. write "BEGIN;" to psql
> 4. WHILE not EOF read STDIN/file listed on command-line
> 5. write "COMMIT;" to psql
>
> With the appropriate error-checking etc.

And don't forget to strip out the possible BEGINs and COMMITs in the file
since PostgreSQL doesn't support nested transactions.

Greg

Craig Longman writes:

> 1) stop on error
> this seems to be doable through the env-var ON_ERROR_STOP, but i do wish
> it was also parm i could pass on the cmd-line to psql.  its just so much
> easier than setting the everytime i want the stop behaviour.  i guess i
> could just set it and leave it.

psql -V ON_ERROR_STOP=

or check out ~/.psqlrc

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter