If I have a script like this:
select 1;
select wrong;
select 1;
I can run it in postgres and the 1st and third commands run:
20:18:19:~ $ psql postgres postgres
psql (14.1)
Type "help" for help.
postgres=# \i ~/bad.sql
?column?
----------
1
(1 row)
psql:/home/richard/bad.sql:3: ERROR: column "wrong" does not exist
LINE 1: select wrong;
^
?column?
----------
1
(1 row)
postgres=#
If I want it to stop when it encounters an error, I can use ON_ERROR_STOP=1 like this:
19:52:29:~ $ psql -v ON_ERROR_STOP=1 postgres postgres
psql (14.1)
Type "help" for help.
postgres=# \i ~/bad.sql
?column?
----------
1
(1 row)
psql:/home/richard/bad.sql:3: ERROR: column "wrong" does not exist
LINE 1: select wrong;
^
postgres=#
So far so good.
But if I have a script which executes a shell command, like this:
select 1;
\! bash badshell.sh
select 1;
And run it like this:
20:25:40:~ $ psql -v ON_ERROR_STOP=1 postgres postgres
psql (14.1)
Type "help" for help.
postgres=# \i ~/bad-cmd.sql
?column?
----------
1
(1 row)
bash: badshell.sh: No such file or directory
?column?
----------
1
(1 row)
postgres=#
The ON_ERROR_STOP doesn't stop when it hits the error. Am I doing something wrong? Is there a postgres setting for this? Or some other workaround (even a compile time fix would be ok)?
An error in a subshell won't affect the execution of the psql script. If you want to stop the execution from the subshell, identify the shell's parent process (which is the psql process) and kill that.