Re: Surprising behaviour of \set AUTOCOMMIT ON

As an extra data point, if you try this in Python (psycopg2) you get an
exception:

psycopg2.ProgrammingError: autocommit cannot be used inside a transaction

I think this exception is a legitimate response. If the user switches on
autocommit mode inside a transaction, it was most likely not on purpose.
Chances are, they didn't realise autocommit was off in the first place.

Even if we assume that it was done deliberately, it's difficult to know
exactly what the user intended. It seems to hinge on a subtlety of what
the user understands autocommit mode to mean -- either "issue an implicit
COMMIT after each statement", or "ensure there is never an open
transaction".

I feel that raising an error is a sane move here -- it is reasonable to
insist that the user make their intention unambiguous.

Cheers,
BJ

On Sat, 6 Aug 2016 at 15:30 Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Thu, Aug 4, 2016 at 7:46 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> > 2016-08-04 15:37 GMT+02:00 Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>:
> >>
> >> > I dislike automatic commit or rollback here.
> >> >
> >>
> >> What problem you see with it, if we do so and may be mention the same
> >> in docs as well. Anyway, I think we should make the behaviour of both
> >> ecpg and psql same.
> >
> >
> > Implicit COMMIT can be dangerous
> >
>
> Not, when user has specifically requested for autocommit mode as 'on'.
> I think here what would be more meaningful is that after "Set
> AutoCommit On", when the first command is committed, it should commit
> previous non-pending committed commands as well.
>
> >>
> >> Not sure what benefit we will get by raising warning. I think it is
> >> better to choose one behaviour (automatic commit or leave the
> >> transaction open as is currently being done in psql) and make it
> >> consistent across all clients.
> >
> >
> > I am not sure about value of ecpg for this case. It is used by 0.0001%
> > users. Probably nobody in Czech Republic knows this client.
> >
>
> Sure, but that doesn't give us the license for being inconsistent in
> behaviour across different clients.
>
> > Warnings enforce the user do some decision
> >
>
> They could be annoying as well, especially if that happens in scripts.
>
>
> --
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>