POSIX shell & Postgre Error capturing

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


#!/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!

Any help would be appreciated! Thanks!

I did not study the whole script yet (first for dinner :wink: ), but in your title you mention* bash*, while your script starts with

#!/bin/sh

which means that sh (the POSIX shell) is used as interpreter and not bash (the Bourbne-again shell). This may lead to differences now and then and here and there.

It is definitely #!/bin/sh. When I started I tried #!/bin/bash which I see with Linux all of the time but this is FreeBSD and it couldn’t understand me.

So the POSIX shell is correct. I’ll have to see if I can get the title changed.

I changed it.

Yes, I am not sure that Bash is available on FreeBSD (but I do not know much about FreeBDS). In any case, when you wrote it for POSIX, then of course use POSIX everywhere.

Take care, many people here will not know the difference between the two and post answer which Bash knowledge as background only. However the differences are minimal (but not neglectable).

On Mon, 13 Aug 2012 16:06:02 +0000, hcvv wrote:

> which means that -sh- (the POSIX shell) is used as interpreter and not
> -bash- (the Bourbne-again shell). This may lead to differences now and
> then and here and there.

On many systems, /bin/sh is symlinked to /bin/bash - that’s certainly the
case on my 12.1 system here. Bash was developed by the GNU project as a
free software replacement for sh - so in principle, it’s intended to be
backwards compatible to sh, but adds some new features while retaining
that compatibility.

Jim

Jim Henderson
openSUSE Forums Administrator
Forum Use Terms & Conditions at http://tinyurl.com/openSUSE-T-C

I am not with you. The fact that the two are the same file (symlinked) does not mean that both calls do the same thing. See other examples like vi aand ex, both pointing to vim, but definetly offering you different thing

As I said bash and sh differ only in some points, but they do. And

man bash

is different from

man sh

A few years ago we had a problem here that was due to someone using the wrong interpreter (using the “shebang”). As said, I go for dinner, but will try to find a nice example.

Am 13.08.2012 18:19, schrieb Jim Henderson:
> On many systems, /bin/sh is symlinked to /bin/bash - that’s certainly
> the case on my 12.1 system here. Bash was developed by the GNU
> project as a free software replacement for sh - so in principle, it’s
> intended to be backwards compatible to sh, but adds some new features
> while retaining that compatibility.

It is symlinked but nonetheless it makes a difference if it is started
with the name bash or with the name sh as the man page clearly tells


If  bash  is  invoked  with  the  name sh, it tries to mimic the startup
behavior of historical versions of sh as closely as possible, while con-
forming  to  the POSIX standard as well.


PC: oS 12.2 x86_64 | i7-2600@3.40GHz | 16GB | KDE 4.8.4 | GeForce GT 420
ThinkPad E320: oS 12.1 x86_64 | i3@2.30GHz | 8GB | KDE 4.8.5 | HD 3000
eCAFE 800: oS 12.1 i586 | AMD Geode LX 800@500MHz | 512MB | KDE 3.5.10

On Mon, 13 Aug 2012 16:46:02 +0000, hcvv wrote:

> I am not with you. The fact that the two are the same file (symlinked)
> does not mean that both calls do the same thing. See other examples like
> vi aand ex, both pointing to vim, but definetly offering you different
> thing

Well, yes, there are differences, but in terms of the scripting language,
how significant are they?

https://www.gnu.org/software/bash/ says that bash is a “sh-compatible
shell”. As such, it would be a superset of sh syntax/functionality.

Jim


Jim Henderson
openSUSE Forums Administrator
Forum Use Terms & Conditions at http://tinyurl.com/openSUSE-T-C

Are you aware of the fact that in your last if … then … else … fi construct, in the case of an error, you abruptly exit the script whichout ever reaching the mail command at the end?

To me it seems that you try to learn from what you are doing. That is why I will point you now to the fact that in:


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

the first three lines are useless. The > redirection will create the file when it does not exist (you are aware of that), and if the file exists, it will empty it before adding the redirected output. (The >> redirection will only add at the end). Thus the > redirection does allready exectly what you want here.

Another thing is that you can directly test on the exit code:

if /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
then    echo "     Successfully loaded table." >> ${ERRMSG}
else    echo "     Error loading the table." >> ${ERRMSG]
fi

Also, my impression is that those { and } around statements (not those in the ${ }, those are very good imho) reveal that you learned another programming language earlier ')

Ok, so that is a redundant statement and the " > " redirection should do the same thing effectively? I’ll clean that up.

I thought the “exit 1” in the following code would just exit the if … fi structure

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

I thought the “exit 1” in the following code would just exit the if … fi structure

Wrong. And seeing your { and } around those statements, you are trying to use a pogramming language you allready know something of, to program POSIX shell statements. You should not do that.

Either find some learning stuff on the internet or a paper book. Or try to read the man page.

For the bash vs. sh (the POSIX, not the Bourne shell) discussion: I found that thread: http://forums.opensuse.org/english/other-forums/development/programming-scripting/425855-include-other-shell-script-current-shell-script-2.html

You will learn there that there is a difference in how the “sourcing” works. You will see also, that that difference was not implemented originaly in the GNU bash/sh program on Linux, but that the bug was removed. You will also see that the OP there did not realise that he did it wrong and only after the bug removing found out that he had done it wrong.

Actually I copied that from something I found online while looking up if… then structures.

So I changed it some to

if  $? -ne 0 ]then
     echo "     Error loading the table." >> ${ERRMSG}
else 
     echo "     Successfully loaded the table." >> ${ERRMSG}
fi

And just like the previous version with { }, it hits the else portion.

So it could have been anything, from COBOL via Algol and FORTRAN to Python and Ruby. Be glad that it did anything at all other then swamping you in error messages :cry:

I see you are stuck to the indirect testing of $? instead of direct testing how the program exited.

Now the only thing I believed you on your word is that the program psql returns a non zero status in those cases where you think it should. I have no Postgre (I only know the word Postgress, is that different?) knowledge, but looked into your script. Do not await from me that I correct you when the return codes are different from what you think. I take it for granted that you read the psql documentation thouroughly.

The $? returns a zero and PostgreSQL’s success code is “00000” but the issue is $? isn’t changing from zero even when an ERROR is thrown while running manually.

So it seems I am not capturing the error message thrown out during the PostgreSQL command, but how do I capture that it happened?

I even tried wrapping the PostgreSQL block in

$psql_out=$( ... )

and that returns nothing.

I’ve also tried echoing out “stderr” and “stdout” and “EOF” and all of them show blank values.

So if anybody has any suggestions, links or experience with this, I would greatly appreciate it.

Am 13.08.2012 21:46, schrieb dragonbite:
> The $? returns a zero and PostgreSQL’s success code is “00000” but the
> issue is $? isn’t changing from zero even when an ERROR is thrown while
> running manually.
>
Look into the psql man page (EXIT CODES)


3 if an error occurred in a script and the variable ON_ERROR_STOP
was set.

The variable ON_ERROR_STOP refers to the database session, try to set


\set ON_ERROR_STOP 1

in your embedded script (the part you pass to psql) as your first
command and introduce deliberately an error to check if you get a
nonzero exit status.


PC: oS 12.2 x86_64 | i7-2600@3.40GHz | 16GB | KDE 4.8.4 | GeForce GT 420
ThinkPad E320: oS 12.1 x86_64 | i3@2.30GHz | 8GB | KDE 4.8.5 | HD 3000
eCAFE 800: oS 12.1 i586 | AMD Geode LX 800@500MHz | 512MB | KDE 3.5.10

The $? returns a zero and PostgreSQL’s success code is “00000” but the issue is $? isn’t changing from zero even when an ERROR is thrown while running manually.

I do not understand why you think a program behaves different running manualy or not (but I also do not understand what you mean with “running manualy”).

It is up to the program designer/programmer to decide if and when what return code must be created. And that behaviour should of course be documented for the users. It isnot you that decides how it should be. But you can of course try to contact the designers with a proposal for change. OOne thing you sould not: thinking that a product behaves as if you would have liked/done/mde it.

But I see that Martin comes to the resvue and that he succeeded in reading some docs.

I’ve also tried echoing out “stderr” and “stdout” and “EOF” and all of them show blank values.

That is not something I can realy understand. But it could be that Martin’s help makes your clarification unneeded.

Am 13.08.2012 22:36, schrieb hcvv:
>> I’ve also tried echoing out “stderr” and “stdout” and “EOF” and all
>> of them show blank values.
> That is not something I can realy understand. But it could be that
> Martin’s help makes your clarification unneeded.
>
I am not sure I understand that statement either, I do a lot of database
related work (including working with Postgres, Oracle, MS SQL, Sybase
and others) but have near to no experience doing such things within
bash/sh scripts (that is the friendly way to say: I have exactly
absolute zero experience doing that from within bash/sh scripts).

Most of what I do is with Java’s jdbc or lower level C API’s or Python’s
DB API, ORM and friends with direct database connections from within a
programming language which has a lot more flexibility in error handling
than the exit code of some command where you never know if the exit code
depends on whether the command psql as such succeeded (which it always
does unless you do something really hairy) or the sql command within the
session psql creates succeeded.

I can later give the whole thing myself a try with psql, until now I
have only used it as a interactive shell for sql commands and nothing else.

But that can take a while, I have no Postgres database setup on the
machine I am sitting in front of at the moment and do not plan to
install it now on it. So when I am at my PC where everything is in place
I will give it a try.


PC: oS 12.2 x86_64 | i7-2600@3.40GHz | 16GB | KDE 4.8.4 | GeForce GT 420
ThinkPad E320: oS 12.1 x86_64 | i3@2.30GHz | 8GB | KDE 4.8.5 | HD 3000
eCAFE 800: oS 12.1 i586 | AMD Geode LX 800@500MHz | 512MB | KDE 3.5.10