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@xxxxxxxxxxx
