Thread: survey: psql syntax errors abort my transactions

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

https://www.postgresql.org/message-id/flat/CABTbUpiAOKZ405uArt8cJFtC72RhzthmvWETQK_6Qw0Ad-HquQ%40mail.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)

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

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...)
> 
> https://www.postgresql.org/message-id/flat/CABTbUpiAOKZ405uArt8cJFtC72RhzthmvWETQK_6Qw0Ad-HquQ%40mail.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

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