[Proposal] Improve Postgres driver resilience against "cached plan must not change result type"

[mfn]

Yesterday I was hit by the following postgres error: cached plan must not change result type

This can happen if:

• you're using Eloquent to fetch a model
• which uses select * from table
• add a new column between the time the prepared statement is created and when it's executed

There are a few of resources to be found with google:

• https://stackoverflow.com/questions/2783813/postgres-error-cached-plan-must-not-change-result-type
• fix: try re-execute statement if backend responds with "prepared statement is no longer valid" pgjdbc/pgjdbc#451
• PostgreSQL prepared statement failure inside transaction results in a permanently cached prepared statement that's never cleaned up. rails/rails#12330 and subsequently Correctly deallocate prepared statements if we fail inside a transaction rails/rails#22170

A stacktrace looks like this:

PDOException: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  cached plan must not change result type
#22 Connection.php(330): execute
#21 Connection.php(330): Illuminate\Database\{closure}
#20 Connection.php(657): runQueryCallback
#19 Connection.php(624): run
#18 Connection.php(333): select
#17 Query/Builder.php(2095): runSelect
#16 Query/Builder.php(2083): Illuminate\Database\Query\{closure}
#15 Query/Builder.php(2569): onceWithColumns
#14 Query/Builder.php(2084): get
#13 Eloquent/Builder.php(516): getModels
#12 Eloquent/Builder.php(500): get
#11 Concerns/BuildsQueries.php(77): first

Example query which triggered this:

            $result = Model
                ::where('table.column', '=', $someValue)
                ->where('table.column', '=', $anotherValue)
                ->first();

Unfortunately at the moment, I can reproduce it in pure PHP code but not with a Laravel application.

Reproducible pure PHP script:

<?php
// cached plan must not change result type

$pdo = new PDO('…');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('select * from some_table where whatever = ? limit 1');
$stmt->execute([ 'whatever']);
var_dump($stmt->fetchAll());

// add new column now
sleep(30);

$stmt->execute([ 'whatever']);
var_dump($stmt->fetchAll());

Exception:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  cached plan must not change result type in …
Stack trace:
#0 cached_plan_must_not_change_result_type.php(16): PDOStatement->execute(Array)
#1 {main}
  thrown in cached_plan_must_not_change_result_type.php on line 16

It's easy to see/understand how this happens:

• prepare statement
• run it
• alter table
• run it again => 💥

However in pure Laravel ORM, I don't see the statements actually being cached; all I see is prepare and immediate execute.

And with this immediate execute I was not able to reproduce it. I.e. ->prepare(), then sleep(30) and add a column in the meantime and then ->execute()/->fetchAll() does yield this error.

Yet, the framework triggered that case and hence the initial stacktrace.

Unless I can reproduce it, there's no point coming up with an PR. I think however it will go along with some error handling in \Illuminate\Database\Connection::handleQueryException:

    protected function handleQueryException($e, $query, $bindings, Closure $callback)
    {
        if ($this->transactions >= 1) {
            throw $e;
        }

        return $this->tryAgainIfCausedByLostConnection(
            $e, $query, $bindings, $callback
        );
    }

Not sure about the transactions >= check, but it would be likely be checked for if causedByLostConnection is false.

Appreciate any input/insights from the community if you've experienced this or an idea how to reproduce it. I should be able to easily do it, yet as my testing indicates, I can't easily.