Redshift and Postgres JDBC driver both intercept jdbc://postgresql connection string

Question

I have a problem that I'm not quite sure how to solve: I have a web application (packaged as a war) and clients can configure which database they wish to point to. We support both PostgreSQL and Redshift (as well as others). JDBC4 drivers are loaded automatically, which is good. Here's the problem:

It appears that the Redshift JDBC driver will respond to the jdbc://postgresql connection string before the PostgreSQL one can. This causes JDBC errors when connecting to a PostgreSQL database.

I am specifying the driver name 'org.postgresql.Driver' as the driver for the datasource in my pom.xml, but i'm not sure how the spring JDBC templates are choosing the driver (unless it picks up the first handler).

Anyone else run into this sort of issue?

Answer 1

Another solution would be to add "OpenSourceSubProtocolOverride=true" to JDBC connection string for regular PostgreSQL connections.

Example:

jdbc:postgresql://localhost:5432/postgres?OpenSourceSubProtocolOverride=true  

Answer 2

This is because the redshift driver registers as being able to handle both the jdbc:postgresql and jdbc:redshift URL prefix.

When the Postgres & Redshift drivers are loaded from their jars they each register with DriverManger.

The logic implemented in DriverMananger.getDriver() and DriverManager.getConnection() is to loop through each of the drivers and stop once a driver indicates that it is able to handle the given URL.

If the Postgres driver registers first, everything works out fine as the Postgres driver only attempts to handle jdbc:postgresql. If Redshift driver manages to register first, then the Postgres driver will never be used.

The only way I have figured out to solve this is to add:

static {
    // Put the redshift driver at the end so that it doesn't
    // conflict with postgres queries
    java.util.Enumeration<Driver> drivers =  DriverManager.getDrivers();
    while (drivers.hasMoreElements()) {
        Driver d = drivers.nextElement();
        if (d.getClass().getName().equals("com.amazon.redshift.jdbc41.Driver")) {
            try {
                DriverManager.deregisterDriver(d);
                DriverManager.registerDriver(d);
            } catch (SQLException e) {
                throw new RuntimeException("Could not deregister redshift driver");
            }
            break;
        }
    }
}

Answer 3

Too bad I didn't see other answers before I solved the issue but I fixed the issue by overriding "postgres" subprotocol from Redshift driver by doing this

String[] names = com.amazon.redshift.PGInfo.PG_SUBPROTOCOL_NAMES;
for (int i = 0; i < names.length; i++)
    if (names[i].equals("postgresql"))
        names[i] = UUID.randomUUID().toString(); // Just random string