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();
