Re: survey: psql syntax errors abort my transactions

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