> > "ERROR: current transaction is aborted, queries ignored until end of
> > transaction block"
So am I the only one that finds this behaviour a little odd? It definitely
took me by surprise when I first saw it so I don't think other DB cli
interfaces do this.
I'm pretty sure Oracle's SQLPlus (no paragon of UI design I admit) just gives
an error but lets you type further commands without ignoring them. I don't
know what isql does in Sybase/MSSQL.
I imagine when you're running scripts as batch processing this logic makes
sense. It means you don't have to explicitly handle error conditions using
PL/PgSQL or another language, any transaction that fails just automatically
rolls back.
However it's really annoying when in interactive mode. frequently the error is
a simple typo or easily corrected error. It's frustrating to have to start
over from scratch after making a minor error in the middle of a complex
operation.
I'm not sure it's logical either. In an application interface transactions
don't automatically rollback after an error. The application receives the
error and makes the decision whether to branch to some alternate logic but
continue with the transaction or to rollback and pass the error up.
I guess most people just always use psql in autocommit mode, but I know once I
got used to it I was quite happy that SQLPlus was always in autocommit OFF
mode. It meant after doing any manual database updates I could double check
that the results were what I expected before committing. More than once I
avoided deleting the wrong records or updating fields to the wrong values.
It's much easier to rollback and try again to start trying to recover data
from backups...
But the same practice is much less practical with Postgres because any minor
error means starting over from scratch.
--
greg