当准备好的查询省略NOT NULL字段时，PostgreSQL PHP PDO执行挂起

Question

Using PostgreSQL 9.6 and PHP 7.2, I am creating a PDO connection, preparing an INSERT query, binding the parameters, then executing the prepared query. I have ATTR_ERRMODE set to ERRMODE_EXCEPTION, and in fact can cause exceptions to be thrown by the execute if, for example, the prepared query has 4 parameters, but I’ve only bound 3.

But if my prepared query doesn’t include a field with a NOT NULL constraint, then the execute hangs for maybe 20 seconds, then the browser displays “the connection was reset”. The Postgres log says:

ERROR:  null value in column "fullname" violates not-null constraint
DETAIL:  Failing row contains (345, fredf, [e-mail], yabadabadoo, null, Bedrock, none, user).
STATEMENT:  INSERT INTO users (username, password, location, description, email, role) VALUES ($1, $2, $3, $4, $5, $6)
LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

These 4 log entries repeat a total of 10 times.

I understand this situation is really a programming issue -- the query should be consistent with the requirements of the database design. But I’d really like to coax the Postgres PDO driver into telling me what the error was, rather than have to go dig through logs.

---

Sample code

/*

Sample code to demonstrate failure of prepared query execute()
to throw an exception when the prepared query omits a field
with a NOT NULL constraint.

CREATE TABLE users (
  id                SERIAL PRIMARY KEY,
  username      varchar(50) NOT NULL,
  email           varchar(100) NOT NULL,
  password      varchar(255) NOT NULL,
  fullname      varchar(100) NOT NULL,
  location      varchar(100) NOT NULL,
  description text NOT NULL,
  role            varchar(50) NOT NULL DEFAULT ''
);

*/
    $host   = "localhost";  
    $user   = "postgres";  
    $pass   = "password";  
    $dbname = "database";  
    /**
     * Initialize the PDO connection. 
     */
        $dsn = 'pgsql:host=' . $host . ';dbname=' . $dbname;
        $options = [
            PDO::ATTR_PERSISTENT => true,  
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION  
            ]; 
        try {  
            $handler = new PDO($dsn, $user, $pass, $options);  
        } catch (PDOException $e) {  
            echo $e->getMessage();
            die();  
        } 

     /**
      * Demonstrate failure of execute() to return an error or throw an exception
      * Error condition omits the fullname field from the INSERT query, which has a 
      * "not null" constraint.
      */
        $demonstrateProblem = 1;

        $badQuery = "INSERT INTO users (username, password, location, description, email, role) " . 
                    "VALUES (:username, :password, :location, :description, :email, :role)";

        $goodQuery = "INSERT INTO users (username, password, fullname, location, description, email, role) " . 
                    "VALUES (:username, :password, :fullname, :location, :description, :email, :role)";

        if ($demonstrateProblem == 1) {
            $query = $badQuery;
        } else  {
            $query = $goodQuery;
        }

        $stmt = $handler->prepare($query);  
        if (!$stmt) {
            echo "Error in prepare, errorInfo():<br>";
            print_r($handler->errorInfo());
        }
    /**
     * Bind the variables
     */
        $username   = "fredf";
        $password   = "yabadabadoo";
        $fullname   = "Fred Flintstone";
        $location   = "Bedrock";
        $description = "none";
        $email      = "[e-mail]";
        $role       = "user";

        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        if ($demonstrateProblem == 0) {
            $stmt->bindParam(':fullname', $fullname);
        } else {
            // nothing to do, :fullname is not in the prepared query!
        }
        $stmt->bindParam(':location', $location);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':role', $role);
    /**
     * Execute a prepared statement.
     */
        echo "Executing prepared query...<br>";
        try {
            $res = $stmt->execute(); // <-- this statement hangs when the query prepare is bad.
            if ($res) {
                echo "execute returns true<br> ";
            } else {
                echo "execute returns false<br> ";               
            }
        } catch (PDOException $e) {  
            echo "execute error " . $e->getMessage();  // <-- this never appears
        }

PostgreSQL log (sequence repeats 10 times)

 ERROR:  null value in column "fullname" violates not-null constraint
 DETAIL:  Failing row contains (345, fredf, [e-mail], yabadabadoo, null, Bedrock, none, user).
 STATEMENT:  INSERT INTO users (username, password, location, description, email, role) VALUES ($1, $2, $3, $4, $5, $6)
 LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

Answer 1

Well, you're trying to add data with NULL value for field with NOT NULL constraint on it. You either need to define default value in the Postgresql for "fullname" column, or make it NULL-able.