Re: unexplainable psql exit status 1

Tim Kane <tim.kane@xxxxxxxxx> · Wed, 04 Dec 2013 12:44:26 +0000

I should have mentioned, psql client and server version 9.2.4

From:  Tim Kane <tim.kane@xxxxxxxxx>
Date:  Wednesday, 4 December 2013 10:59
To:  pgsql-general General <pgsql-general@xxxxxxxxxxxxxx>
Subject:  unexplainable psql exit status 1

HI all,

Is anyone aware of cases where psql will occasionally return an exit status of 1, despite there being no obvious error condition?

I have a regular (daily) cron that executes a fairly simple script of the form:

psql --set _ON_ERROR_STOP_=1 -h $DB_HOST -U $DB_USER -a -v DATE="'${DATE}'" -v YYYYMM="${DATE_YYYYMM}" -f duplicate_removal.sql $DB_NAME >> $DB_LOG 2>&1

The execution of this duplicate_removal.sql script appears to run without error. It’s fairly simple, and never fails to execute in and of itself.  
However, on very rare occasion, the psql session will run to completion, without visible/logged error – but it returns an exit status 1.

The only thing close to an error coming from this script is a notice, resulting from a CREATE TABLE IF NOT EXISTS. This is no different from any other execution of the script.
NOTICE:  relation "discarded" already exists, skipping

I can see that the output is correctly redirected to $DB_LOG and I can see the successful COMMIT response returned at the end of the session. I can also verify that the behaviour of the script is as expected, to completion.

The psql man page suggests that an exit status of 1 is a fatal error of its own.

EXIT STATUS
       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.

There is no indication in the postgres log file of anything adverse.

At this point my only option is to set log_statement = all, enable shell debugging, and wait a few months for it to happen again (I’ve seen this occur only twice, over a 2-3 month period).  Has anyone encountered this behaviour before?

Thanks

Tim