Surprising behaviour of \set AUTOCOMMIT ON

2016-08-03 12:16 GMT+02:00 Rahila Syed <rahilasyed90@gmail.com>:

Hello,

Need community's opinion on following behaviour of \set AUTOCOMMIT

If \set AUTOCOMMIT ON is issued after \set AUTOCOMMIT OFF the commands
which follow after AUTOCOMMIT is set ON are not committed until an explicit
COMMIT is issued.
Its can be surprising to the user to not see results of the commands fired
after AUTOCOMMIT is set to ON.

bash-4.2$ psql -d postgres -U rahila
psql (9.6beta3)
Type "help" for help.

postgres=# \set AUTOCOMMIT OFF
postgres=# create table test1(i int);
CREATE TABLE
postgres=# \set AUTOCOMMIT ON
postgres=# create table test2(j int);
CREATE TABLE
postgres=# \c postgres rahila
You are now connected to database "postgres" as user "rahila".
postgres=# \dt;
No relations found.

The ongoing transaction is left running when there is this change in mode
from AUTOCOMMIT OFF to AUTOCOMMIT ON.
This happens because \set AUTOCOMMIT ON is fired within a transaction
block started when first command after \set AUTOCOMMIT OFF is executed.
Hence it requires an explicit COMMIT to be effective.

Should changing the value from OFF to ON automatically either commit or
rollback transaction in progress?

FWIW, running set autocommit through ecpg commits the ongoing transaction

when autocommit is set to ON from OFF. Should such behaviour be implemented
for \set AUTOCOMMIT ON as well?

I dislike automatic commit or rollback here. What about raising warning if
some transaction is open?

Regards

Pavel

On Wed, Aug 3, 2016 at 5:09 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2016-08-03 12:16 GMT+02:00 Rahila Syed <rahilasyed90@gmail.com>:

Should changing the value from OFF to ON automatically either commit or
rollback transaction in progress?

FWIW, running set autocommit through ecpg commits the ongoing transaction
when autocommit is set to ON from OFF. Should such behaviour be implemented
for \set AUTOCOMMIT ON as well?

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.

What about raising warning if
some transaction is open?

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.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-08-04 15:37 GMT+02:00 Amit Kapila <amit.kapila16@gmail.com>:

On Wed, Aug 3, 2016 at 5:09 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2016-08-03 12:16 GMT+02:00 Rahila Syed <rahilasyed90@gmail.com>:

Should changing the value from OFF to ON automatically either commit or
rollback transaction in progress?

FWIW, running set autocommit through ecpg commits the ongoing

transaction

when autocommit is set to ON from OFF. Should such behaviour be

implemented

for \set AUTOCOMMIT ON as well?

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 - ROLLBACK can be unfriendly surprising.

What about raising warning if
some transaction is open?

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.

Warnings enforce the user do some decision - I don't think so we can do
this decision well.

Regards

Pavel

On Thu, Aug 4, 2016 at 7:46 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2016-08-04 15:37 GMT+02:00 Amit Kapila <amit.kapila16@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-08-06 7:29 GMT+02:00 Amit Kapila <amit.kapila16@gmail.com>:

On Thu, Aug 4, 2016 at 7:46 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2016-08-04 15:37 GMT+02:00 Amit Kapila <amit.kapila16@gmail.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.

This is place when safety and and user friendly interface going against -
the most safe behave is raising rollback there. But it can be in contrast
with user's expectation.

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.

This is question. ecpg was designed years ago - and some details can be
designed wrong.

Next question is design for interactive and non interactive usage.

Warnings enforce the user do some decision

They could be annoying as well, especially if that happens in scripts.

in script - probably rollback is correct - script can be executed more time
and user can fix it.

I am not sure if we can solve this issue as isolated problem. The first
question should be - who, why and when does switching from autocommit off
to on? How often this operation is? And we should be safe or we should not?

Regards

Pavel

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.kapila16@gmail.com> wrote:

Its worth noting that the JDBC's behavior when you switch back to
autocommit is to immediately commit the open transaction.

Personally, I think committing immediately or erroring are unsurprising
behaviors. The current behavior is surprising and obviously wrong.
Rolling back without an error would be very surprising (no other database
API I know of does that) and would take forever to debug issues around the
behavior. And committing after the next statement is definitely the most
surprising behavior suggested.

IMHO, I think committing immediately and erroring are both valid. I think
I'd prefer the error in principle, and per the law of bad code I'm sure,
although no one has ever intended to use this behavior, there is probably
some code out there that is relying on this behavior for "correctness". I
think a hard failure and making the dev add an explicit commit is least
likely to cause people serious issues. As for the other options, consider
me opposed.

- Matt K.

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit
2. Rollback
3. Error
4. Warning

As per opinion upthread, issuing implicit commit immediately after
switching autocommit to ON, can be unsafe if it was not desired. While I
agree that its difficult to judge users intention here, but if we were to
base it on some assumption, the closest would be implicit COMMIT in my
opinion.There is higher likelihood of a user being happy with issuing a
commit when setting autocommit ON than a transaction being rolled back.
Also there are quite some interfaces which provide this.

As mentioned upthread, issuing a warning on switching back to autocommit
will not be effective inside a script. It won't allow subsequent commands
to be committed as set autocommit to ON is not committed. Scripts will have
to be rerun with changes which will impact user friendliness.

While I agree that issuing an ERROR and rolling back the transaction ranks
higher in safe behaviour, it is not as common (according to instances
stated upthread) as immediately committing any open transaction when
switching back to autocommit.

Thank you,
Rahila Syed

On Sun, Aug 7, 2016 at 4:42 AM, Matt Kelly <mkellycs@gmail.com> wrote:

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit
2. Rollback
3. Error
4. Warning

As per opinion upthread, issuing implicit commit immediately after switching
autocommit to ON, can be unsafe if it was not desired. While I agree that
its difficult to judge users intention here, but if we were to base it on
some assumption, the closest would be implicit COMMIT in my opinion.There is
higher likelihood of a user being happy with issuing a commit when setting
autocommit ON than a transaction being rolled back. Also there are quite
some interfaces which provide this.

As mentioned upthread, issuing a warning on switching back to autocommit
will not be effective inside a script. It won't allow subsequent commands to
be committed as set autocommit to ON is not committed. Scripts will have to
be rerun with changes which will impact user friendliness.

While I agree that issuing an ERROR and rolling back the transaction ranks
higher in safe behaviour, it is not as common (according to instances stated
upthread) as immediately committing any open transaction when switching back
to autocommit.

I think I like the option of having psql issue an error. On the
server side, the transaction would still be open, but the user would
receive a psql error message and the autocommit setting would not be
changed. So the user could type COMMIT or ROLLBACK manually and then
retry changing the value of the setting.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

Aborting the server-side transaction - with or without notice -
doesn't seem very reasonable.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Aug 8, 2016 at 11:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.com>
wrote:

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit
2. Rollback
3. Error
4. Warning

As per opinion upthread, issuing implicit commit immediately after

switching

autocommit to ON, can be unsafe if it was not desired. While I agree

that

its difficult to judge users intention here, but if we were to base it on
some assumption, the closest would be implicit COMMIT in my

opinion.There is

higher likelihood of a user being happy with issuing a commit when

setting

autocommit ON than a transaction being rolled back. Also there are quite
some interfaces which provide this.

As mentioned upthread, issuing a warning on switching back to autocommit
will not be effective inside a script. It won't allow subsequent

commands to

be committed as set autocommit to ON is not committed. Scripts will have

to

be rerun with changes which will impact user friendliness.

While I agree that issuing an ERROR and rolling back the transaction

ranks

higher in safe behaviour, it is not as common (according to instances

stated

upthread) as immediately committing any open transaction when switching

back

to autocommit.

I think I like the option of having psql issue an error. On the
server side, the transaction would still be open, but the user would
receive a psql error message and the autocommit setting would not be
changed. So the user could type COMMIT or ROLLBACK manually and then
retry changing the value of the setting.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

Aborting the server-side transaction - with or without notice -
doesn't seem very reasonable.

​Best of both worlds?​

​IF (ON_ERROR_STOP == 1)
THEN (treat this like any other error)
ELSE (send COMMIT; to server)

David J.
​

On Mon, Aug 8, 2016 at 11:17 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Mon, Aug 8, 2016 at 11:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.com>
wrote:

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit
2. Rollback
3. Error
4. Warning

As per opinion upthread, issuing implicit commit immediately after
switching
autocommit to ON, can be unsafe if it was not desired. While I agree
that
its difficult to judge users intention here, but if we were to base it
on
some assumption, the closest would be implicit COMMIT in my
opinion.There is
higher likelihood of a user being happy with issuing a commit when
setting
autocommit ON than a transaction being rolled back. Also there are
quite
some interfaces which provide this.

As mentioned upthread, issuing a warning on switching back to autocommit
will not be effective inside a script. It won't allow subsequent
commands to
be committed as set autocommit to ON is not committed. Scripts will have
to
be rerun with changes which will impact user friendliness.

While I agree that issuing an ERROR and rolling back the transaction
ranks
higher in safe behaviour, it is not as common (according to instances
stated
upthread) as immediately committing any open transaction when switching
back
to autocommit.

I think I like the option of having psql issue an error. On the
server side, the transaction would still be open, but the user would
receive a psql error message and the autocommit setting would not be
changed. So the user could type COMMIT or ROLLBACK manually and then
retry changing the value of the setting.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

Aborting the server-side transaction - with or without notice -
doesn't seem very reasonable.

Best of both worlds?

IF (ON_ERROR_STOP == 1)
THEN (treat this like any other error)
ELSE (send COMMIT; to server)

No, that's not a good idea. The purpose of ON_ERROR_STOP is something
else entirely, and we shouldn't reuse it for an unrelated purpose.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 08/08/16 17:02, Robert Haas wrote:

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit
2. Rollback
3. Error
4. Warning

As per opinion upthread, issuing implicit commit immediately after switching
autocommit to ON, can be unsafe if it was not desired. While I agree that
its difficult to judge users intention here, but if we were to base it on
some assumption, the closest would be implicit COMMIT in my opinion.There is
higher likelihood of a user being happy with issuing a commit when setting
autocommit ON than a transaction being rolled back. Also there are quite
some interfaces which provide this.

As mentioned upthread, issuing a warning on switching back to autocommit
will not be effective inside a script. It won't allow subsequent commands to
be committed as set autocommit to ON is not committed. Scripts will have to
be rerun with changes which will impact user friendliness.

While I agree that issuing an ERROR and rolling back the transaction ranks
higher in safe behaviour, it is not as common (according to instances stated
upthread) as immediately committing any open transaction when switching back
to autocommit.

I think I like the option of having psql issue an error. On the
server side, the transaction would still be open, but the user would
receive a psql error message and the autocommit setting would not be
changed. So the user could type COMMIT or ROLLBACK manually and then
retry changing the value of the setting.

This is my preferred action.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

I don't care for this very much.

Aborting the server-side transaction - with or without notice -
doesn't seem very reasonable.

Agreed.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Just for information,

PG current behavior,

"\set AUTOCOMMIT OFF" implicitly does/open "BEGIN;" block

So, "\set AUTOCOMMIT ON" has no effect once "\set AUTOCOMMIT OFF" is issued
until "END;" or "COMMIT;" or "ROLLBACK;"

however, I think if exit session release the transactions then change
session should also release the transactions

Thanks
Sridhar

On Mon, Aug 8, 2016 at 10:34 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:

On Tue, Aug 9, 2016 at 1:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.com>
wrote:

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit
2. Rollback
3. Error
4. Warning

As per opinion upthread, issuing implicit commit immediately after

switching

autocommit to ON, can be unsafe if it was not desired. While I agree

that

its difficult to judge users intention here, but if we were to base it on
some assumption, the closest would be implicit COMMIT in my

opinion.There is

higher likelihood of a user being happy with issuing a commit when

setting

autocommit ON than a transaction being rolled back. Also there are quite
some interfaces which provide this.

As mentioned upthread, issuing a warning on switching back to autocommit
will not be effective inside a script. It won't allow subsequent

commands to

be committed as set autocommit to ON is not committed. Scripts will have

to

be rerun with changes which will impact user friendliness.

While I agree that issuing an ERROR and rolling back the transaction

ranks

higher in safe behaviour, it is not as common (according to instances

stated

upthread) as immediately committing any open transaction when switching

back

to autocommit.

I think I like the option of having psql issue an error. On the
server side, the transaction would still be open, but the user would
receive a psql error message and the autocommit setting would not be
changed. So the user could type COMMIT or ROLLBACK manually and then
retry changing the value of the setting.

This makes more sense as the user who is doing it would realise that the
transaction has been left open.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

Issuing commit would indicate that, open transactions will be committed
which is not a good idea in my opinion. If the user is issuing AUTOCOMMIT =
ON, then it means all the transactions initiated after issuing this must be
committed, whereas it is committing the previously pending transactions as
well.

Aborting the server-side transaction - with or without notice -
doesn't seem very reasonable.

Agreed. Traditionally, open transactions in the database must be left open
until user issues a COMMIT or ROLLBACK. If the session is changed or
killed, then, the transaction must be rolled back.

Regards,
Venkata B N

Fujitsu Australia

On Thu, Aug 11, 2016 at 2:58 PM, Venkata Balaji N <nag1010@gmail.com> wrote:

On Tue, Aug 9, 2016 at 1:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed <rahilasyed90@gmail.com>
wrote:

Thank you for inputs everyone.

The opinions on this thread can be classified into following
1. Commit

This makes more sense as the user who is doing it would realise that the
transaction has been left open.

Alternatively, I also think it would be sensible to issue an immediate
COMMIT when the autocommit setting is changed from off to on. That
was my first reaction.

Issuing commit would indicate that, open transactions will be committed
which is not a good idea in my opinion. If the user is issuing AUTOCOMMIT =
ON, then it means all the transactions initiated after issuing this must be
committed, whereas it is committing the previously pending transactions as
well.

My apologies for confusing statement, correction - i meant, by setting
autocommit=on, committing all the previously open transactions (
transactions opened when autocommit=off) may not be a good idea. What user
meant by autocommit=on is that all the subsequent transactions must be
committed.

Regards,
Venkata B N

Fujitsu Australia

On Thu, Aug 11, 2016 at 2:11 AM, Venkata Balaji N <nag1010@gmail.com> wrote:

​[...]
committing all the previously open transactions
​[...]

"All"? ​There can only ever be at most one open transaction at any given
time...

I don't have a fundamental issue with saying "when turning auto-commit on
you are also requesting that the open transaction, if there is one, is
committed immediately." I'm more inclined to think an error is the correct
solution - or to respond in a way conditional to the present usage
(interactive vs. script). I disagree with Robert's unsubstantiated belief
regarding ON_ERROR_STOP and think that it captures the relevant user-intent
for this behavior as well.

David J.

On Thu, Aug 11, 2016 at 8:34 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

I don't have a fundamental issue with saying "when turning auto-commit on
you are also requesting that the open transaction, if there is one, is
committed immediately." I'm more inclined to think an error is the correct
solution - or to respond in a way conditional to the present usage
(interactive vs. script). I disagree with Robert's unsubstantiated belief
regarding ON_ERROR_STOP and think that it captures the relevant user-intent
for this behavior as well.

I'll substantiate my belief by referring to you for the documentation
for ON_ERROR_STOP, which says:

"By default, command processing continues after an error. When this
variable is set to on, processing will instead stop immediately. In
interactive mode, psql will return to the command prompt; otherwise,
psql will exit, returning error code 3 to distinguish this case from
fatal error conditions, which are reported using error code 1. In
either case, any currently running scripts (the top-level script, if
any, and any other scripts which it may have in invoked) will be
terminated immediately. If the top-level command string contained
multiple SQL commands, processing will stop with the current command."

In every existing case, ON_ERROR_STOP affects whether we continue to
process further commands after an error has already occurred. Your
proposal would involve changing things so that the value ON_ERROR_STOP
affects not only *how errors are handled* but *whether they happen in
the first place* -- but only in this one really specific case, and no
others.

This isn't really an arguable point, even if you want to try to
pretend otherwise. ON_ERROR_STOP should affect whether we stop on
error, not whether generate an error in the first place. The clue is
in the name.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Aug 12, 2016 at 3:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Aug 11, 2016 at 8:34 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

I don't have a fundamental issue with saying "when turning auto-commit on
you are also requesting that the open transaction, if there is one, is
committed immediately." I'm more inclined to think an error is the

correct

solution - or to respond in a way conditional to the present usage
(interactive vs. script). I disagree with Robert's unsubstantiated

belief

regarding ON_ERROR_STOP and think that it captures the relevant

user-intent

for this behavior as well.

I'll substantiate my belief by referring to you for the documentation
for ON_ERROR_STOP, which says:

"By default, command processing continues after an error. When this
variable is set to on, processing will instead stop immediately. In
interactive mode, psql will return to the command prompt; otherwise,
psql will exit, returning error code 3 to distinguish this case from
fatal error conditions, which are reported using error code 1. In
either case, any currently running scripts (the top-level script, if
any, and any other scripts which it may have in invoked) will be
terminated immediately. If the top-level command string contained
multiple SQL commands, processing will stop with the current command."

In every existing case, ON_ERROR_STOP affects whether we continue to
process further commands after an error has already occurred. Your
proposal would involve changing things so that the value ON_ERROR_STOP
affects not only *how errors are handled* but *whether they happen in
the first place* -- but only in this one really specific case, and no
others.

This isn't really an arguable point, even if you want to try to
pretend otherwise. ON_ERROR_STOP should affect whether we stop on
error, not whether generate an error in the first place. The clue is
in the name.

​Changing AUTOCOMMIT to ON while in a transaction is a psql error - period.

If ON_ERROR_STOP is on we stop. This meets the current semantics for
ON_ERROR_STOP.

With ON_ERROR_STOP off psql is going to continue on with the next command.
I'd suggest changing things so that psql can, depending upon the error,
invoke additional commands to bring the system into a known good state
before the next user command is executed. In the case of "\set AUTOCOMMIT
on" this additional command would be COMMIT. We can still report the error
before continuing on - so there is no affecting the "generating [of] an
error in the first place.".

​Allowing command-specific responses to errors when faced with script
continuation would be a change. I do not think it would be a bad one. Am
I stretching a bit here? Sure. Is it worth stretching to avoid adding
more knobs to the system? Maybe.

I'll admit I haven't tried to find fault with the idea (or discover better
alternatives) nor how it would look in implementation. As a user, though,
it would make sense if the system behaved in this way. That only
AUTOCOMMIT needs this capability at the moment doesn't bother me. I'm also
fine with making it an error and moving on - but if you want to accommodate
both possibilities ​this seems like a cleaner solution than yet another
environment variable that a user would need to consider.

David J.

On Sat, Aug 13, 2016 at 1:05 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

I'll admit I haven't tried to find fault with the idea (or discover better
alternatives) nor how it would look in implementation. As a user, though,
it would make sense if the system behaved in this way. That only AUTOCOMMIT
needs this capability at the moment doesn't bother me. I'm also fine with
making it an error and moving on - but if you want to accommodate both
possibilities this seems like a cleaner solution than yet another
environment variable that a user would need to consider.

I agree on your broad point that we should consider user convenience,
but in this case I am not sure if it will be convenient for a user to
make the behaviour dependent on value of ON_ERROR_STOP. I have
checked this behaviour in one of the top commercial database and it
just commits the previous transaction after the user turns the
Autocommit to on and the first command is complete. I am not saying
that we should blindly follow that behaviour, but just to indicate
that it should be okay for users if we don't try to define multiple
behaviours here based on value of ON_ERROR_STOP.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers