[SOLVED] Bash & Postgre Error capturing

dragonbite · #1 13th August 2012, 04:31 PM

I have a shell script on a FreeBSD web server for work that is taking a text file (generated from the office server) and updates a table in the Postgre database.

It works and emails me success messages. I have verified that it does copy the data into the table, and it does send me an email with the success message.

Unfortunately I cannot get it to recognize there is an error in the code though when I manually run the script I see a message about a field that does not accept NULL values but the script doesn't see it.

Ideally I want to be able to capture the actual error message, which I can put into the email, if not then at least to register there IS an error which I can go and start investigating manually.

Here is my code, after removing comments and other clutter

Code:

#!/bin/sh
set -e

ERRMSG="/usr/home/dbupdater/error.msg"
SENDTO="me@work.com"
SUBJECT="Database update status"

# 
# If the file exists, remove it so we can create a new empty file
#

if [ -f $ERRMSG ]
then
     rm $ERRMSG
fi
echo "Started $(date)" > $ERRMSG

#
# run this block of actions in the Postgre database
#

/usr/local/bin/psql database_name -U user_name <<EOF

     TRUNCATE table_name;

    \copy table_name (field1, field2, field3 ) from '/usr/home/dbupdater/update.txt' with NULL 'NULL'

EOF

#
# If there is an error ( $? ) then do something. The problem is the process
# never goes into this loop. 
#

if [ $? != 0 ]; then
{
     echo "     Error loading the table." >> ${ERRMSG]
     exit 1
}
else
{
     echo "     Successfully loaded table." >> ${ERRMSG}
} fi

mail -s "$SUBJECT" "$SENDTO" < $ERRMSG

I have been using Linux, but am relatively new to actual shell scripting. I know this is FreeBSD, not Linux but I have gotten good advice from these forums and am sure the issue isn't all FreeBSD-specifc (I could be wrong though).

Any help would be appreciated! Thanks!

cazo · #2 13th August 2012, 05:52 PM

I'm not certain, but the set -e near the top means "Exit immediately if a command exits with a non-zero status." & psql
returning an error (non-zero) may be causing the script to exit at that point, so it never gets to the error test. I'd comment out that line to see if it helps.

Another thing I noticed is the test itself. I believe the != is for comparing strings. You'll probably need to use

Code:

if [ $? -ne 0 ]; then

instead - since you're comparing numeric values instead of strings.

dragonbite · #3 13th August 2012, 07:57 PM

Quote:

Originally Posted by cazo

I'm not certain, but the set -e near the top means "Exit immediately if a command exits with a non-zero status." & psql
returning an error (non-zero) may be causing the script to exit at that point, so it never gets to the error test. I'd comment out that line to see if it helps.

Another thing I noticed is the test itself. I believe the != is for comparing strings. You'll probably need to use

Code:

if [ $? -ne 0 ]; then

instead - since you're comparing numeric values instead of strings.

Thanks, I've tried that but the issue seems to be more fundamental with getting an error code/message from the Postgre block into shell.

cazo · #4 13th August 2012, 08:03 PM

In that case, you can try something like:

Code:

ST=$(/usr/local/bin/psql database_name -U user_name <<EOF
     TRUNCATE table_name;
    \copy table_name (field1, field2, field3 ) from '/usr/home/dbupdater/update.txt' with NULL 'NULL'
EOF
)

if [ $ST -ne 0 ]; then

to set the value of ST to psql's return value. Even something like

Code:

/usr/local/bin/psql database_name -U user_name <<EOF
     TRUNCATE table_name;
    \copy table_name (field1, field2, field3 ) from '/usr/home/dbupdater/update.txt' with NULL 'NULL'
EOF
ST=$?

if [ $ST -ne 0 ]; then

might work too.

dragonbite · #5 13th August 2012, 08:14 PM

The thing is even when I change the \copy command to throw an error (remove one column from the update that is still present in the actual file) $? returns 0 while using $ST=( ....) returns nothing at all.

cazo · #6 13th August 2012, 10:52 PM

I don't have any experience with psql, but the manpage for it has this:

Code:

EXIT STATUS
       psql  returns 0 to the shell if it finished normally, 1 if a fatal error of its own (out of memory, file not found) occurs, 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  vari-
       able ON_ERROR_STOP was set.

and, further down:

Code:

       ON_ERROR_STOP
              By default, if non-interactive scripts encounter an error, such as a malformed SQL command or internal meta-command, pro-
              cessing continues. This has been the traditional behavior of psql but it is sometimes not desirable. If this variable  is
              set,  script processing will immediately terminate. If the script was called from another script it will terminate in the
              same fashion. If the outermost script was not called from an interactive psql session but rather  using  the  -f  option,
              psql will return error code 3, to distinguish this case from fatal error conditions (error code 1).

From what I can tell, it looks like you need a line like:

Code:

\set ON_ERROR_STOP TRUE

dragonbite · #7 14th August 2012, 04:33 PM

I had posted this at other locations and was weeding through some of their responses as well as here and trying to read what I found online.

Using "\set ON_ERROR_STOP TRUE" is working and returning the error code. I may move it to the opening PostgreSQL line just in case, though.

So for reference in case anybody stumbles upon this thread is

Code:

#!/bin/sh

ERRMSG="/usr/home/dbupdater/error.msg"
SENDTO="me@work.com"
SUBJECT="Database update status"

# 
# Remove previous $ERRMSG file because if file exists it assumes there is an error 
# and will send an email to the person in charge.
#

if [ -f $ERRMSG ]
then rm $ERRMSG
fi

#
# Run this block against the PostgreSQL database
#

/usr/local/bin/psql database_name -U user_name <<EOF 

     \set ON_ERROR_STOP TRUE 
    
     TRUNCATE table_name; 
   
     \copy table_name (field1, field2, field3 ) from '/usr/home/dbupdater/update.txt' with NULL 'NULL'

EOF

#
# Clear the variable and capture any error codes from the PostgreSQL database running above
# 

ERRCODE=0
ERRCODE=$?

#
# Compare returned $ERRCODE to focus the message to be sent
# 

case $ERRCODE in
     3) echo "Script error(3) loading the table." >> $ERRMSG
     ;;
     2) echo "Connectivity error(2) loading the table." >> $ERRMSG
     ;;
     1) echo ""Fatal (out of memory, file not found) error(1) loading the table." >> $ERRMSG
     ;;
esac

#
# If the $ERRMSG file exists, assume file is populated with some error code and must be sent to
# the person in charge of this.
#

 if [ -f $ERRMSG ]
then mail -s "$SUBJECT" "$SENDTO" < $ERRMSG
fi