Thread: psql exit status varies for scripts on STDIN

I just noticed something unexpected when using psql.  I had written a
shell script to bulk-load some hundreds of files into a database, and
move each file to success/ or failure/ directories depending on the exit
status.

The problem is psql exit status differs depending on if the SQL script
is provided with -c or on STDIN.  Try this for example:

$ echo "your mom" | psql
ERROR:  syntax error at or near "your" at character 1
$ echo $?
0

The script failed, but the exit status is 0 (success) anyway.

$ psql -c "your mom"
ERROR:  syntax error at or near "your" at character 1
$ echo $?
1

If the same command is given with -c, the exit status indicates failure.

The workaround in this case was to provide "COPY table FROM STDIN;" as
the -c COMMAND and to pipe the data in on STDIN.  But simply adding the
COPY statement to the top of the input doesn't work right, with respect
to the exit status.

psql should probably give the same exit status in this case.  Do you
agree?

-jwb

"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> $ echo "your mom" | psql
> ERROR:  syntax error at or near "your" at character 1
> $ echo $?
> 0

> The script failed, but the exit status is 0 (success) anyway.

> $ psql -c "your mom"
> ERROR:  syntax error at or near "your" at character 1
> $ echo $?
> 1

> If the same command is given with -c, the exit status indicates failure.

AFAICS the first behavior comports with what it says in the psql man
page, and the second does not.

: 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 variable ON_ERROR_STOP was set.

I think this behavior is correct for the script case.  You can quibble
about whether reading from stdin is just like the script case or not,
but really the answer is to set ON_ERROR_STOP if you want a report.

If -c is considered to be a shorthand for a script, then this ought to
yield either 0 or 3 depending on whether ON_ERROR_STOP is set by
~/.psqlrc.  However I'm not sure if ~/.psqlrc is read for a -c
invocation.  I'd be willing to agree that -c should act as though
ON_ERROR_STOP is set always ... but in that case the exit code should
be 3, not 1.

Peter, I think you were the last to touch this stuff; any comments?

            regards, tom lane

Tom Lane wrote:
> If -c is considered to be a shorthand for a script, then this ought
> to yield either 0 or 3 depending on whether ON_ERROR_STOP is set by
> ~/.psqlrc.  However I'm not sure if ~/.psqlrc is read for a -c
> invocation.  I'd be willing to agree that -c should act as though
> ON_ERROR_STOP is set always ... but in that case the exit code should
> be 3, not 1.

-c isn't really like a script, it's just a one-shot command.  (You can't
mix SQL and meta-commands, for example.)  I think changing the exit
status from 1 to 3 is reasonable.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/