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(dot)klaver(at)aklaver(dot)com