Re: psql exit status with multiple -c or -f

Hello.

At Tue, 18 Dec 2018 10:24:39 -0600, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote in <20181218162439(dot)GB8042(at)telsasoft(dot)com>
> On Tue, Dec 18, 2018 at 05:13:40PM +0900, Kyotaro HORIGUCHI wrote:
> > $ psql postgres -v ON_ERROR_STOP=0 -f ~/work/y.txt ; echo $?
> > $ psql postgres -v ON_ERROR_STOP=0 < ~/work/y.txt ; echo $?
>
> > c) psql postgres -v ON_ERROR_STOP=0 -c foo -c 'select 1'; echo $?
> > d) psql postgres -v ON_ERROR_STOP=0 -c 'select 1' -c foo; echo $?
> >
> > (c) returns 0 and (d) returns 1, but both return 1 when
> > ON_ERROR_STOP=1.
>
> > The attached second patch lets (c) and (d) behave the same as (a)
> > and (b).
>
> I don't think the behavior in the single command case should be changed:

I guess the reason is that psql is widely used with just a single
-c command and acutually the fix breaks the cases. So it doesn't
seem back-pachable but it is apparently contradicting to
documentation, which seems perfectly reasonable.

So I propose to fix the behavior for 12 and back-patch
documentation fix.

| 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.
+ As the only exception, irrespective of ON_ERROR_STOP setting,
+ psql returns 1 if the last executed command failed and it was
+ givin by -c option.

# What a ...

> |[pryzbyj(at)database postgresql]$ ./src/bin/psql/psql ts -c FOO 2>/dev/null ; echo $?
> |1
> |[pryzbyj(at)database postgresql]$ patch -p1 </tmp/0002-Unify-psql-s-behavior-on-c-with-scripts.patch
> |patching file src/bin/psql/startup.c
> |[pryzbyj(at)database postgresql]$ make >/dev/null
> |[pryzbyj(at)database postgresql]$ ./src/bin/psql/psql ts -c FOO 2>/dev/null ; echo $?
> |0
>
> Also, unpatched v11 psql returns status of last command
> |[pryzbyj(at)database postgresql]$ psql ts -xtc 'SELECT 1' -c FOO 2>/dev/null ; echo $?
> |?column? | 1
> |
> |1
>
> patched psql returns 0:
> |[pryzbyj(at)database postgresql]$ ./src/bin/psql/psql ts -xtc 'SELECT 1' -c FOO 2>/dev/null ; echo $?
> |?column? | 1
> |
> |0
>
> I'd prefer to see the exit status of the -f scripts (your cases a and b)
> changed to 3 if the last command failed. I realized that's not what the
> documentation says so possibly not backpatchable.

It doesn't make sense that psql exits with ERROR stauts when
accidentially the last command was failed while erros occurred so
far was ignored. It is achieved by ON_ERROR_STOP=1 in a better way.

> |3 if an error occurred in a script and the variable ON_ERROR_STOP was set.
>
> Think of:
>
> $ cat /tmp/sql
> begin;
> foo;
> select 1;
>
> $ psql ts -f /tmp/sql ; echo ret=$?
> BEGIN
> psql:/tmp/sql:2: ERROR: syntax error at or near "foo"
> LINE 1: foo;
> ^
> psql:/tmp/sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block
> ret=0

As you wrote below, ON_ERROR_STOP=1 would give the desired
behavior.

> I think ON_ERROR_STOP would control whether the script stops, but it should
> fail the exit status should reflect any error in the last command. The shell
> does that even without set -e.

At least bash behaves exactly the same way to psql for me. (Just
so there's not confusion, set -e works opposite as you think. I
always use explcit exit to do that, though).

===t.sh:
hoge
hage
echo Ho-Ho-Ho
===
$ bash t.sh ; echo $?
test.sh: line 1: hoge: command not found
test.sh: line 2: hage: command not found
Ho-Ho-Ho
0

===t.sh:
set -e
hoge
hage
echo Ho-Ho-Ho
===
$ bash t.sh ; echo $?
test.sh: line 2: hage: command not found
127

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center