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

Hello.

At Mon, 17 Dec 2018 11:58:41 -0600, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote in <20181217175841(dot)GS13019(at)telsasoft(dot)com>
> Our deployment script failed to notice dozens of commands failed in a
> transaction block and I only noticed due to keeping full logs and monitoring
> for error_severity>'LOG'. I would have thought that exit status would be
> nonzero had an error occured in an earlier script.
>
> The docs since PG9.6 say:
> https://www.postgresql.org/docs/9.6/app-psql.html
> |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.
>
> d5563d7df94488bf0ab52ac0678e8a07e5b8297e
> psql: Support multiple -c and -f options, and allow mixing them.
>
> If there's an error, it returns 1 (although that's not "a fatal error of its
> own").
>
> |[pryzbyj(at)database ~]$ psql ts -c foo 2>/dev/null ; echo $?
> |1
>
> But the error is "lost" if another script or -c runs without failing:
>
> |[pryzbyj(at)database ~]$ psql ts -txqc foo -c SELECT 2>/dev/null ; echo $?
> |0

As written in the documentation[1]:

> Because of this behavior, putting more than one SQL command in a
> single -c string often has unexpected results. It's better to use
> repeated -c commands or feed multiple commands to psql's standard
> input,

This seems saying that -c is equvalent to each line fed from
stdin or a line in a script.

The attached 0001 patch makes it clear in app-psql.html.

> Note, this works as expected:
>
> |[pryzbyj(at)database ~]$ psql ts -v ON_ERROR_STOP=1 -txqc foo -f /dev/null 2>/dev/null ; echo $?
> |1

The status should be 3, according to the documentation. Addition
to that, the current behavior looks inconsistent (if) considering
the equivalency between -c and a script line.

y.txt:
a)
foo;
select 1;
b)
select 1;
foo;

$ psql postgres -v ON_ERROR_STOP=0 -f ~/work/y.txt ; echo $?
$ psql postgres -v ON_ERROR_STOP=0 < ~/work/y.txt ; echo $?

All combinations return 0, and 3 when ON_ERROR_STOP=1.

But,

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).

Does it make sense?

regards.

[1]: https://www.postgresql.org/docs/11/app-psql.html

--
Kyotaro Horiguchi
NTT Open Source Software Center