reading errors returned from psql in a bash script

Question

I need to check for errors when running psql from a bash script. Here's an example of how we're running things in the script:

return_value=$(psql \
-X \
 $POSTGRES_CONNECTION_STRING \
-f ./build_table.sql \
-w \
-b \
-A \
-q \
-t \
)

psql_exit_status=$?

The above statement works fine unless there's an error in the sql script, in which case I get some error output on the console but return_value is set to zero, as is psql_exit_status.

The build_table sql script creates a table and imports data from a csv file-- if there are errors in the csv file, or if, say, I intentionally misspell create tableeeee in the sql script I see psql errors on the screen but no error info is returned as best I can tell.

I've tried using the -o flag in psql to output to a file. Nothing shows, it's a blank file. I've also tried adding a 2>&1 bit after the psql statement to see if I could get some error info that way, but nothing doing.

What I need is some way to tell that the sql script has exited abnormally and/or crashed, without having to look at the output on the screen. Is that possible with the way I'm executing psql? Possible something's up with one of my psql flags?

I don't know psql, but its man page suggests you may need to enable ON_ERROR_STOP for it to exit with error in such cases. — that other guy
– that other guy, Commented Nov 20, 2019 at 0:01

Laurenz Albe · Accepted Answer · 2019-11-20 08:10:44Z

6

From the psql documentation:

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.

So you need to add -v ON_ERROR_STOP=on to your psql options.

However setting this variable has the following side effect:

By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.

edited Nov 20, 2019 at 8:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Nov 20, 2019 at 7:08

steve

6,0301 gold badge23 silver badges22 bronze badges

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

3 Comments

Laurenz Albe Over a year ago

Sorry, I had to fix your answer. ON_ERROR_STOP is not an environment variable.

larryq Over a year ago

This is really weird. Your suggestion definitely sounds like the way to go, however when I add it to my psql options, and the sql script fails, my entire bash script quits at that point too-- I've output some echo comands directly after calling psql with -v ON_ERROR_STOP=on, and they're never reached. As a result I have no opportunity to check the return value from psql.

larryq Over a year ago

@steve sure thing, I put a description of it in another SO post regarding the exit issues here: stackoverflow.com/questions/58965726/…

Collectives™ on Stack Overflow

reading errors returned from psql in a bash script

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related