PG client: cached prepared statement is never evicted after a schema change on tables with 10+ columns #1751

Version

5.1.7 through 5.2.1, and current master (3967b19).

Context

ExtendedQueryPgCommandMessage evicts a cached prepared statement when the server reports a table schema change:

private static final String TABLE_SCHEMA_CHANGE_ERROR_MESSAGE_PATTERN = "bind message has \\d result formats but query has \\d columns";
...
return errorResponse.getMessage().matches(TABLE_SCHEMA_CHANGE_ERROR_MESSAGE_PATTERN) || ...

\d matches a single digit, and String.matches requires the whole message to match. So for any query that returns 10 or more columns, for example bind message has 15 result formats but query has 16 columns, the pattern doesn't match and no InvalidCachedStatementEvent is fired. The stale statement stays in the cache, and every later execution of that SQL on the connection fails with the same 08P01 until the connection is closed.

Against plain Postgres a cached statement usually fails with cached plan must not change result type instead, which is handled. The bind message has N result formats form shows up when something parses the named statement again after the schema change, before the client's Bind. AWS RDS Proxy does this when it moves the statement to a backend connection. We hit it in production: after an ALTER TABLE ... ADD COLUMN on a 26-column table, pooled connections behind RDS Proxy kept failing with bind message has 26 result formats but query has 27 columns for several minutes, until the pool recycled them.

Steps to reproduce

Against plain Postgres, with setCachePreparedStatements(true) and a single connection:

1. CREATE TABLE t (id int PRIMARY KEY, c1 int, ..., c9 int) (10 columns), then insert a row.
2. preparedQuery("SELECT * FROM t WHERE id=$1").execute(...) succeeds and caches the statement.
3. ALTER TABLE t ADD COLUMN c10 int.
4. To simulate the proxy, look up the statement name in pg_prepared_statements and run DEALLOCATE "<name>"; PREPARE "<name>" (int4) AS SELECT * FROM t WHERE id=$1.
5. Execute the prepared query: it fails with bind message has 10 result formats but query has 11 columns (expected).
6. Execute it again: it fails with the same error. It should succeed, because the statement should have been evicted in step 5.

With a table of 9 or fewer columns, step 6 succeeds.

Fix

Use \d+ for both counts. I have a branch with the one-line change and a regression test in PreparedStatementCachedTest covering the steps above, and will open a PR.