Re: AUTOCOMMIT off + ON_ERROR_ROLLBACK usability - Mailing list pgsql-hackers

On Sun, Mar 16, 2014 at 11:58:53AM +0000, Greg Stark wrote:
> 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.

psql limits special treatment to PreventTransactionChain() commands, more or
less.  I grant that there are other credible policies, but it would take a
strong benefit to switch policies now.

> 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.

Any function or operator could modify the database.  psql has no general way
to distinguish, in advance, SELECTs that do change the database.

> Notably \d
> and the like do *not* begin a new transaction.

It's probably not worth changing \d itself, but I would like it if more
backslash commands respected ON_ERROR_ROLLBACK.  This is a wart:

[local] test=# \set ON_ERROR_ROLLBACK on \unset AUTOCOMMIT
[local] test=# select 1;?column? 
----------       1
(1 row)

[local] test=# \ef no_such_function
ERROR:  function "no_such_function" does not exist
[local] test=# select 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

> 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.

You could offer that under "\set AUTOCOMMIT trivial", perhaps.  I would not be
an early adopter, but I can imagine folks liking it.  Both DBI and JDBC have
autocommit-off features that consistently require an explicit COMMIT from the
user.  psql's similar policy meets my expectations.

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com