phpsqlpostgresqlexec

PHP: exec() returns status '0' (successful) when executing failing PostgreSQL scripts

I'm trying to handle SQL-errors when running PostgreSQL-scripts via a PHP. My exec()-call always returns status '0', even if I force the SQL-script to fail:

PHP:

[...]  
$command = "$psqlPath -h $myHost -d $myDb -U $myUser -f $sqlScript 2>&1";
$output = [];
$return_var = 0;
exec($command, $output, $return_var);

sqlScript is designed to result in a constraint violation. The results are:

$return_var -> 0
$output[0] -> "psql:/pathToMyScript/sqlScript.sql:1: ERROR: duplicate key value violates unique constraint"

What am I doing wrong? Why is return_var still '0'?

If it's of concern: I'm running a relatively old version of PostgreSQL: 10.17.

Solution

  • PHP only transmits the exit code generated by the command, and pgsql doesn't return non-zero values for constraint violations by default:

    bash-5.1# psql -U docker -f test.sql; echo $?
psql:test.sql:1: ERROR:  null value in column "iso_code" of relation "country" violates not-null constraint
DETAIL:  Failing row contains (1, null, null, blah, null, null, null).
0

    As documented:

    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.

    In your case, you can provide it as argument:

    bash-5.1# psql -U docker -f test.sql -v ON_ERROR_STOP=1; echo $?
psql:test.sql:1: ERROR:  null value in column "iso_code" of relation "country" violates not-null constraint
DETAIL:  Failing row contains (1, null, null, blah, null, null, null).
3

    I've linked current release documentation but this should also work in your PostgreSQL/10 version.