For a long time one of the big gripes we get is that when using psql
interactively if you turn autocommit off and you typo on the nth command
you've suddenly lost all your work. The response was always that we needed a
generic subtransaction facility to handle it.
We've had such a facility for a while but we're still biting people with the
same problem.
I would like to suggest that we make psql default when in interactive mode to
using AUTOCOMMIT=false and ON_ERROR_ROLLBACK=true.
Defaulting to autocommit=false is somewhat surprising and a big change. I
recall the first time I used Oracle I was completely flummoxed by having
autocommit off. I know that'll be a hard sell.
But as I used it I grew quite fond of having autocommit off. It's saved my
bacon more than once. I strongly recommend it to anyone working with data they
care about even a little bit. Being able to double check your work results
before committing them is extremely valuable.
So basically what I'm saying is that I think what I recommend users use should
just be the default when using psql interactively.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com