psql return value / error killing the shell script that called it?

Question

I'm running several psql commands inside a bash shell script. One of the commands imports a csv file to a table. The problem is, the CSV file is occasionally corrupt, it has invalid characters at the end and the import fails. When that happens, and I have the ON_ERROR_STOP=on flag set, my entire shell script stops at that point as well.

Here's the relevant bits of my bash script:

$(psql \
-X \
 $POSTGRES_CONNECTION_STRING \
-w \
-b \
-L ./output.txt
-A \
-q \
--set ON_ERROR_STOP=on \
-t \
-c "\copy mytable(...) from '$input_file' csv HEADER"\
)

echo "import is done"

The above works fine as long as the csv file isn't corrupt. If it is however, psql spits out a message to the console that begins ERROR: invalid byte sequence for encoding "UTF8": 0xb1 and my bash script apparently stops cold at that point-- my echo statement above doesn't execute, and neither do any other subsequent commands.

Per the psql documentation, a hard stop in psql should return an error code of 3:

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of >memory, file not found), 2 if the connection to the server went bad and the session was not >interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set

That's fine and good, but is there a reason returning a value of 3 should terminate my calling bash script? And can I prevent that? I'd like to keep ON_ERROR_STOP set to on because I actually have other commands I'd like to run in that psql statement if the intial import succeeds, but not if it doesn't.

Laurenz Albe · Accepted Answer · 2019-11-21 08:15:15Z

3

ON_ERROR_STOP will not work with the -c option.

Also, the $(...) surronding the psql look wrong — do you want to execute the output as a command?

Finally, you forgot a backslash after the -L option

Try using a “here document”:

psql \
  -X \
  $POSTGRES_CONNECTION_STRING \
  -w \
  -b \
  -L ./output.txt \
  -A \
  -q \
  --set ON_ERROR_STOP=on \
  -t <<EOF
\copy mytable(...) from '$input_file' csv HEADER
EOF

echo "import is done"

answered Nov 21, 2019 at 8:15

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

larryq Over a year ago

Sorry about the missing backslash after the -L in the snippet, it's actually there in my script, not sure how it 'vanished' when transcribing it in the snippet. As to the $() bit, I originally was capturing (or trying to) the return value from psql like so: ret_val=$(psql... That didn't work, because the entire script was dying when psql did.

Collectives™ on Stack Overflow

psql return value / error killing the shell script that called it?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related