Interesting request.

But I'm still unclear when pgcli would commit. For example: When a user launches pgcli we start a transaction, then they explore using select, change data using insert or update or drop, then explore some more using select. In this workflow where does the commit happen? Do we keep all these commands in a transaction until they quit pgcli or type in COMMIT/ROLLBACK themselves?

@darikg I was thinking your latest changes to add the extra field for denoting success/failure of a query can be used to achieve the undo feature.

But I'm still unclear when pgcli would commit.

I would only commit explicitly, i.e. by issuing a COMMIT statement. Of course this would need to be displayed on startup when transactions are enabled (and there could be an optional prompt on exit if there are uncommitted changes).

I think this would be very useful as a mode that can be enabled/disabled with its status shownin the bottom bar like smart completion and multiline mode. When autocommit mode is ON its behaviour would be the standard one when no transaction is explicly started and when it is OFF, a commit is explicitly needed.

Improving autocommit off mode with an automatic start of a new transaction (ROLLBACK+BEGIN) when an errors fail as @ThiefMaster purpose would be great.

This sounds like a nice feature to have. I like the idea of autocommit mode being displayed in the bottom status bar. We could also add a little indicator (like a red * or !) to show that user has uncommited changes - in addition to a warning on exit.

I would love to see this feature coming to pgcli, I'm currently dealing with begin; ... commit; on my own but that makes it really prone to error, especially when dealing with production databases...

Do note that there are PostgreSQL operations that can't be run inside a transaction (VACUUM comes to mind). This feature should be off by default.

This should be possible to enable. Or, more specifically, the optional behaviour should/must match that of psql when run with

\set AUTOCOMMIT off
\set ON_ERROR_ROLLBACK interactive

And it should be possible to configure this as the default operation somehow. The ON_ERROR_ROLLBACK interactive in psql creates implicit savepoints over each command, and rolls them back on exception so that in case of a simple typo in my session I do not have to start again from scratch.

It would be really nice to at least have the equivalent of psql's \set ON_ERROR_ROLLBACK interactive in pgcli. That's the only feature I really miss from psql. http://blog.endpoint.com/2015/02/postgres-onerrorrollback-explained.html might be helpful to anyone interested.

Huge +1 that ON_ERROR_ROLLBACK support would be huge. A typo after a bunch of transactional work can be quite frustrating. =) Thank you for all your great work!

\set AUTOCOMMIT off
is essential for any proper database developer work