Prepared Statement Cache: Invalid Cache Retention on Failed PREPARE Statements

[yiyuntian]

Is there a better solution than completely disabling prepared statement caching? Perhaps a way to make Odyssey aware of schema changes?

When using prepared statements with Odyssey as a connection pool, there's an inconsistency in handling cached prepared statements after initial table creation. The issue occurs when:

1. A PREPARE statement is sent for a non-existent table (incorrectly)
2. The table is then created
3. The same PREPARE statement is resent
   Odyssey incorrectly reports success without forwarding the statement to PostgreSQL, leading to "prepared statement does not exist" errors.

Environment:
Odyssey version: 1.4.1
PostgreSQL version: 15.15
JDBC driver: postgresql-42.7.5.jar
Connection string: jdbc:postgresql://127.0.0.1:7432/postgres?prepareThreshold=1&binaryTransfer=true

Problem Description
Odyssey incorrectly caches failed prepared statement metadata when:

1. A client sends a PREPARE statement for a non-existent table (fails)
2. The table is subsequently created
3. The same PREPARE statement is reattempted

Observed Behavior:
Odyssey returns success without forwarding the statement to PostgreSQL
Subsequent executions fail with "prepared statement does not exist"

Expected Behavior:
Failed PREPARE attempts should invalidate the cache
Valid retries should reach PostgreSQL after table creation

Root Cause
Odyssey's statement cache:

1. Retains metadata from initial (failed) PREPARE
2. Skips revalidation for subsequent identical statements
3. Never propagates valid PREPARE to PostgreSQL after schema changes

Test Case (Java):

import java.sql.*;
import java.math.BigDecimal;

public class BatchInsertOptimized {
    private static final String URL = "jdbc:postgresql://127.0.0.1:7432/postgres?prepareThreshold=1&binaryTransfer=true";
    private static final String USER = "test";
    private static final String PASSWORD = "";
    private static final int BATCH_SIZE = 10;
    private static final BigDecimal VALUE = new BigDecimal("10");

    public static void main(String[] args) {
        String sql = buildInsertQuery();
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            setParameters(ps);
            
            int rows = ps.executeUpdate();
            System.out.println("Successfully inserted " + rows + " rows.");
            
        } catch (SQLException e) {
            System.err.println("Database error occurred:");
            e.printStackTrace();
        }
    }

    private static String buildInsertQuery() {
        StringBuilder sb = new StringBuilder("INSERT INTO tbl1 (id, name) VALUES ");
        String valuesPlaceholder = "(?, ?)";
        
        for (int i = 0; i < BATCH_SIZE; i++) {
            if (i > 0) {
                sb.append(", ");
            }
            sb.append(valuesPlaceholder);
        }
        return sb.toString();
    }

    private static void setParameters(PreparedStatement ps) throws SQLException {
        int paramIndex = 1;
        
        for (int i = 0; i < BATCH_SIZE; i++) {
            ps.setInt(paramIndex++, i + 1);  // Auto-incrementing ID
            ps.setBigDecimal(paramIndex++, VALUE);
        }
    }
}