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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
