survey: psql syntax errors abort my transactions

On Thu, Jul 2, 2020 at 8:54 AM Jeremy Schneider <schnjere@amazon.com> wrote:

/messages/by-id/CABTbUpiAOKZ405uArt8cJFtC72RhzthmvWETQK_6Qw0Ad-HquQ@mail.gmail.com

This thread on hackers actually seemed kindof short to me. Not nearly
enough bike-shedding to call it a closed case.

Seemed about right:

"We should do this"
Yes
No - with a reason
No - with a reason
No - improve user education
No - emphatically
Yes - but ends ups deferring to the majority

No one else chooses to voice an opinion

The status quo prevailed since no-one chose to contribute further arguments
for change and the original patch was retracted. What kind of
"bike-shedding" (which seems to be used incorrectly here) would you expect?

All I can speak for is personal usage but I don't find the current default
to be an issue. I'm also generally opposed to changing this kind of
default even when I disagree with its current value. If anything psql is a
bit too permissive by default IMO. Default should be as safe as possible
even at the cost of user inconvenience - so that unknowledgeable people get
the most protection. If options exist to trade safety for convenience that
is good - each user can make that trade-off for themselves and in the
process be aware of what exactly their decision entails.

David J.

On 7/2/20 8:54 AM, Jeremy Schneider wrote:

Maybe it's just me, but I'm wondering if it's worth changing the default
behavior of psql so it doesn't abort transactions in interactive mode
when I mistakenly mis-spell "select" or something silly like that.  This
is of course easily remedied in my psqlrc file by adding "\set
ON_ERROR_ROLLBACK interactive". I don't know whether there are
equivalent settings for pgAdmin and Toad and whatever other tools people
are using for their interactive SQL sessions. But I do know that for all
the new people coming to PostgreSQL right now (including lots at my
company), none of them are going to know about this setting and
personally I think the default is user-unfriendly.

https://www.postgresql.org/docs/current/app-psql.html#id-1.9.4.18.8.5.2.9.17.1.2

A couple years back, some hackers discussed changing the default, and it
was decided against (IIUC) because of concerns about broken scripts
suddenly causing damage rather than aborting out. (About which... I
think if a script is sending broken SQL, then it might not be checking
error return values either and will likely keep running even after
PostgreSQL ignores a few SQL statements after the error in the current
session...)

/messages/by-id/CABTbUpiAOKZ405uArt8cJFtC72RhzthmvWETQK_6Qw0Ad-HquQ@mail.gmail.com

This thread on hackers actually seemed kindof short to me. Not nearly
enough bike-shedding to call it a closed case. It also seems to me that
the community has made significant changes across new major versions in
the past, and this idea here might not be entirely off the table quite yet.

So...

Survey for the user community here on the pgsql-general list: it would
be great if lots of people could chime in by answering two questions
about your very own production environment:

question 1) are you worried about scripts in your production environment
where damage could be caused by a different default in a future new
major version of postgresql?  (not aborting transactions in interactive
mode when syntax errors occur)

question 2) do you think the increased user-friendliness of changing
this default behavior would be worthwhile for specific users in your
organization who use postgresql?  (including both yourself and others
you know of)

I would say just add a message to the ERROR that points out
ON_ERROR_ROLLBACK = 'on' is available. For instance:

test(5432)=# begin ;
BEGIN
test(5432)=# select 1/0;
ERROR: division by zero
test(5432)=# select 1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block

New part of message
HINT: \set ON_ERROR_ROLLBACK on to rollback on error.

As someone working at a large company with an aweful lot of PostgreSQL,
thinking of the internal production systems I'm aware of, I'd personally
vote pretty strongly in favor of changing the default.

-Jeremy

--
Jeremy Schneider
Database Engineer
Amazon Web Services

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/2/20 09:28, David G. Johnston wrote:

The status quo prevailed since no-one chose to contribute further
arguments for change and the original patch was retracted.  What kind
of "bike-shedding" (which seems to be used incorrectly here) would you
expect?

The bike-shedding comment reflects my sense of humor, which is evidently
so dry that it can be entirely un-detectable.  :)  But overall I do
think there's room for input from more people.  Might not change the
outcome, I'm just curious if there are more people who'd have thoughts
to offer.

I'm not sure hint text would be the right course, as the hint wouldn't
make sense in the context of other clients and I don't know if we have a
mechanism now for the server to change it's error response based on
which client is being used.

One other thought occurred to me after sending this email - does
ON_ERROR_ROLLBACK=interactive differentiate between syntax errors and
other errors? Thinking about how users approach SQL, this feels
significant. I'd happily want to let users at my company retry after
syntax errors, but I'd want them to inspect any other error more closely.

-Jeremy

--
Jeremy Schneider
Database Engineer
Amazon Web Services

On 7/2/20 9:44 AM, Jeremy Schneider wrote:

On 7/2/20 09:28, David G. Johnston wrote:

The status quo prevailed since no-one chose to contribute further
arguments for change and the original patch was retracted.  What kind
of "bike-shedding" (which seems to be used incorrectly here) would you
expect?

The bike-shedding comment reflects my sense of humor, which is evidently
so dry that it can be entirely un-detectable.  :)  But overall I do
think there's room for input from more people.  Might not change the
outcome, I'm just curious if there are more people who'd have thoughts
to offer.

I'm not sure hint text would be the right course, as the hint wouldn't
make sense in the context of other clients and I don't know if we have a
mechanism now for the server to change it's error response based on
which client is being used.

But psql can. psql knows what the setting is and act accordingly e.g.:

test(5432)=# \x
Expanded display is on.

My suggestion is that the hint would only be shown in psql. Using HINT:
maybe not be the best choice as it shows up in server messages, but
something along that line.

One other thought occurred to me after sending this email - does
ON_ERROR_ROLLBACK=interactive differentiate between syntax errors and
other errors? Thinking about how users approach SQL, this feels
significant. I'd happily want to let users at my company retry after
syntax errors, but I'd want them to inspect any other error more closely.

-Jeremy

--
Jeremy Schneider
Database Engineer
Amazon Web Services

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/2/20 9:44 AM, Jeremy Schneider wrote:

On 7/2/20 09:28, David G. Johnston wrote:

The status quo prevailed since no-one chose to contribute further
arguments for change and the original patch was retracted.  What kind
of "bike-shedding" (which seems to be used incorrectly here) would you
expect?

The bike-shedding comment reflects my sense of humor, which is evidently
so dry that it can be entirely un-detectable.  :)  But overall I do
think there's room for input from more people.  Might not change the
outcome, I'm just curious if there are more people who'd have thoughts
to offer.

I'm not sure hint text would be the right course, as the hint wouldn't
make sense in the context of other clients and I don't know if we have a
mechanism now for the server to change it's error response based on
which client is being used.

One other thought occurred to me after sending this email - does
ON_ERROR_ROLLBACK=interactive differentiate between syntax errors and
other errors? Thinking about how users approach SQL, this feels
significant. I'd happily want to let users at my company retry after
syntax errors, but I'd want them to inspect any other error more closely.

An error is an error:

\set ON_ERROR_ROLLBACK interactive

test(5432)=# begin ;
BEGIN
test(5432)=# slect 1;
ERROR: syntax error at or near "slect"
LINE 1: slect 1;
^
test(5432)=# select 1;
?column?
----------
1
(1 row)

test(5432)=# select 1/0;
ERROR: division by zero
test(5432)=# select 1;
?column?
----------
1
(1 row)

-Jeremy

--
Jeremy Schneider
Database Engineer
Amazon Web Services

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 2020-07-02 at 09:31 -0700, Adrian Klaver wrote:

I would say just add a message to the ERROR that points out
ON_ERROR_ROLLBACK = 'on' is available. For instance:

test(5432)=# begin ;
BEGIN
test(5432)=# select 1/0;
ERROR: division by zero
test(5432)=# select 1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block

New part of message
HINT: \set ON_ERROR_ROLLBACK on to rollback on error.

-1

That would increase the already annoying spam of "current transaction is aborted"
messages when your transaction fails.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Thu, 2020-07-02 at 08:54 -0700, Jeremy Schneider wrote:

Maybe it's just me, but I'm wondering if it's worth changing the default behavior
of psql so it doesn't abort transactions in interactive mode when I mistakenly
mis-spell "select" or something silly like that.
This is of course easily remedied in my psqlrc file by adding "\set ON_ERROR_ROLLBACK interactive".
[...]
But I do know that for all the new people coming to PostgreSQL right now
(including lots at my company), none of them are going to know about this setting
and personally I think the default is user-unfriendly.
[...]

So...

Survey for the user community here on the pgsql-general list: it would be great if lots
of people could chime in by answering two questions about your very own production environment:

question 1) are you worried about scripts in your production environment where damage
could be caused by a different default in a future new major version of postgresql?
not aborting transactions in interactive mode when syntax errors occur)

I would dislike if interactive mode behaves differently from a non-interactive mode.

This is my favorite example why I like the way PostgreSQL does things:

/* poor man's VACUUM (FULL) */
BEGIN;
CREATTE TABLE t2 AS SELECT * FROM t1;
DROP TABLE t1;
ALTER TABLE t2 RENAME TO t1;
COMMIT;

question 2) do you think the increased user-friendliness of changing this default
behavior would be worthwhile for specific users in your organization who use postgresql?
(including both yourself and others you know of)

I personally would benefit because I wouldn't have to repeat the whole transaction
while teaching a class when I made a typo inside a transaction.

Still I prefer the way things are currently. Teaching classes is not the main
use case of psql.

Yours,
Laurenz Albe

On 7/3/20 1:54 AM, Laurenz Albe wrote:

On Thu, 2020-07-02 at 08:54 -0700, Jeremy Schneider wrote:

Maybe it's just me, but I'm wondering if it's worth changing the default behavior
of psql so it doesn't abort transactions in interactive mode when I mistakenly
mis-spell "select" or something silly like that.
This is of course easily remedied in my psqlrc file by adding "\set ON_ERROR_ROLLBACK interactive".
[...]
But I do know that for all the new people coming to PostgreSQL right now
(including lots at my company), none of them are going to know about this setting
and personally I think the default is user-unfriendly.
[...]

So...

Survey for the user community here on the pgsql-general list: it would be great if lots
of people could chime in by answering two questions about your very own production environment:

question 1) are you worried about scripts in your production environment where damage
could be caused by a different default in a future new major version of postgresql?
not aborting transactions in interactive mode when syntax errors occur)

I would dislike if interactive mode behaves differently from a non-interactive mode.

This is my favorite example why I like the way PostgreSQL does things:

/* poor man's VACUUM (FULL) */
BEGIN;
CREATTE TABLE t2 AS SELECT * FROM t1;
DROP TABLE t1;
ALTER TABLE t2 RENAME TO t1;
COMMIT;

How so, since it does not carry over indexes, foreign keys, triggers,
partition references, etc?

--
Angular momentum makes the world go 'round.

On Fri, Jul 3, 2020 at 7:46 PM Ron <ronljohnsonjr@gmail.com> wrote:

On 7/3/20 1:54 AM, Laurenz Albe wrote:

This is my favorite example why I like the way PostgreSQL does things:

/* poor man's VACUUM (FULL) */
BEGIN;
CREATTE TABLE t2 AS SELECT * FROM t1;
DROP TABLE t1;
ALTER TABLE t2 RENAME TO t1;
COMMIT;

How so, since it does not carry over indexes, foreign keys, triggers,
partition references, etc?

The point of this example is that if you have a typo in the CREATE
TABLE like here, you *don't want* to continue executing the commands,
which would drop the original table while you don't have a copy of the
data anymore. That's what he meant by liking the way postgres does
things, not how to do this poor man's vacuum full.

Jeremy Schneider wrote:

Survey for the user community here on the pgsql-general list: it would
be great if lots of people could chime in by answering two questions
about your very own production environment:

question 1) are you worried about scripts in your production environment
where damage could be caused by a different default in a future new
major version of postgresql?� (not aborting transactions in interactive
mode when syntax errors occur)

No. Any production scripts would be a single transaction.
I think anything else is a disaster waiting to happen
(and waiting for me to clean up afterwards).

question 2) do you think the increased user-friendliness of changing
this default behavior would be worthwhile for specific users in your
organization who use postgresql?� (including both yourself and others
you know of)

No. In fact, I think aborting on error is more
user-friendly than not doing so. I often send ad hoc
sql to psql via vim key bindings. I think that counts
as interactive from psql's point of view. The only
problem I have with that is when I mistyped "begin
transaction;" (or forgot the semi colon) and so an
error half way through doesn't result in a full
rollback. But I don't see what can be done about that
(other than automating the insertion of "begin
transaction;" and "commit transaction;" in my ad hoc
sql vim buffers).

What might be actually user-friendly is the ability,
after such an error, of being able to edit the contents
of the failed statement/transaction in the interactive
session in an editor to fix the typo. But that's for
real interactive use where stdin is a pty. That way,
you wouldn't have to retype or copy and paste the good
bits. That might already be possible. Yes, it's "\e".
And it works after an error, bringing up the
erroroneous sql in an editor, and it executes it when
you save and exit the editor. I think that's probably
what you want and it's already there. But it only edits
the last statement, not the whole transaction. It might
not be exactly what you need.

As someone working at a large company with an aweful lot of PostgreSQL,
thinking of the internal production systems I'm aware of, I'd personally
vote pretty strongly in favor of changing the default.

-Jeremy

Jeremy Schneider
Database Engineer
Amazon Web Services

cheers,
raf

On Fri, 2020-07-03 at 12:46 -0500, Ron wrote:

This is my favorite example why I like the way PostgreSQL does things:

/* poor man's VACUUM (FULL) */
BEGIN;
CREATTE TABLE t2 AS SELECT * FROM t1;
DROP TABLE t1;
ALTER TABLE t2 RENAME TO t1;
COMMIT;

How so, since it does not carry over indexes, foreign keys, triggers,
partition references, etc?

It is an example of what a transaction could look like that
would suffer from statement-level rollback.

I am not claimimg that that code as such is very useful.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com