I've been putting my money where my mouth is and running with
AUTOCOMMIT=off and ON_ERROR_ROLLBACK set which I've been recommending
for years but hadn't gotten around to switching to myself.
I think we knew the user experience wasn't perfect but it would be
nice to enumerate the problems and they don't seem insurmountable.
Some of them seem quite trivial.
1) I find it essential to add %x to the prompt or else I'm a)
continually forgetting to commit and b) continually being surprised by
being in a transaction when I didn't expect or need to be. In fact I
added it three times as '%/%R%x%x%x%# '. It would be nice to be able
to put something else other than * there though.
2) Some commands begin transactions that are a complete surprise.
CHECKPOINT in particular was a shock.
3) Plain SELECTs in read committed mode begin a transaction even
though there's no purpose served by the transaction -- there are no
snapshots pending and the locks taken don't seem relevant to
subsequent queries. I suppose it means if you select multiple times
from the same table you're guaranteed to get a consistent schema but
since DML can commit in between that doesn't seem useful. Notably \d
and the like do *not* begin a new transaction.
3) Some commands can't be run in a transaction such as VACUUM and
CREATE INDEX CONCURRENTLY and since you're often in a transaction
unexpectedly this often gets in your way.
I think the user expectation is that if after running a command the
session still only has a vxid and has no remaining snapshots (i.e.
it's not in serializable or read consistent mode) then the transaction
will just automatically commit/abort after the command. I'm not sure
if it's safe to go that far but it sure would be nice.
--
greg