psql -v ON_ERROR_STOP=1 not working, cannot terminate script execution

Question

I have a bash script that calls another script containing a psql command, i want to stop executing when the psql command fails. The below psql call is to execute a postgres function. In below script the echo $exitcode always returns 0 even if script calling postgres function returns a error. I want psql command to return right error code, so i can handle it properly in below script. FirstScript:

list=`sh /opt/dbb_promo_list.sh -60 -30`
ID_LIST=`echo $list |sed 's/[a-z _-]*//g'|sed 's/(.*//g'`

exitcode=$? 
echo $exitcode //always returns 0 both in successful and error case, i want it to return corresponding exit code for error, so i can handle it and stop the script.
if [ $exitcode -ne 0 ] ; then
  echo "There seems to have errors while running script "
  echo "Exit code is : "$exitcode
  exit 1
fi

dbb_promo_list.sh : This script contains the following command

psql -U $DB_USERNAME -h $DB_HOST -p $DB_PORT -d $DATABASE -c "SELECT schema.${PROCEDURE}($day_begin,$day_end)"

Error thrown below when calling the above command. Since this error is thrown the psql command shouldnt return exit code 0 but its not the case, it returns 0 even if the postgres function works fine or returns a error like below. I am not sure if there a way to tweak in bash script to recognize a error is thrown

ERROR:  function string_agg(numeric, unknown) does not exist
LINE 1: SELECT string_agg(pmotn_id, ',')               FROM ppcapr.p...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

When the sql function fails. It then reports the error on the console, but continues to proceed with executing remainder of the script. But i want the script execution to terminate once the error occurs.

Any suggestions?

ON_ERROR_STOP is only going to cause psql to exit per "In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 ...". It has nothing to do with the Bash script. — Adrian Klaver
– Adrian Klaver, Commented Jul 15, 2022 at 19:46
@AdrianKlaver as per this response, stackoverflow.com/questions/58944395/… it should work with bash script also. I just want to do some exception handling to stop script execution, when there is a error thrown from the sql file that is being called — Kevin William
– Kevin William, Commented Jul 18, 2022 at 14:46
See the follow up answer here. In particular "ON_ERROR_STOP will not work with the -c option. ...". — Adrian Klaver
– Adrian Klaver, Commented Jul 18, 2022 at 15:08
@AdrianKlaver thanks for that, i didnt see that. I also tried to capture the response of psql into a variable, as per this example stackoverflow.com/questions/15242752/… , but it doesnt reach my echo variable statement , after psql command is run — Kevin William
– Kevin William, Commented Jul 18, 2022 at 15:30

Vinicius · Accepted Answer · 2023-05-04 22:40:14Z

1

The $? variable holds the last executed command exit code.

In your code, the last command executed before the exitcode=$? line is sed 's/(.*//g', not the psql command. Just moving up this line may solve the issue.

P.S.: I see it's an old question, but someone may benefit from the answer.

answered May 4, 2023 at 22:40

Vinicius

1,1631 gold badge12 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

psql -v ON_ERROR_STOP=1 not working, cannot terminate script execution

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related